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

ando000

New Member
Joined
Sep 26, 2014
Messages
5
My spreadsheet.
ABCDEFGH
s1street1street1
s2street2street2
s3street2street3
s4street2street4
s5street2street5
s6street2
s7street3
s8street3
s9street4
s10street5

<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.

ABCDEFGH
s1street1street1s1
s2street2street2s3s4s5s6
s3street2street3s7s8
s4street2street4s9
s5street2street5s10
s6street2
s7street3
s8street3
s9street4
s10street5

<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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

azumi

Well-known Member
Joined
Jun 4, 2013
Messages
555
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top