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!
 

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top