Question description:
This user has given permission to use the problem statement for this
blog.
Hi I am generating a large data file that I would like to create multiple lists from. I have a list of first names in column C and last names on column D emails in column E. I need it to consolidate the info to make a master list of Last name and if the email addresses match to only bring back the name list once onto a new sheet. I have multiple fist names with the same last name that have the same email address. I also have multiple last names with different email address. Can you help?
Solved by C. C. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
31/07/2018 - 03:16
hi
Excelchat Expert
31/07/2018 - 03:16
Hi...
User
31/07/2018 - 03:16
Was the way I formed my question understandable?
Excelchat Expert
31/07/2018 - 03:17
Based on the description.. you need list to be built..
User
31/07/2018 - 03:17
yes
Excelchat Expert
31/07/2018 - 03:17
do you have sample data to share?
User
31/07/2018 - 03:17
yes hold on one moment
Excelchat Expert
31/07/2018 - 03:18
sure..
User
31/07/2018 - 03:19
here you go
[Uploaded an Excel file]
User
31/07/2018 - 03:20
On the sheet labeled "New" it has the lists I need to generate
Excelchat Expert
31/07/2018 - 03:20
let me check give me few min
Excelchat Expert
31/07/2018 - 03:22
so you want all the unique emails then then corresponding member together ?
Excelchat Expert
31/07/2018 - 03:22
example
Excelchat Expert
31/07/2018 - 03:23
123@yahoo.com - Abbot Cole Abbot Gavin Abbot
Excelchat Expert
31/07/2018 - 03:23
family name is abbot
User
31/07/2018 - 03:24
Yes and I want it to populate the email column on sheet "New" with what email address if tied to the family name
User
31/07/2018 - 03:24
only if the email addresses match
User
31/07/2018 - 03:24
I alsi have at the bottom of the list Murdock
User
31/07/2018 - 03:24
same last name different email
Excelchat Expert
31/07/2018 - 03:24
so it will be different
User
31/07/2018 - 03:24
so I need it to bring over the name for the number of different email listed
Excelchat Expert
31/07/2018 - 03:26
ok..in New there is row24..Family Name Oldest Child Name Grade 1 Grade 2 Grade 3 Grade 4
this is not possible
Excelchat Expert
31/07/2018 - 03:26
instead i can get family menber names and grades
Excelchat Expert
31/07/2018 - 03:26
only 2 columns would that help
User
31/07/2018 - 03:26
possibly
Excelchat Expert
31/07/2018 - 03:26
let me build
User
31/07/2018 - 03:26
I just need to also have the eldest child listed by first name
User
31/07/2018 - 03:27
ok thank you
Excelchat Expert
31/07/2018 - 03:27
hmm will try since the grades are stoed as 6th and 8th it may be difficult to get the eldest ... let me check
User
31/07/2018 - 03:28
thanks
User
31/07/2018 - 03:31
my email is jamieluithly@gmail.com if we get cut off
Excelchat Expert
31/07/2018 - 03:32
We would get an 20 min extention over this..
User
31/07/2018 - 03:32
great thanks
Excelchat Expert
31/07/2018 - 03:32
We aren't allowed to send out emails or other pesonal communication
Excelchat Expert
31/07/2018 - 03:32
completed the 1st part
[Uploaded an Excel file]
Excelchat Expert
31/07/2018 - 03:32
cahn you confirm
User
31/07/2018 - 03:33
part one looks good
Excelchat Expert
31/07/2018 - 03:33
ok
Excelchat Expert
31/07/2018 - 03:34
will the data be ordered?
User
31/07/2018 - 03:34
sorry I'm not sure what you mean by ordered?
User
31/07/2018 - 03:35
in order by a specific column?
Excelchat Expert
31/07/2018 - 03:36
like in your example all family members are together will that be the case?
User
31/07/2018 - 03:37
no
User
31/07/2018 - 03:37
they will be ignorer by grade
User
31/07/2018 - 03:37
then by last name
Excelchat Expert
31/07/2018 - 03:37
ok
Excelchat Expert
31/07/2018 - 03:47
check sheet 1
[Uploaded an Excel file]
Excelchat Expert
31/07/2018 - 03:47
it isnt possible to achive your requirement in formula
Excelchat Expert
31/07/2018 - 03:47
hence i created a pivot
User
31/07/2018 - 03:47
ok let me take a look
Excelchat Expert
31/07/2018 - 03:48
ig you sort by grade then the eldest will always be on the top of the group
Excelchat Expert
31/07/2018 - 03:48
Name 2 is your family name and Name 1 is the name of all members
User
31/07/2018 - 03:49
What if I added a column that had a graduating year?
User
31/07/2018 - 03:49
would it then be possible?
User
31/07/2018 - 03:50
because I have to send this file to someone who is not computer savvy at all
Excelchat Expert
31/07/2018 - 03:51
what do you need changed in Sheet1..
User
31/07/2018 - 03:52
I don't want it to be a pivot table
User
31/07/2018 - 03:53
I would like it to lookup the info on the Master sheet and bring back the name of the eldest child on a separate sheet
User
31/07/2018 - 03:53
If I added a column to the Master sheet that indicates the year the child will graduate will that be possible to generate the info I need?
Excelchat Expert
31/07/2018 - 03:54
Let me try since you mention there are cases like Murdock it is difficult to handel in a formula
User
31/07/2018 - 03:54
will it also have to look at the email
Excelchat Expert
31/07/2018 - 03:56
let me try to work on the formula
Excelchat Expert
31/07/2018 - 03:56
give me few min
User
31/07/2018 - 03:56
thank you
Excelchat Expert
31/07/2018 - 04:08
eldest remaining
[Uploaded an Excel file]
Excelchat Expert
31/07/2018 - 04:12
you there
User
31/07/2018 - 04:13
yes sorry I was looking at it not exactly what I need. I don't need to know how many kids there are in the family I need to have the name of the child who is oldest
User
31/07/2018 - 04:14
I will send you another file with a graduate date column
Excelchat Expert
31/07/2018 - 04:14
[Uploaded an Excel file]
Excelchat Expert
31/07/2018 - 04:14
no need got it
Excelchat Expert
31/07/2018 - 04:15
they are all array formula.. please make sure you press cntrl+shift+enter when you incorporate it in your excel
Excelchat Expert
31/07/2018 - 04:15
It was a challenging task :)
Excelchat Expert
31/07/2018 - 04:15
Please do leave a feedback
User
31/07/2018 - 04:15
I know it was thank you so much for all your help :)
Excelchat Expert
31/07/2018 - 04:16
:)
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.
