A way to collect the like data (email) from different Columns

Debbie Petersen

Hi,

I have the need to extract a list from a sheet that will give me a list of all of the employees another employee is assigned to review.

My row contains is the name of the person who is submitting a review on others.

I have 5 columns that will contain the email address of the person who will be submitting a review. There are 106 rows that employees email can be listing on in any one of hte 5 columns.

I want a report or sheet that will give me the names of all the people that one reviewer has been assigned to:

Example - Who are all the people that DebbieP has been assigned to in all 5 columns on any rows.

I cannot figure this out and I've been on it so long I'm betting it's so simple I will kick myself!

Thank you!

Paul Newcome

You would have to use a JOIN/COLLECT 5 separate times and "add" them together.

=JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 1st 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 2nd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 3rd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 4th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 5th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10))

Paul Newcome

Happy to help. 👍️

Paul Newcome

You would have to use a JOIN/COLLECT 5 separate times and "add" them together.

=JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 1st 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 2nd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 3rd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 4th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 5th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10))

Debbie Petersen

I tried this out and I am assuming the Source Sheet Name Column would be the one shown above labeled Person to Submit 360. I am getting an #invalid ref error. Here's the formula

=JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 1st 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 2nd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 3rd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 4th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 5th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10))

Devin Lee

Not sure if those columns are Contact Lists or Text/Number. If they are Contact Lists you can create a filter based on current user which is what I imagine you are trying to do so people can tell what they are assigned to. If not, you would need to create a filter for each email address used and have each person select their name in the filter. You can do the same thing with a report so the filter is already applied when they open it.

Debbie Petersen

Thank you Lee - that's what I'm trying to avoid as there are 106 ppl on the list and trying to create report for VPs to see who their employees are reviewing.

Paul Newcome

Did you make sure to create all of the cross sheet references?

Debbie Petersen

Got it to work! Thanks so much! I just needed the string once and it pulled all the names in!

Thank you both so much and Happy Thanksgiving!

Paul Newcome

Happy to help. 👍️