Repeat name based on number of email id

[neeraj2050]

Greetings!!
I have data into which vendor name comes once but that has more than 1 email id. I want to repeat the vendor name in different rows based on the number of email ids. For example : for 5 email id related to single vendor, vendor names should repeat 5 times.
File has been attached for more clarity

Any non VBA solution would be much appreciated

Thanks in advance!!

 

[Chihiro]

Assuming you already have process to separate out each email to individual lines. To get matching vendor name...
=INDEX($A$2:$A$3,MATCH("*"&D7&"*",$D$2:$D$3,0))
Copy down.

 

[neeraj2050]

Thanks for your suggestion!!

May I know the logic as well in respect of using *

 

[neeraj2050]

Also please suggest how can we get email ids into separate line, so that each email comes into next line

 

[Chihiro]

1. "*" is used as wild card. So, if the email string (found in D7~) is found anywhere in the cell referenced (ex. D2) it will be considered a match.

2. Easiest way I can think of requires VBA. Without it, I'd do following.
- Copy D2 to D7
- Go to Data tab->Text to Columns->Delimited->Comma
- Set Destination to E7
- Copy resulting data
- With D7 selected Paste Special->Transpose (Alt+e+s & then e)
Repeat. At the end, you can delete column E~.

 

[bosco_yip]

Try the formula solution to repeat Vendor names and separate Email IDs.

1] Helper B2, copy down :

=(LEN(D2)-LEN(SUBSTITUTE(D2,", ","")))/2+1

2] Vendor name A7, copy down :

=IFERROR(INDEX(A$2:A$3,MATCH(1,INDEX(--(COUNTIF(A$6:A6,A$2:A$3)<B$2:B$3),),)),"")

3] Email ID D7, copy down :

=IF(A7="","",TRIM(MID(SUBSTITUTE(", "&VLOOKUP(A7,A$2:D$3,4,0),", ",REPT(" ",50)),COUNTIF(A$7:A7,A7)*50,50)))

Regards
Bosco

 

[neeraj2050]

Thanks to all!!

@Bosco, that formula worked very well. But I afraid of using it incorrectly as apart from correct result in some cell, in other cells result are being shown like this
a

ccounts@gmail.com

abc@

gmail.com

Please suggest

 

[bosco_yip]

......
@Bosco, that formula worked very well. But I afraid of using it incorrectly as apart from correct result in some cell, .....

Please attach a file regarding of your problem.

Regards