Comparing Two columns to create column with duplicates

**braves1t** · 04-28-2010, 04:34 PM

Hi All,

I'm looking to compare two columns and then put the duplicate names from those columns into a third column. Can this be done? Thanks in advance!

**NBVC** · 04-28-2010, 04:44 PM

Say your lists are in Column A and Column B, where B is the longer list...

Then in C2, =IF(ISNUMBER(MATCH(B2,$A$2:$A$10,0)),COUNT(C$1:C1)+1,"") copied down... where A2:A10 is list in column A to match to value in B2

in D2: =MAX(C2:C11) where C2:C11 corresponds with list in column B.

Then in E2:

=IF(ROWS($A$1:$A1)>$D$2,"",INDEX($B$2:$B$11,MATCH(ROWS($A$1:$A1),$C$2:$C$11,0)))

copied down... again, where ranges line up with original data... adjust all as necessary.

**braves1t** · 04-28-2010, 07:21 PM

OK...I'm really a huge novice....How do you apply the formula? How do you copy down the formula?

Thanks

**braves1t** · 04-28-2010, 07:25 PM

I may have miss wrote the request.....I'm looking to have non-duplicates in the third column...Sorry for the confusion. Thanks

**NBVC** · 04-28-2010, 10:13 PM

For non-duplicates just change formula in C2 to:

=IF(ISNA(MATCH(B2,$A$2:$A$10,0)),COUNT(C$1:C1)+1,"")

and copy down the other formulas are the same.

To copy down a formula, after you enter it, reselect the cell, then click, hold and drag down the little black square at the bottom right corner of the cell, drag it down to as far as you need to go and let go.

**braves1t** · 04-29-2010, 08:29 AM

?....Will this work with Alpha characters as well as numbers? I have a list of names...there are no numbers involved. Thanks in advance

**NBVC** · 04-29-2010, 08:31 AM

It should.

It is checking for exact matches, whether numeric, alpha or alphanumeric... (if numeric, must be also same format... not mixed number,text).

Test it out. If problematic, advise me.