Append multiple email addresss to a report

[Jaimee001]

Hi Team,
I've looked through some of the responses here and am not able to find an answer. I'm also just learning VB so I'm not all that well versed.

I have a report which I have to append the email address to. However, some accounts have 1, some don't have any and some have multiple email addresses. There are about 2000 to append. Is there a way to append all and string them across?
Vlookup will append 1 but not those that have multiples.

I'm attaching an example report.

Thank you in advance
Jaimee

 

[Chihiro]

Here, sample without VBA.

It's bit long, but if you can live with having "0" instead of blank, when there is no email for SENO, then it can be halved in length.

 

[Marc L]

Hi !

According from original attached workbook :​

Sub Demo()
With CreateObject("Scripting.Dictionary")
        VA = Sheet2.Cells(1).CurrentRegion.Value
    For R& = 2 To UBound(VA)
        If .Exists(VA(R, 1)) Then .Item(VA(R, 1)) = .Item(VA(R, 1)) & vbTab & VA(R, 2) Else .Add VA(R, 1), VA(R, 2)
    Next
         Sheet1.Cells(1).CurrentRegion.Offset(1).Clear
         Sheet1.[A2:B2].Resize(.Count).Value = Application.Transpose(Array(.Keys, .Items))
         Sheet1.[B2].Resize(.Count).TextToColumns
         Sheet1.Cells(1).CurrentRegion.Columns.AutoFit
        .RemoveAll
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Jaimee001]

Here, sample without VBA.

It's bit long, but if you can live with having "0" instead of blank, when there is no email for SENO, then it can be halved in length.

Hi Chihiro,
I like your formula however I can't get it to work. I've attached a word document with the formula you provided and mine. I just changed one field. When I run it, the results are blank. I'm also oaky with having a "0" instead of blank but not sure how to change the formula to reflect that.

Thank you so much!

 

[Chihiro]

@Jaimee001
Glad you got it working. To shorten the formula and having "0" instead of blank when there's no email... you basically remove IF portion of the formula, that's where it's checking for "0".

So, resulting formula would be.
=IFERROR(INDEX('Email LU'!$A$2:$B$9,SMALL(IF(INDEX('Email LU'!$A$2:$B$9,,1)='Main Sheet'!$A4,ROW(INDEX('Email LU'!$A$2:$B$9,,1))-ROW($A$1),""),COLUMN(A:A)),2),"")

Entered as array (CSE).

 

[Jaimee001]

@Jaimee001
Glad you got it working. To shorten the formula and having "0" instead of blank when there's no email... you basically remove IF portion of the formula, that's where it's checking for "0".

So, resulting formula would be.
=IFERROR(INDEX('Email LU'!$A$2:$B$9,SMALL(IF(INDEX('Email LU'!$A$2:$B$9,,1)='Main Sheet'!$A4,ROW(INDEX('Email LU'!$A$2:$B$9,,1))-ROW($A$1),""),COLUMN(A:A)),2),"")

Entered as array (CSE).

Thank you so much!!!!!!!!! Love it!!!!