Formula Help for duplicate emails and bring up data from below cells

[TNewton]

Hello I am not sure the terms, so I will try my best to describe what I need. I am rusty in Excel.

I have a database that keeps track of what email contacts comes from what Reports which leads the email column having duplicates because the emails show up on other reports.

I need only one entry for the contact email and the 'X" that marks what reports the email contact is on brought into one column. Please see picture as a simple example.

I manually have to go through the list and move all the 'X' to the first line and delete the duplicates and since the database is now larger than it used to be, it's taking me a long time to figure out a better way.

I was thinking an If Function, but not sure how to bring up the 'data' from the other cells.

Thank you to anyone who can help me complete this task.

 

[Legacy 143009]

Assuming your data starts from A3, your results start from A15:

=IFERROR(INDEX($B$3:$E$8,MATCH(1,INDEX($B$3:$E$8="x",0,MATCH(B$14,$B$2:$E$2,0))*($A$3:$A$8=$A15),0),MATCH(B$14,$B$2:$E$2,0)),"")

 

[TNewton]

Thank you for your time, I don't think I explained what I needed properly and I left out the column and row identifiers (sorry) so I will try again and include the full headers I have in my original spreadsheet.

This is an example I have to start with:

This data is exported by a report that lists email contacts that uses certain "Reports/AKA Services the customer uses" (Reports 1-5). These "Reports" captures every time the customer uses them, which makes duplicates email entries in D. For my use I need to convert this information so that there is only one entry for email and the Reports are X'ed on one line. (Other words: Test@email.com is listed 4 times because they showed up on "Report 1 and 3" twice. Test2@email.com is listed three times because they show up on "Report 1 and twice on Report 3").

What I manually do is on Row 2 and 6 I would move up all the X's associated with that email according to the 'Report" then delete the duplicates (Row 3-5 and 7-8). which is taking me days to complete. What I need is if there is a duplicate email in D, I need a formula to look at N-R and bring up all the X's to the first line of the email (or if the X's will be fill for every email entry that is fine as I can use the delete duplicates feature later). Example of what I mean:

I need it to look like the below when all is finished:

I can only use Excel, and I can duplicate the Report rows N-R further over the spreadsheet to enter a formula and use the paste values feature back into N-R if needed.

 

[Legacy 143009]

Could you please share the sample file please (the first file)? I can make the necessary changes and share you back.

 

[TNewton]

Of course, how do I do that here? I am on my work computer right now, but if you require me to download anything I will have to complete after work. Again thank you for your time on this

 

[Legacy 143009]

Can you share via wetransfer or another file hosting service? I feel too lazy to rewrite all

 

[TNewton]

apparently I need your email address to send it to you, which makes no sense because I could just email it to you directly...

 

[Fluff]

You do not need an email address & files should not be shared privately.
You can upload to a share site & mark for sharing & then post the link you are given to the thread.

 

[TNewton]

I understand that, I was saying that it didn't give the option to share without an email address. Here is a google doc link, if this doesnt work I don't know what else to do, because the instructions to use XL2BB is not working either nor is it beginner friendly.

Test Example.xlsx

Sheet1 First Name,Last Name,Position,Email,Company Name,BP,2ND BP,BRANCH,PR,EN/FR,RITS,MDS,INFO,Report 1,Report 2,Report 3,Report 4,Report 5 Customer 1,<a href="mailto:test@email.com">test@email.com</a>,Company 1,X,x Customer 1,<a href="mailto:test@email.com">test@email.com</a>,Company 1,X,x Cus...

docs.google.com

 

[Legacy 143009]

Please find the file link below:

easyupload.io

easyupload.io

easyupload.io