# Compare 2 columns and if there is duplicates extract data from corresponding column into a new columns.

#### ando000

##### New Member
 A B C D E F G H s1 street1 street1 s2 street2 street2 s3 street2 street3 s4 street2 street4 s5 street2 street5 s6 street2 s7 street3 s8 street3 s9 street4 s10 street5

<tbody>
</tbody>

I require a formula to compare columns B and D for a match up, and if a match up does occur print the corresponding result from Column A into E. However, if there are multiple duplicates between B and D print into new columns.

End result of what I'd like.

 A B C D E F G H s1 street1 street1 s1 s2 street2 street2 s3 s4 s5 s6 s3 street2 street3 s7 s8 s4 street2 street4 s9 s5 street2 street5 s10 s6 street2 s7 street3 s8 street3 s9 street4 s10 street5

<tbody>
</tbody>

<tbody>
</tbody>

I have browsed the forums for previously solved solutions and this is the closest i've found.
http://www.mrexcel.com/forum/excel-...l-print-third-column-without-replacement.html

I have been using this formula and it worked amazingly but it does not account for duplicates into new columns. '=INDEX(A:A,SMALL(IF(B\$2:B\$10=D2,ROW(B\$2:B\$10)),COUNTIF(D\$2:D2,D2)))'

Thanks for taking the time to read this!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### azumi

##### Well-known Member
Put in E2:
=IFERROR(INDEX(\$A\$2:\$A\$11,SMALL(IF(\$B\$2:\$B\$11=\$D2,ROW(\$A\$2:\$A\$11)-ROW(\$A\$2)+1),COLUMNS(\$A\$1:A1))),"")

this array formula when ENTER you need to press CTRL-SHIFT-ENTER button together and copied down and cross as necessary

Replies
6
Views
102
Replies
8
Views
68
Replies
0
Views
91
Replies
2
Views
219
Replies
5
Views
61

1,109,435
Messages
5,528,746
Members
409,833
Latest member
tdnhan

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...