comparing two lists and returning value from different column

[Duser]

I am trying to compare two parts lists to each other to determine if there
are matches within the data. If that data matches i would like to return the
part number and description from different cells of the parts that matched.
In addition to finding the ones that match is there a way to sort the parts
that "don't match".
Example of what i'm looking at is below....

9008011597 643330E010 Bolt Washer
643490E010 9046709204 Nut Screw
521270E010 521280E010
6221713010 647180E010
9008011602 9008011603
9008011604 9008011620

 

[Harlan Grove]

Duser wrote...

I am trying to compare two parts lists to each other to determine if there
are matches within the data. If that data matches i would like to return the
part number and description from different cells of the parts that matched.
In addition to finding the ones that match is there a way to sort the parts
that "don't match".
Example of what i'm looking at is below....

9008011597 643330E010 Bolt Washer
643490E010 9046709204 Nut Screw
521270E010 521280E010
6221713010 647180E010
9008011602 9008011603
9008011604 9008011620

Nothing above appears to match. What are you matching between the two
parts lists?

 

[Otto Moehrbach]

What is the layout of your data? Are both lists in the same sheet?
Different sheets? Different files? What columns of each list do you want
to check for matches? What columns do you want copied from which list to
which list and in what columns do you want the copied data pasted? HTH
Otto

 

[mrbernz]

I have a question similiar. Basically one sheet has multiple columns
each holding different information about an item. Each item has its own
row. On another sheet I have one column that shares a name with one of
the columns on the previous sheet.

Sheet 1
Name Phone Address City State Zip
Jon Doe 555555 newhere nowhere st 55555
Jon Doe2 555555 newhere nowhere ni 55555
Jon Doe2 555555 newhere nowhere st 55555
Jon Doe3 555555 newhere nowhere st 55555
Jon Doe4 555555 newhere nowhere me 55555

Sheet 2
State
st
ni

What I would like to do is filter the state column on Sheet 1 by the
contents of the State column on sheet 2. A couple issues are Sheet 2 is
pulling data from a web query so the contents will change. Meaning
sometimes it might pull only two items, another time it may be 20 or
more.

 

[CLR]

With one list in column A, and the second list in column B, and your lookup
table in columns G:I, then
put this in C1 and copy down,
=IF(VLOOKUP(B1,A:A,1,FALSE)=B1,VLOOKUP(B1,G:I,2,FALSE),"")

and put this in D1 and copy down.....
=IF(VLOOKUP(B1,A:A,1,FALSE)=B1,VLOOKUP(B1,G:I,3,FALSE),"")

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Yours would take a relatively elaborate macro to check each State item on
sheet 2 and see if it existed in the State column in Sheet 1 and if so run a
Autofilter on that criteria and copy and paste those results to another
"Report" sheet, then clear the autofilter and then step down and do the same
thing with each item in State on sheet 2 and append each Autofilter results
to the bottom of the Report sheet list.....
It is definately do-able, but only justified if you have do do this exercize
frequently.....

hth
Vaya con Dios,
Chuck, CABGx3

 

[Harlan Grove]

CLR wrote...

With one list in column A, and the second list in column B, and your lookup
table in columns G:I, then
put this in C1 and copy down,
=IF(VLOOKUP(B1,A:A,1,FALSE)=B1,VLOOKUP(B1,G:I,2,FALSE),"")

and put this in D1 and copy down.....
=IF(VLOOKUP(B1,A:A,1,FALSE)=B1,VLOOKUP(B1,G:I,3,FALSE),"")

....

What are your IF checks supposed to accomplish? If B1 is in A:A, they
serve no purpose, and if B1 isn't in A:A, the VLOOKUP *and* the IF
calls return #N/A. If the OP wanted #N/A results when B1 wasn't in A:A,
why would they need your IF test?

The simplest way to check that B1 is in A:A is COUNTIF(A:A,B1), and the
most efficient is COUNT(MATCH(B1,A:A,0)). It'd also be much better to
use the result of the first formula as the check for the second
formula.

C1:
=IF(COUNT(MATCH(B1,A:A,0)),VLOOKUP(B1,A:A,2,0),"")

D1:
=IF(C1<>"",VLOOKUP(B1,A:A,3,0),"")