Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Formula To Help Match Data From Two Worksheets

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have two worksheets - one with the customer information (name, cust. #, address, etc.) and the other with the customer name, customer # and the sales info for the last two years. I need to match the customer numbers from each sheet against eachother to add the sales information to the addresses. There are varied customer numbers in each, so there are a few that will not match up. I have no clue how to do this except manually and there are over 14000 records in each sheet. Help!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e
Delete Hidden Worksheets
- This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







I have quote package for my estimators to use where they select the customer from a drop-down list and the other customer fields are automatically filled in. The problem is when its a new customer not on the list. In the current approach, the user gets an error message "the value you entered is not valid."

I would like them to continue on and enter the customer information manually. Later, I can update the customer list & information.

Customer information is currently on a sheet called Clists. Each row contains a customer's information: name, contact, address, city, st, zip, phone, etc. The sheet has a header row.

On the Estimate page where the user would normally type the customer's name, they now select the customer from a drop-down list generated by a Name defined by OFFSET(Clists!A2,0,0,COUNTA(Clists!$A:$A)-1,1).

The other customer fields are filled by doing an index match to the customer name using INDEX(Clists!C2:C1000,MATCH($B$1,Clists!$A$2:$A$1000)). Each field has a similar INDEX formula.

Any ideas or suggestions would be welcome.


Hello everybody. I need your help yet again. I have a sheet (copied below for viewing) where cells from another sheet are copied into columns S-W. A formula in columns B-G then sorts the information into the appropriate columns, i.e. puts the customer information under the correct sales person. What I need to do is for sales that have been carried over (marked "c/o" in column S) to be highlighted in the correct column for that customer and sales person.

For example.... Sales person Rob (RM) sold and item to customer 1 last month. Customer 1 is displayed in cell B3 and the gross for that customer is in cell C3. I want that line to be highlighted using conditional formatting so that I can see that customer was carried over. from the previous month. I thought I had figured it out, but it would highlight whatever row the information was on in columns S-W. The conditional formatting needs to change depending on if column S has "C/O" in it or not, because I am going to use a similar formatting to mark customers that WILL be carried to the following month.

Here is the sheet. Tahnk you in advance for all of your suggestions.

******** ******************** ************************************************************************> Microsoft Excel - green book.xlsm ___Running: 12.0 : OS = Windows XP File Edit View Insert Options Tools Data Window Help About S2 T2 U2 V2 W2 B3 C3 D3 E3 F3 G3 H3 S3 T3 U3 V3 W3 B4 C4 D4 E4 F4 G4 S4 T4 U4 V4 W4 B5 C5 D5 E5 F5 G5 S5 T5 U5 V5 W5 B6 C6 D6 E6 F6 G6 S6 T6 U6 V6 W6 B7 C7 D7 E7 F7 G7 S7 T7 U7 V7 W7 B8 C8 D8 E8 F8 G8 S8 T8 U8 V8 W8 B9 C9 D9 E9 F9 G9 S9 T9 U9 V9 W9 B10 C10 D10 E10 F10 G10 S10 T10 U10 V10 W10 =
A B C D E F G H I J K L M N O P Q R S T U V W X 1 * Rob Pete Tony House * * * * * * * * * Carried*In Carry*Over Sales*Person Cust.*Name Gross SP*List 2 * Cust.*Name Gross Cust.*Name Gross Cust.*Name Gross Cust.*Name Gross Sales*Person * * * * * * * * c/o *
RM Customer*1 1234 RM 3 1 Customer*1 1234 Customer*4 4378 Customer*2 342 *
* * * * * * * * * * *
*
TB Customer*2 342 PG 4 2 Customer*3 5674 Customer*6 9999 Customer*5 2323 * * * * * * * * * * * *
a RM Customer*3 5674 TB 5 3 *
*
*
*
*
*
* * * * * * * * * * * *
*
PG Customer*4 4378 TC 6 4 *
*
*
*
*
*
* * * * * * * * * * * *
a TB Customer*5 2323 MB 7 5 *
*
*
*
*
*
* * * * * * * * * * * *
*
PG Customer*6 9999 CL 8 6 *
*
*
*
*
*
* * * * * * * * * * * *
*
*
*
*

hi, i use excel in my dispatch office. i am working on a new workbook and could use some help. In one sheet of the workbook we enter in customer calls. i would like to keep customer records in another sheet, which would include their address, phone numbers, and past invoice numbers all on the same row of the customer database sheet. At the top of the sheet that we enter caller information i would have a table set up to automatically fill in the customer info based on the name typed in, if the customer exists in the customer database sheet of the same workbook the table will automatically fill in their address, phone number and past invoice numbers. i was thinking something like this =IF(COUNTIF('Customer Database'!A:A,Blanks!D6)>0,'Customer Database'!A2,"Not Found") ....the problem is that no matter who's name i enter, it will always add the information from row 2 of the customer database sheet. i need it to take information from the corresponding row(if customer name is found on row 45 then my formula needs to enter the info from row 45, not row 2. not sure how to do this, if anyone knows what i am talking about and can help me solve this issue i would be forever in your debt. thanks in advance...shannon


Hi all, I own a pool service company and im trying to streamline the process of setting routes for my employees to follow.

Heres what my spreadsheet looks like so far:

Customer Phone Numbers Shipping Address 1 Customer 1 2 Customer 2 3 Customer 3 4 Customer 4 5 Customer 5 6 Customer 6 7 Customer 7 8 Customer 8 9 Customer 9 10 Customer 10 11 Customer 11 12 Customer 12 13 Customer 13
What im trying to accommplish is to select the customers via a drop down box or something and have the phone number and shipping address populate automatically

Probably a simple procedure but one I have been unable to search for on google.

Thanks in advance for your help!


Hi All,


Basically I have a set of Customer Numbers, and each month I get a report with customer numbers and the data next to that for the month. The problem is the customer number come in mixed up and sometimes not at all. I could filter then numbers but there are random skipped customer numbers. (~850 customers)

I need a way to match the customer numbers and then input the data for the month in the correct column. I will attach the sample worksheet with what I am trying to explain! Thanks!
Sandy

p.s. In the first sheet is the customer numbers and data for the month of june. In the second sheet is the complete list of customer numbers and a column for the june data should it match....


Hi,

Can someone please help with the following:-

I have two sets of data that I need to match using the following rules:

- Match low sales with high purchases - firstly to the same customer and then to whoever is left.

DATA:-

PURCHASE SALE
Customer Price Customer Price
A 13.60 E 20.10
B 9.50 G 19.50
C 9.25 H 19.50
D 9.25 B 19.50
C 9.20 A 19.50
F 9.20 C 19.00
G 8.95 D 18.50
C 8.90 C 18.25
I 8.90 H 16.00

e.g. so ideally would like to match the lowest priced customer H with the highest priced customer H in order and if there is more than one H then match that customer using the standard 'low to high' rule.

If you need more clarification please let me know.


Can anyone help with this one?
I use Excel 2003 and have a spreadsheet that gives all sales to all customers over a period of time. Currently for 2009 it's at 46,000 rows so I have to select out the sales that are exactly the same but debit and credit as give a total between the two entries of zero. An example of this would be:

Year Month Customer Length Volume Value
2009 May A Customer & Co Ltd 3.0 (0.79) (118.50)
2009 May A Customer & Co Ltd 3.0 (1.02) (152.25)
2009 May A Customer & Co Ltd 3.0 (2.14) (321.45)
2009 May A Customer & Co Ltd 3.0 (1.33) (198.75)
2009 May A Customer & Co Ltd 3.0 1.02 152.25
2009 May A Customer & Co Ltd 3.0 2.14 321.45
2009 May A Customer & Co Ltd 3.0 0.66 99.45
2009 May A Customer & Co Ltd 3.0 1.03 154.35
2009 May A Customer & Co Ltd 3.0 0.79 118.50
2009 May A Customer & Co Ltd 3.0 (0.66) (99.45)
2009 May A Customer & Co Ltd 3.0 1.03 154.35
2009 May A Customer & Co Ltd 3.0 1.33 198.75

Does anyone have an "easy" method of doing this rather than manually?

Thanks again.

Cheers

Aargh!!


Ok so I have a number of worksheets inside a single workbook. They are all related to a given customer, each new customer gets a workbook. What I want to happen is when I create a new workbook for the customer I enter customer information onto the Customer tracking document and I want the customers name to appear on several other sheets in the workbook. I also want other customer information to appear on these other sheets including dates, invoice numbers, phone numbers, customer numbers....the list goes on. I assume it is all the same process. I can take values from a cell in another worksheet and apply it to a formula and that works but it will not work for the other peices of information I mentioned. Any help would be great. Thank you


I have numerous spreadsheets that I need to combine into one large one. For example; I have a spreadsheet of customer names, a spreadsheet of customer addresses, a spreadsheet of customer billing address and a shreadsheet of the next bill date for the customer. Each spreadsheet has more or less information on them which means I can't just cut and paste each column. I have to make sure the customer on my main spreadsheet has information on each of the other ones. What is the best formula to use to capture what I need?


I have a series of worksheets. Sheet 1 contains data re customer name and Customer ID viz

Cust A 100
Cust B 200
Cust C 300

In another worksheet 2, I input sales information. We have many customers and I would like to be able to refer to the lists on sheet 1 (? through the validation facility) find the Customer name and then click onto the appropriate ID which is then transferred to sheet 2

I use Excel 2003 with WinXP
Thanks for your help
elm


Hi all, have a problem..

In summary i want to index sheet 1, match the same items in sheet 2, add all the relevant sales together that reside in sheet 1 then return the dollar value.

In detail...
Sheet 1 (Product Sales) has dates, customer names, and sales columns for each customer on that date. This is updated each time a sale for a product is made. (sometimes more than one product is purchased by the customer on the same date)

Sheet 2 (Income) is used to total/aggregate a variety of different sales. In this example, if in sheet 2, i select from a drop down list a customer's name on a specific date, it looks in sheet 1 for all sales for that customer on that date, adds them up from sheet 1 and puts the total beside that customer in that row.

I've tried a few calc's to no avail this is where i was going but its not working and i haven't got as far as adding everthying together...

=INDEX(ProductSales!A4:E40, [ this is sheet 1 which has has the list of customers, products, sales, dates etc.]

MATCH(Income!A4:B40, [ this is sheet 2 where i want the dates and customers in here to match sheet1]

ProductSales!E4:E40)) [ and in sheet 2 i want it to return the sum total of all those product sales for that customer on that date

Any ideas? Can this be done?

Thanks!!


Hello to all. Need some help to alter this formula.

Code:

 {=IF(ISERROR(SMALL(IF($U$2:$U$203=$X$2,ROW($U$2:$U$203)),$A3)),"",INDIRECT("V"&SMALL(IF($U$2:$U$203=$X$2,ROW($U$2:$U$203)),$A3)))}


this formula pulls the information from columns S-W and puts it into the appropriate column for the salesmen listed (ie. Rob, Peter, and Tony.) Occassionally there are other salesmen that make a sale for this sheet. I need to alter this formula so that any of the other salesmen (anyone other than RM, PG, or TB) will then be put into the house column. All of the salesmen are listed in column X. I've copied the sheet to aid in the explanation below. Any thoughts would be most helpful.

******** ******************** ************************************************************************> Microsoft Excel - green book.xlsm ___Running: 12.0 : OS = Windows XP File Edit View Insert Options Tools Data Window Help About S2 T2 U2 V2 W2 B3 C3 D3 E3 F3 G3 H3 S3 T3 U3 V3 W3 B4 C4 D4 E4 F4 G4 S4 T4 U4 V4 W4 B5 C5 D5 E5 F5 G5 S5 T5 U5 V5 W5 B6 C6 D6 E6 F6 G6 S6 T6 U6 V6 W6 B7 C7 D7 E7 F7 G7 S7 T7 U7 V7 W7 B8 C8 D8 E8 F8 G8 S8 T8 U8 V8 W8 B9 C9 D9 E9 F9 G9 S9 T9 U9 V9 W9 B10 C10 D10 E10 F10 G10 S10 T10 U10 V10 W10 =
A B C D E F G H I J K L M N O P Q R S T U V W X 1 * Rob Pete Tony House * * * * * * * * * Carried*In Carry*Over Sales*Person Cust.*Name Gross SP*List 2 * Cust.*Name Gross Cust.*Name Gross Cust.*Name Gross Cust.*Name Gross Sales*Person * * * * * * * * c/o *
RM Customer*1 1234 RM 3 1 Customer*1 1234 Customer*4 4378 Customer*2 342 *
* * * * * * * * * * *
a TB Customer*2 342 PG 4 2 Customer*3 5674 *
*
*
*
* * * * * * * * * * * c/o *
RM Customer*3 5674 TB 5 3 Customer*7 1500 *
*
*
*
* * * * * * * * * * * *
a PG Customer*4 4378 TC 6 4 *
*
*
*
*
*
* * * * * * * * * * * *
*
SC Customer*5 2323 MB 7 5 *
*
*
*
*
*
* * * * * * * * * * * *
*
RA Customer*6 9999 CL 8 6 *
*
*
*
*
*
* * * * * * * * * * * *
a RM Customer*7 1500 SW 9 7 *
*
*
*
*
*
* * * * * * * * * * * *
*
MB

As I understand the SUMPRODUCT function, the ranges have to be the same size in order for this to work.

Let me try an example to illustrate:

I have two worksheets.

On the first is a list of all customer numbers (I have 1,240 customers).

On the second is a table of sales $ (by customer number). This table has 13 columns: Customer #, Year, Jan, Feb, Mar, Apr, etc. The second worksheet does not contain a record for every customer number (some customers don't have sales $ in the table), and, some customer numbers will have more than one record (year 2004, 2005, 2006 etc.).

On the first worksheet beside each customer #, I want to sum up information from my second sheet where the "year" value is equal to X. If a customer did not have sales, then I want to return the value 0.

What the SUMPRODUCT formula is doing for me now is working, but only on the rows of my first worksheet that are within the row range of my second worksheet. (Worksheet #1 has 1240 customer numbers listed. Worksheet #2 has only 1032 records. Worksheet #1 SUMPRODUCT formula works up through row #1032. Formula does not work for rows 1033 through 1240.)

Hope you can help.
Thx.


We have recently set up an excel workbook to enter our customer information as it comes in when we set up appointment. This info. is printed out as our customer sheet and all the info. from it goes to other worksheets to estimate the job. Is there a way/how can I use that general customer info. (name, address, phone, etc.) and transfer it to a different document that would store only that info. from each customer? so that I can have a database for mailings, etc.


hello!
i am in need of help with some kind of formula, i hope there is a way to even do this.
here's the situation:
say on the 1st excel sheet cells A2-A5 list one customer's name, address, etc. along with their specific customer needs. this is a generic form that can be updated with different customer's info from the all-data sheet
on the 2nd sheet is a spreadsheet with every customer's name, address, etc. (the all-data sheet)
here's what i want to do:
use a cell outside of the specific customer form, say K1, and enter the line number from the all-data sheet for the customer's info I want to input into the main form. so say on the all-data sheet customer number 11 is Joe's Business.
In cell K1 on the form, I can put 11 and Joe's Business information will auto-input into the form..
the data layout in my all-data tab is set up with the following columns:
customer name
customer address
customer city
customer state
customer zip
customer phone
etc.
it is an all-inclusive listing of all of the customers in question with their physical information.

on the form at the top left is their basic information:
cell A1 = customer name
cell A2 = customer phone
cell A3 = customer address, and so on..

but what i am wanting to do, is enter the line number from the all-data tab outside of the form and have the info from the all-data tab auto-populate on the form.
so say i enter "10" in cell K1 on the form sheet, it would pull all info from line number 10 from the all-data sheet...
does anyone know if this is possible?
apprecate any help!



Hi,

I have a major issue that I'm hoping someone can help with,

essentially I have 2 sheets:

Sheet 1

2 columns
Customer Number, Date

Sheet 2

2 columns
Customer Number, Date

Sheet 1 contains distinct customer numbers with 1 date per customer number

Sheet 2 contains multiple numbers (some the same) and multiple dates (some the same)

I would like to do some kind of match/vlookup on sheet 1 customer number & date against sheet 2 customer number & date and return YES if it matches on both fields.

I tried a vlookup for this but the issue I am having is it's finding the 1st instance on sheet 2 and if the date does not match it wont check anymore records, so a more dynamic approach is needed.

Please does anyone have any ideas?

Thanks in advance


I am looking to transfer data from one spreadsheet to another with a kind of vlookup although I cannot get it to work, basically my date sheet looks like this:-

Jan Feb Mar Apr May Item 1 3 6 9 12 15 Customer 1 1 2 3 4 5 Customer 2 1 2 3 4 5 Customer 3 1 2 3 4 5 Item 2 11 11 11 11 21 Customer 4 5 5 5 5 9 Customer 3 6 6 6 6 12

So there are multiple customer for each item (but not the same ones and the item row is the total sale for all customers.

I want to be able to use this information in another workbook so that I can then extract the total sales and each customer when I type in the item number, but I don't know where to start.


I hope this is a simple one. I need to average the gross for each customer in column C, bit only the ones that do not have the conditional format to make them yellow (format caused by cell in column T having an 'a' in it)(See sheet below). With your help on this formula, I can alterit to do a counifs function to count the sales not in yellow and to count only the ones in yellow.

******** ******************** ************************************************************************> Microsoft Excel - green book.xlsm ___Running: 12.0 : OS = Windows XP File Edit View Insert Options Tools Data Window Help About R2 S2 T2 U2 V2 W2 B3 C3 D3 E3 F3 G3 H3 I3 J3 R3 S3 T3 U3 V3 W3 B4 C4 D4 E4 F4 G4 H4 I4 J4 R4 S4 T4 U4 V4 W4 B5 C5 D5 E5 F5 G5 H5 I5 J5 R5 S5 T5 U5 V5 W5 B6 C6 D6 E6 F6 G6 H6 I6 J6 R6 S6 T6 U6 V6 W6 B7 C7 D7 E7 F7 G7 H7 I7 J7 R7 S7 B8 C8 D8 E8 F8 G8 H8 I8 J8 R8 S8 T8 B9 C9 D9 E9 F9 G9 H9 I9 J9 R9 S9 T9 U9 V9 W9 B10 C10 D10 E10 F10 G10 H10 I10 J10 R10 S10 T10 U10 V10 W10 =
A B C D E F G H I J K L M N O P Q R S T U V W 1 * Rob Pete Tony House * * * * * * * * * Carried*In Carry*Over Sales*Person Cust.*Name Gross 2 * Cust.*Name Gross Cust.*Name Gross Cust.*Name Gross Cust.*Name Gross Sales*Person * * * * * * * 0 c/o *
RM Customer*1 1234 3 1 Customer*1 1234 Customer*4 4378 Customer*2 342 test7 1111 RA * * * * * * * 0 c/o *
TB Customer*2 342 4 2 Customer*3 5674 *
*
*
*
*
*
*
* * * * * * * 0 c/o *
RM Customer*3 5674 5 3 Customer8 2222 *
*
*
*
*
*
*
* * * * * * * 0 c/o *
PG Customer*4 4378 6 4 Customer9 123 *
*
*
*
*
*
*
* * * * * * * 1 c/o *
RA test7 1111 7 5 *
*
*
*
*
*
*
*
*
* * * * * * * 0 *
a RM Customer8 2222 8 6 *
*
*
*
*
*

I have a list of customers and sales -- multiple sales rows for customers. The customer#s are unique, but some customer#s are assigned to more than one account name. The reason is that when a business was purchased the location kept the same # but a new record was added for the new name. What I want to do is select the name that corresponds to the most recent activity.

The file is layed out with customer number in the first field, customer name in the next, then sales by quarter across the next 12 fields.

I have filtered the data to identify the unique customer numbers. To those I would like to assign a single customer name -- the most recent name. From there, I can go back and update all names and then query for totals once I have unique names AND cust#s.

If you can help me do what I THINK I should do, any it would be greatly appreciated. If there's a better way to clean it up that'd be great too!!!


I'm working on a customer data conversion for over 7000 customers. In my file I may have the a customer to show up only once or up to 80 times (multiple addresses for same customer). So I have to determine which of the customer addresses is the "primary customer" by flagging it with a "Y" for those that have the highest sales amount.

So in the attached spreadsheet, I have 3 columns.
Column A - Customer #
Column B - Sales amount
Column C - Primary (top customer address)

Any ideas on a formula I can use in column C?


I am new to pivot tables and am attempting to build what I thought was a simple one. I have two sheets, one for customer information and the other for sales information. The Customer Information sheet contains the following: customer #, name, address, city, and zip. The Sales sheet only contains customer #, name, dates and sales quantities. In order to make it easier on myself, I combined the two sheets into one, using the Sales sheet as my main sheet and employing vlookup formulas to fill in the detail info from the Customer Information sheet. I then copy and paste values on these columns.

All of the fields listed above are to be included in the pivot table, with the dates being the column labels, quantities as the value and everything else as row labels. My problem is that all fields that were supplied via the vlookup formulas contain #N/A. All other fields that are native to the Sales sheet are shown correctly with no problems. I've googled and searched and can't seem to find solutions to this. Any thoughts?


I have an excel file that contains thousands of lines of sales data. I'd like to combine rows that contain similar information. For example, customer 99199 has two lines for product 1026 - I'd like to combine the quantity, sales and Cost on those two lines into one line.

Some sample data is below. Thanks for your help in advance!

cust-NUMBER cust-name prod-NUMBER prod-name Quantity-Ordered Sales Cost 99919 Customer Name 1 1123 Product #1 1 $12.90 $6.12 99919 Customer Name 1 2129 Product #2 2 $88.50 $52.32 99919 Customer Name 1 418925 Product #3 1 $5.00 $0.00 100348 Customer Name 2 1214 Product #4 8 $26.00 $9.60 100348 Customer Name 2 1230 Product #5 1 $28.75 $12.42 100348 Customer Name 2 23020 Product #6 3 $111.75 $54.18 100348 Customer Name 2 418925 Product #3 1 $5.00 $0.00 96179 Customer Name 3 11292 Product #7 2 $65.90 $25.82 96179 Customer Name 3 418925 Product #3 1 $5.00 $0.00 99199 Customer Name 4 1026 Product #8 8 $16.00 $15.52 99199 Customer Name 4 1026 Product #8 4 $8.00 $7.76 99199 Customer Name 4 1123 Product #1 9 $81.00 $55.08 99199 Customer Name 4 1125 Product #9 4 $176.00 $86.72 99199 Customer Name 4 1127 Product #10 5 $44.25 $30.80 99199 Customer Name 4 1128 Product #11 16 $32.00 $13.76 99199 Customer Name 4 1128 Product #11 5 $220.00 $103.35 99199 Customer Name 4 1128 Product #11 10 $20.00 $8.60 99199 Customer Name 4 1128 Product #11 6 $264.00 $124.02 99199 Customer Name 4 1135 Product #12 3 $21.00 $13.17


Hello,

I am wanting to create a chart from a list of data that changes frequently, and to have the chart plot results based on only a segment of the list (of my choosing).

Taking the list below as an example, I would like to have a chart that displays sales per month for customer 1 (i.e. Rows 2 to 7), but if I want to see sales for customer 2, using something like a spin button, the source data for the chart would change to Rows 8 to 12 with a simple button click:

Sheet1

* A B C 1 Customer Name Month Sales 2 Customer 1 1 10 3 Customer 1 3 5 4 Customer 1 4 15 5 Customer 1 5 12 6 Customer 1 7 10 7 Customer 1 8 5 8 Customer 2 2 20 9 Customer 2 3 22 10 Customer 2 4 15 11 Customer 2 7 8 12 Customer 2 9 6

Excel tables to the web >> Excel Jeanie HTML 4

I am aware of Pivot Charts, but they aren't as flexible as normal charts, so I want to avoid using them.

Does anyone have any ideas as to how I could go about doing what I have described?

Many thanks in advance.

Matty


Just started wondering if there's a simpler way to do this, I basically need to manually scrape information from text files and enter into excel. I've usually done it all by hand which takes a good long while.

Source plain-text input:
CUSTOMER NAME - [0a-00-3e-f5-2e-63] - LUID: 006
CUSTOMER NAME (CONTACT), (780) xxx-xxxx - [0a-00-3e-f6-ea-2a] - LUID: 009
CUSTOMER NAME (CONTACT) (780) xxx-xxxx - [0a-00-3e-f6-ea-30] - LUID: 007
CUSTOMER NAME - [0a-00-3e-f2-0c-f1] - LUID: 008
CUSTOMER NAME (Abbreviated) - [0a-00-3e-f7-4d-7e] - LUID: 010
CUSTOMER NAME (CUSTOMER SITE - NAME) - [0a-00-3e-d0-b5-de] - LUID: 003
CUSTOMER NAME, (780)xxx-xxxx cell. - [0a-00-3e-d0-b6-05] - LUID: 002
CUSTOMER NAME - [0a-00-3e-fa-81-45] - LUID: 005
CUSTOMER SITE - CUSTOMER NAME - [0a-00-3e-d0-b5-fe] - LUID: 004

Final Excel output after copying each item by hand ('|' is a separated column):
CUSTOMER NAME |0a-00-3e-f5-2e-63|
CUSTOMER NAME (CONTACT) |0a-00-3e-f6-ea-2a|
CUSTOMER NAME (CONTACT) |0a-00-3e-f6-ea-30|
CUSTOMER NAME |0a-00-3e-f2-0c-f1|
CUSTOMER NAME (Abbreviated) |0a-00-3e-f7-4d-7e|
CUSTOMER NAME (CUSTOMER SITE - NAME) |0a-00-3e-d0-b5-de|
CUSTOMER NAME |0a-00-3e-d0-b6-05|
CUSTOMER NAME |0a-00-3e-fa-81-45|
CUSTOMER SITE - CUSTOMER NAME |0a-00-3e-d0-b5-fe|

As you can imagine this is quite tedious after awhile.

It's the same thing I want to apply on each tab/sheet, but could always make it so its all in one sheet if that's easier. Any ideas?


I have a quote sheet where I load the customer's information (address, telephone, terms, etc) by selecting the customer from a drop down list and use INDEX and MATCH functions for the other fields. The customer data is on a sheet named Clists.

=INDEX(CLists!B2:B47,MATCH($B$1,CLists!$A$2:$A$47))

a) I'm struggling with how to handle a new customer, someone not on the list. Dynamic update of the list would be nice.
b) Can the customer data be in another file instead of a sheet within the file?