Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare 2 worksheets for simliar cells and then comb into one work
Hi there,
I need some urgent help please..... I have 2 worksheets. Worksheet 1 contains a list of part numbers from one of my customers in one column followed by number of units sold etc in other columns. Worksheet 2 has a list of my company part numbers and the sale prices etc. I need to compare the part numbers, and number of units sold in sheet 1 with the part numbers and value in sheet2. I want to end up with one combined sheet that shows my company part number with cost price, my customer part number and their number of units sold etc. Promblem i am having is that my customers part numbers do not exactly match my company part numbers, although they both conatin the same information, but just not in the same order. I need some kind of llok up and comparison and when it finds the same information in each cell it copies the relevant info from one sheet to the other. example Worksheet 1 Col A Col B 30-2-DRIFT PUNCHES 125MM £1.25 Worksheet 2 Col A Col B COl C 302DRIFTPUNCHES 37 55 I hope this explains my problem, if not please let me know and I will try to explain better. Thank you. Adam |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare 2 worksheets for simliar cells and then comb into one work
Are you saying that the *only* difference between your numbers and your
customer's numbers are the *absence* of dashes and spaces, and truncated characters. This will work for your example, although I'm sure that you haven't posted other variances. With your numbers and prices on Sheet1, A2 to B100, And customers list on Sheet2, from A2 to C100, enter this in C2 of Sheet1: =VLOOKUP("*"&LEFT(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),15)&"*",Sheet2!$A$2:$C$10,2,0) And copy down as needed. This will return the data from the 2nd column (B) of your customers datalist. Don't forget, this will *only* work if all your data matches the same configuration with the example you posted. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- " ft.com wrote in message ... Hi there, I need some urgent help please..... I have 2 worksheets. Worksheet 1 contains a list of part numbers from one of my customers in one column followed by number of units sold etc in other columns. Worksheet 2 has a list of my company part numbers and the sale prices etc. I need to compare the part numbers, and number of units sold in sheet 1 with the part numbers and value in sheet2. I want to end up with one combined sheet that shows my company part number with cost price, my customer part number and their number of units sold etc. Promblem i am having is that my customers part numbers do not exactly match my company part numbers, although they both conatin the same information, but just not in the same order. I need some kind of llok up and comparison and when it finds the same information in each cell it copies the relevant info from one sheet to the other. example Worksheet 1 Col A Col B 30-2-DRIFT PUNCHES 125MM £1.25 Worksheet 2 Col A Col B COl C 302DRIFTPUNCHES 37 55 I hope this explains my problem, if not please let me know and I will try to explain better. Thank you. Adam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to compare data in two different work sheets | Excel Worksheet Functions | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
formula adding cells in worksheets when # of sheets in work book changes | Excel Discussion (Misc queries) | |||
Can a conditional cell formula reference data selection in a comb. | Excel Worksheet Functions |