Finding common column values with duplicate numbers

**ritavv** · 10-13-2010, 06:01 AM

Hi
i have two columns where in i have a field Region ID coming from two diff tables. Now i need to find out the common Region ID values between Table 1 and Table 2. InTable 2, i have duplicate Region ID's. How can i find out the common values between the two columns with duplicate values also.

I have attached the file with the values.

Pls help me what can i do.

Thanks
Rita

**DonkeyOte** · 10-13-2010, 06:08 AM

It would help if you outlined expected results.

If you're saying you want to list all items from Table2 that appear in Table1 including any duplicated entries then:

D1:
=SUMPRODUCT(COUNTIF($A$3:$A$11,$B$3:$B$38))
this gives total no. of entries to be retrieved

then

D3:
=IF(ROWS(D$3:D3)>$D$1,"",SMALL(IF($B$3:$B$38>=$A$3,IF(LOOKUP($B$3:$B$38,$A$3:$A$11)=$B$3:$B$38,$B$3:$B$38)),ROWS(D$3:D3)))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied down to D38

the above is based on sample file in which values in A & B are sorted in Ascending order.

edit: above revised to account for possibility that first List2 value is lower that first List1 value