Extract Unique List from Multiple Columns based on Multiple Criteria

[Yvonne Love]

Hi all!

I need to create a unique list from multiple columns based on dynamic criteria. In the spreadsheet I uploaded, you'll see a list, which has already been pared down, showing an event, and then registrants for that event in separate columns (I've already used Excel formulas to extract the registrants, email addresses, and titles into separate columns from data that is pulled from a database). Now what I need to do is be able to create list of all registrants for the specific event even though they are in separate columns.

I've used array formulas to extract a unique list based on criteria from one column, and I have used array formulas to extract a unique list from multiple columns, but now I need to be able to combine the two. Is this possible?

Another alternative would be to pull all of the registrants but then have to use index match across multiple columns to pull the data, and then just filter the list by event. I'm open to that option as well if that is the only/best option.

Thanks,
Y

 

[Nebu]

Hi:
Is this what you are looking for?

Thanks

 

[r2c2]

@Yvonne Love Why not use Power Query to unpivot the data?

It is easy. You just load the table data in to PQ, select first three columns and then select "unpivot other columns"
Once the data is unpivoted, you can see all the details easily by either filtering (or slicing). You can even use formulas.

See attached

 

[Yvonne Love]

Thanks so much r2c2! I have still just scratched the surface with Power Query, but this has given me a lot to work with!

 

[Yvonne Love]

@r2c2 , I'm super close, I think. I have attached two tests. The one you sent back was using as a match value "Symp Reg 1", so the list that was extracted via a formula didn't pull any of the registrants if they weren't listed as Symp Reg 1. So I used Power Query to unify the attributes (replace values Reg 1 to Reg, Reg 2 to Reg, etc.), and then modified the formula to look for Symp Reg. That pulled the right folks. But if you look at the attempt.xlsx file, you can see that in cell J11, it lists a name because there was not a Symp Reg Title after Symp Reg Email at row 24. So it was pulling the name where Symp Reg Title would have been.

I also then tried to use Power Query to repivot the columns (attempt 2.xlsx). I first removed the blank values from the values column and then used the Pivot Column on the UI to re-pivot. I used the advanced options and selected "Don't Aggregate", but I got errors. Again, still new to Power Query, but from what I looked up, I thought that would have worked, but it didn't. If pivoting the columns would work somehow, I think that would actually be the best option.

Any thoughts on this?

Thanks again for you help!!

 

[Yvonne Love]

@r2c2 - I solved it. I added an index column before pivoting the columns, and then I filled up for the email and title, and then removed null and blank values from the name, and it gave me the complete list of registrants (don't know why it didn't work without the index column, but this works now, so I'm good!). And it's in a table so we can sort, filter, and even add formulas as necessary!

Thank you!!
Yvonne

 

[r2c2]

@Yvonne Love : I am glad you figured it out