Excel 2007 : Vlookup help with merged cell data.

**here.to.code** · 02-17-2011, 04:37 PM

Hello everyone, I was wondering if I could get some help with this.
I am trying to be able to read all the email addresses from 1 worksheet to another.

Emails = All emails where Sheet1(FirstName + LastName) = Sheet2 (FirstName + LastName)

problem here is that Sheet1 has merged cells that can vary by amount of emails.
I have attached a workbook containing two sheets displaying my issue.

I have tried VLOOKUP but it looks like it can only manage to pull the first email not a range of them.

Any help is very much appreciated.

**NBVC** · 02-17-2011, 05:07 PM

Merged cells are not a good thing....

Add a helper column in D2 of Sheet1 enter:

=LOOKUP(REPT("z",255),A$2:A2)&"^"&LOOKUP(REPT("z",255),B$2:B2)

and in E2 enter:

=D2&"_"&COUNTIF(D$2:D2,D2)

copy both down the rows.

Then in C2 of Sheet2, enter:

=IFERROR(INDEX(Sheet1!$C:$C,MATCH($B2&"^"&$A2&"_"&COLUMNS($A$1:A$1),Sheet1!$E:$E,0)),"")

copied down the rows and across as many columns as you wish.

**here.to.code** · 02-17-2011, 06:15 PM

Originally Posted by NBVC

Merged cells are not a good thing....

Add a helper column in D2 of Sheet1 enter:

=LOOKUP(REPT("z",255),A$2:A2)&"^"&LOOKUP(REPT("z",255),B$2:B2)

and in E2 enter:

=D2&"_"&COUNTIF(D$2:D2,D2)

copy both down the rows.

Then in C2 of Sheet2, enter:

=IFERROR(INDEX(Sheet1!$C:$C,MATCH($B2&"^"&$A2&"_"&COLUMNS($A$1:A$1),Sheet1!$E:$E,0)),"")

copied down the rows and across as many columns as you wish.

That works nearly perfect! Wow thank you for the quick response.
I have 1 quick question though. Is there anyway I could have all those emails show up in 1 column?
Separated by commas instead of into new columns?

**NBVC** · 02-17-2011, 10:25 PM

Try this then.

Add this UDF to your VB editor (Alt+F11, Insert|Module)

and then paste:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then keep formula in D2 of Sheet 1, copied down.

You don't need the formula in column E

Then In C2 of Sheet2 enter formula:

=SUBSTITUTE(TRIM(aconcat(IF(Sheet1!$D$2:$D$7=B2&"^"&A2,Sheet1!$C$2:$C$7,"")," "))," ",",")

confirm it with CTRL+SHIFT+ENTER not just ENTER and copy it down the column.