**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.*