conditional formula or macro

[pm]

I am trying to compare two columns in two different worksheets. If the cell
in column model and serial worksheet #2 matches the cell in column Model and
serial worksheet #1, then I want to pull data from column A, B, C, and D from
worksheet #1 into a separate sheet. Help please.


Worksheet #1
A B C D Model Serial
801 Beaumont 7194629 E32696R36 C2405 PSPSV05008
805 Houston 7764192 E-062306CRR TM233XC LXT21051803284
801 Beaumont 7618786 E32215R24 TM243XC LXT3005347347





Worksheet #2

Model # Serial #
32HL67 AM339004740
32HL67U AM379011491
32HL67U AM37010883
32HL67U AM37021155
32HL67U AM37011550
32HL67U AM379011448
32LC7D 708MXTC4Y105
32LC7D 706MCMT0M499

 

[Pete_UK]

I would suggest that in Sheet1 you join the model and serial columns
together in a helper column, eg. put this in G2:

=E2&F2

and copy down. Then in your new sheet you could have a MATCH formula
along these lines in A2:

=MATCH(Sheet2!A2&Sheet2!B2,Sheet1!G$2:G$1000,0)

to find an exact match, and then this formula in B2:

=IF(ISNA($A2),"",INDEX(Sheet1!A$2:A$1000,$A2,0))

Copy B2 into C2:E2 and then copy A2:E2 down for as many entries as you
have in Sheet2.

Hope this helps.

Pete

 

[pm]

Hi Pete,
Thanks for the suggestion. I will try it.

 

[pm]

Pete,

I'm having difficulty getting this to work...any other suggestions?

 

[rsantaro]

Firstly, you need to resort and organize your Worksheet #2 table as follows
(I changed some of the values so you can compare results, later and follows -
ensure the Model # column is sorted in ascending order):

Worksheet 1

Model # Serial # A B C D
32HL67 AM339004740 801 Beaumont 7194629 E32696R36
32HL67U AM379011491 805 Houston 7764192 E-062306CRR
TM233XC LXT21051803284 801 Beaumont 7618786 E32215R24

For reference purposes, the 32HL67 would be in cell A6.

The following would be your workhsheet #2 table (I did everything on 1
worksheet):

Worksheet 2
Imported Data
Model # Serial # A B C D
32HL67 AM339004740 801 Beaumont 7194629 E32696R36
32HL67U AM379011491 805 Houston 7764192 E-062306CRR
32HL67U AM37010883

Here 32HL67 would be in cell A14.

The column A formula in worksheet #2 would be as follows:

=IF(AND(VLOOKUP($A14,$A$6:$F$8,1)=$A14,VLOOKUP($A14,$A$6:$F$8,2)=$B14),VLOOKUP($A14,$A$6:$F$8,3),"")

Looks bad, but actually easy. The logical test for the IF statement contains
the logical AND function which return true if all of its arguements are true.
The AND function compares the Model # and Serial # to see if both are the
same. If they are, then it returns, in the above formula, the A column value
- if not, it returns a blank (the "").

The column B formula would be:

=IF(AND(VLOOKUP($A14,$A$6:$F$8,1)=$A14,VLOOKUP($A14,$A$6:$F$8,2)=$B14),VLOOKUP($A14,$A$6:$F$8,4),"")

Same thing except the last VLOOKUP fetches the column 4 value (the column B
value).

I can send you the sample worksheet if you like.

 

[pm]

Yes, would you please send me a sample? thank you so very much.....