Reconfiguring data

[tigerzen]

I have a list in the following format but require it in the desired format, looking for a formula method which can consist of a number of steps or Power Query solution. Please note, I will need an Excel 2019 solution.

Book10
	A	B	C	D	E	F
1	Attendance	Surname	First name
2	Conference 1	Smith	Harry
3	Conference 1	Jones	Cathy
4	Conference 2	Smith	Harry
5	Conference 2	Brown	Tom
6	Conference 2	Hendrix	Jim
7	Conference 3	Jones	Cathy
8	Conference 3	Smith	Peter
9	Expo	Hendrix	Jim
10
11	Desired format
12	Surname	First name	Conference 1	Conference 2	Conference 3	Expo
13	Brown	Tom		Yes
14	Hendrix	Jim		Yes		Yes
15	Jones	Cathy	Yes		Yes
16	Smith	Harry	Yes	Yes
17	Smith	Peter			Yes
Sheet4

 

[JB2020]

Thanks JB and Peter.
JB, I may need some more detail with your solution, do you mean add a conditional column?

Peter, the solution needs to accommodate an Excel 2019 user.

You would need to go to Add Column > Custom Column and use = "Yes" as the formula, so your data will look like this:

Afterwards, select the attendance column, then go to Transform>Pivot Column and choose the setting below

It should look like this afterwards:

 

[Peter_SSs]

I haven't figured out how to add the XL2BB yet!!

Have you worked carefully through the instructions on the page linked in my signature block below?
I also suggest using the Test Here forum until you have it worked out rather than in an actual thread like this. If difficulties with XL2BB persist, also look for solutions in the About This Board forum and/or post specific questions about the Add-In in that forum.

I don't know if either is important to the OP but in relation to your suggested formulas.

• The desired format in post #1 has the names listed alphabetically by surname and then first name and your column A:B formulas do not do that.
• Your formula for columns C, D, E etc also do not produce an outcome in the stated desired format.

 

[tigerzen]

Thanks to everyone who contributed!
Thanks JB for the visual additions, I was able to get the desired solution easily after seeing your extra post.
Thanks Glenn for your suggestion, I love what you can achieve with just 3 formulae, I do need the final solution to contain the YES's as per my opening post to easily track attendance.
Thanks again Peter for a clever, creative solution, I can follow your thinking and it does indeed provide the solution that I require.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.