compare 2 worksheets for simliar cells and then comb into one work

[Adam.Bird]

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

 

[RagDyer]

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.