Compare the data in 2 columns
 

Hi
I would like to the compare the in Column A and Column C. Display in Column
D an the common data of A and C and Column E the associated value of Column A
in Column B For example

A B C
12 P1 23
23 p2 24
25 P3 12

Clomun D and E must have the following values
D E
12 P1
23 P2
Can anyone help me

Compare the data in 2 columns
 

On Mon, 29 Dec 2008 01:10:01 -0800, Raj
wrote:

Hi
I would like to the compare the in Column A and Column C. Display in Column
D an the common data of A and C and Column E the associated value of Column A
in Column B For example

A B C
12 P1 23
23 p2 24
25 P3 12

Clomun D and E must have the following values
D E
12 P1
23 P2
Can anyone help me


In D1 put the following formula:

=SMALL(C$1:C$10*COUNTIF(A$1:A$10,C$1:C$10),ROW()+S UMPRODUCT(1-COUNTIF(A$1:A$10,C$1:C$10)))

In E1 put the following formula

=VLOOKUP(D1,A$1:B$10,2,FALSE)

Copy D1:E1 down as far as needed.
Change the $10 to fit the size of your data in columns A:C

Hope this helps / Lars-Åke