Question description:
This user has given permission to use the problem statement for this
blog.
[REPOST] Hi, I have a list of names in 1 column, with title, company, etc in a row and each person has multiple emails. Each different email is in a different column. I want to copy each row and align each different email so the email is in the same column with name, title etc repeating.
Solved by A. S. in 46 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/04/2018 - 04:22
Hi
User
09/04/2018 - 04:22
Hey!
Excelchat Expert
09/04/2018 - 04:22
Sorry for the inconvenience caused
Excelchat Expert
09/04/2018 - 04:22
Let me try to fix your problem
User
09/04/2018 - 04:23
Thank you!
User
09/04/2018 - 04:23
See the example I pasted?
Excelchat Expert
09/04/2018 - 04:23
yes. i saw that.
User
09/04/2018 - 04:23
Each person is a different person regardless if name or company is the same
User
09/04/2018 - 04:23
I want the email in 1 column
User
09/04/2018 - 04:23
as in Sheet 2
Excelchat Expert
09/04/2018 - 04:23
ok. let me do it.
Excelchat Expert
09/04/2018 - 04:23
two minutes
User
09/04/2018 - 04:24
Sweet Thanks
Excelchat Expert
09/04/2018 - 04:24
Is it re posting third time?
User
09/04/2018 - 04:24
huh?
Excelchat Expert
09/04/2018 - 04:24
I am just asking its mentioned as Repost
User
09/04/2018 - 04:25
Yes 3rd time
Excelchat Expert
09/04/2018 - 04:25
oops...Really Sorry
Excelchat Expert
09/04/2018 - 04:25
Give me 2 minutes
Excelchat Expert
09/04/2018 - 04:25
do you want the answer in Sheet1 or sheet2 ?
User
09/04/2018 - 04:26
Create new sheet, this is only an example. I have a list of over 100 names
Excelchat Expert
09/04/2018 - 04:26
I will put in the same sheet it will be very easy for you
User
09/04/2018 - 04:26
ok
Excelchat Expert
09/04/2018 - 04:35
Doing that and request you to extend time
User
09/04/2018 - 04:36
When I see the prompt I will
Excelchat Expert
09/04/2018 - 04:36
sure.
Excelchat Expert
09/04/2018 - 04:45
Doing names are repeating
Excelchat Expert
09/04/2018 - 04:45
so its taking more time.
User
09/04/2018 - 04:46
I understand it's not easy
User
09/04/2018 - 04:47
Do you know a formula that would allow you to lookup a value within a range and then give you a row value that the lookup value was found on, without knowing the row number but the column number? Kinda a reverse vlookup
Excelchat Expert
09/04/2018 - 04:48
yes. last stage
User
09/04/2018 - 04:49
YAY!
Excelchat Expert
09/04/2018 - 04:50
i hope you will give good feedback for me.
Excelchat Expert
09/04/2018 - 04:50
:)
Excelchat Expert
09/04/2018 - 04:51
Here you go
[Uploaded an Excel file]
Excelchat Expert
09/04/2018 - 04:52
??
User
09/04/2018 - 04:52
I'm looking it over
Excelchat Expert
09/04/2018 - 04:53
Your result will be from Y to AD
Excelchat Expert
09/04/2018 - 04:53
In between few columns are used for calculation.
User
09/04/2018 - 04:54
Can you walk me through what you did?
Excelchat Expert
09/04/2018 - 04:54
yes sure..
Excelchat Expert
09/04/2018 - 04:54
Column N - Counted Number of Emailids
Excelchat Expert
09/04/2018 - 04:54
you got it?
User
09/04/2018 - 04:55
Yes
Excelchat Expert
09/04/2018 - 04:55
you can delete column o Result its not required.
User
09/04/2018 - 04:55
alright
Excelchat Expert
09/04/2018 - 04:55
FRom Q I one Id for each person
Excelchat Expert
09/04/2018 - 04:56
*For column Q I just created one number for each person
Excelchat Expert
09/04/2018 - 04:56
Based on number of Emails count
Excelchat Expert
09/04/2018 - 04:56
Q to U
User
09/04/2018 - 04:56
ok
Excelchat Expert
09/04/2018 - 04:56
Since names are duplicates I just combined name and role
Excelchat Expert
09/04/2018 - 04:57
Based on the output Column W I just created SNo.
Excelchat Expert
09/04/2018 - 04:57
Based on the W value I just used vlookup to take the combined value.
Excelchat Expert
09/04/2018 - 04:58
??
Excelchat Expert
09/04/2018 - 04:58
Are you there?
User
09/04/2018 - 04:58
Yes got it
Excelchat Expert
09/04/2018 - 04:59
super :)
User
09/04/2018 - 04:59
keep walking me through
Excelchat Expert
09/04/2018 - 04:59
then Just segregated name and Role in column Y and Z
Excelchat Expert
09/04/2018 - 04:59
AA - Just vlookup to get company name
Excelchat Expert
09/04/2018 - 05:00
Since we need to take email Id every time we need to refer different column result
Excelchat Expert
09/04/2018 - 05:00
AB - I used countif Number of times Particular value repeated.
Excelchat Expert
09/04/2018 - 05:01
AC - Based on the Value I have which column Email id to be lookup.
Excelchat Expert
09/04/2018 - 05:01
That's it.
Excelchat Expert
09/04/2018 - 05:02
AC is column index number for vlookup function in column AD
User
09/04/2018 - 05:02
Column AD is a vlookup for Col X
Excelchat Expert
09/04/2018 - 05:03
Yes..
Excelchat Expert
09/04/2018 - 05:03
Forgot to tell you , First step I have created Name & Role combined together in Column A
User
09/04/2018 - 05:04
Ohhhh
Excelchat Expert
09/04/2018 - 05:04
yep.
User
09/04/2018 - 05:05
What is Col Q?
User
09/04/2018 - 05:05
It's a serial number?
Excelchat Expert
09/04/2018 - 05:05
Its ID for Each person
Excelchat Expert
09/04/2018 - 05:05
1 to 2 it will be allocated to John Smith
Excelchat Expert
09/04/2018 - 05:06
3 allocated to Jane
Excelchat Expert
09/04/2018 - 05:06
Kind of Serial Number
Excelchat Expert
09/04/2018 - 05:06
Is it ok?
User
09/04/2018 - 05:07
Yes, I believe this will work
User
09/04/2018 - 05:07
THANK YOU SO MUCH!!!!
Excelchat Expert
09/04/2018 - 05:07
100% .. No worries :)
Excelchat Expert
09/04/2018 - 05:07
Request you to provide your valuable feedback and remarks.
User
09/04/2018 - 05:07
Will do!
Excelchat Expert
09/04/2018 - 05:07
Thanks for your patience and contacting us.
User
09/04/2018 - 05:07
Have a fantastic day!!!
Excelchat Expert
09/04/2018 - 05:08
Have a nice day to you :)
Excelchat Expert
09/04/2018 - 05:08
Please end the session
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
