Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Data Columns for comparison
I have to compare 3 companies with seperate sheets for each company's product
list. I have 34200 (rows)records for Co. 1 Col A is product ref #. Col B to I is product info, price etc. I have 16387 records (rows) for Co. 2. Col A is product ref #. Col B to M is product info. I have 16121 records (rows) for Co.3. Col A is product ref #, B to M is product info. The product overlap across the 3 companies is 90%. I need to align all three lists onto 1 spread sheet in order that I can compare prices for the same product across three companies. How to sort by product ref #, to get identical products on the same row and bringing with the sort the detail contained across the rest of sheets. End result may be Col A to I, Co 1 detail, Col J to V = Co 2, Col W to AI = Co 3. Would really appreciate some help. Thanks I have -- JOHNW |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Data Columns for comparison
Hi John
I can recommend that you copy all the columns to a single excle sheet. This is basicall to run an advance query which will fetch you all the information for you to compare. I shall explain you how to do it. Assuming that you have copied all the columns to a single spread sheet, you know the column heading now, save the excel sheet. Now I wiil be using the Advance filtering techniques so that all the column information are retrieved even if the typed cells are misspelled, which normally is not the case when you do a simple filtering technique. The filtered values may not be correct. To retieve the values; 1. Select all records and the column headings and define a name for the range List. This is the List range name. 2. Select all column headings and paste in a seperate row anywhere in the spreadsheet. 2. Select the pasted column heading along with an empty row and define a name. This is the criteria range name. 3. Define a criteria on the second row for all columns, if necessary. For example S* will display all details specific to "S'. 4. Click Data- Filter- Advanced Filter 5. Type the List name 6. Type the Criteria Name 7. Click copy to another location option - Mandatory 8. Click Copy to 9. Click the cell below the criteria range All data will be displayed specific to the query you requested. This is very usefull if the spreadsheet data are mistyped or mis-spelled. Now you have done a database funtion on your excle spread sheet. Challa Prabhu "JOHN" wrote: I have to compare 3 companies with seperate sheets for each company's product list. I have 34200 (rows)records for Co. 1 Col A is product ref #. Col B to I is product info, price etc. I have 16387 records (rows) for Co. 2. Col A is product ref #. Col B to M is product info. I have 16121 records (rows) for Co.3. Col A is product ref #, B to M is product info. The product overlap across the 3 companies is 90%. I need to align all three lists onto 1 spread sheet in order that I can compare prices for the same product across three companies. How to sort by product ref #, to get identical products on the same row and bringing with the sort the detail contained across the rest of sheets. End result may be Col A to I, Co 1 detail, Col J to V = Co 2, Col W to AI = Co 3. Would really appreciate some help. Thanks I have -- JOHNW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Matching Button for MS Excel 2007 or later | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Matching two columns and their data | Excel Worksheet Functions | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |