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]

How about this:

Load your data into power query​

Add a new column with the formula = "Yes"​

Select the attendance column and then click Pivot Column​

Select your new "yes" column as your values column​

Go to advanced options and select Don't Aggregate​

Hope this helps

 

[Peter_SSs]

For a formula method idea ..

23 09 13.xlsm
	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
18
Rearrange

Cell Formulas
Range	Formula
C12:F12	C12	=TRANSPOSE(SORT(UNIQUE(A2:A9)))
A13:B17	A13	=LET(d,UNIQUE(B2:C9),SORTBY(d,TAKE(d,,1),,TAKE(d,,-1),))
C13:F17	C13	=IF(COUNTIFS($A$2:$A$9,C$12,$B$2:$B$9,$A13,$C$2:$C$9,$B13),"Yes","")
Dynamic array formulas.

 

[tigerzen]

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.

 

[Peter_SSs]

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

Sorry, I missed that .. again

My previous suggestion produced the 'Attendance' headings, the list of names and the "Yes" values.
Are you looking for a method to do all that or are you really only interested in the "Yes" values or that and perhaps the names?

 

[tigerzen]

Sorry, I missed that .. again

My previous suggestion produced the 'Attendance' headings, the list of names and the "Yes" values.
Are you looking for a method to do all that or are you really only interested in the "Yes" values or that and perhaps the names?

No problem Peter, I will need all three items.

 

[Peter_SSs]

In that case I would use some helper columns (J:K for me) which could be hidden if you want.

23 09 13.xlsm
	A	B	C	D	E	F	G	H		J	K
1	Attendance	Surname	First name
2	Conference 1	Smith	Harry							Smith|Harry	5
3	Conference 1	Jones	Cathy							Jones|Cathy	3
4	Conference 2	Smith	Harry							Smith|Harry
5	Conference 2	Brown	Tom							Brown|Tom	0
6	Conference 2	Hendrix	Jim							Hendrix|Jim	1
7	Conference 3	Jones	Cathy							Jones|Cathy
8	Conference 3	Smith	Peter							Smith|Peter	7
9	Expo	Hendrix	Jim							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
18
Rearrange (2)

Cell Formulas
Range	Formula
J2:J9	J2	=B2&"|"&C2
K2:K9	K2	=IF(COUNTIF(J$1:J1,J2),"",COUNTIF(J$2:J$9,"<"&J2))
C12:G12	C12	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A2:$A9)/ISNA(MATCH($A2:$A9,$B12:B12,0)),1)),"")
A13:A18	A13	=IFERROR(INDEX(B$2:B$9,MATCH(SMALL(K$2:K$9,ROWS(A$13:A13)),K$2:K$9,0)),"")
B13:B18	B13	=IF(A13="","",INDEX(C$2:C$9,MATCH(SMALL(K$2:K$9,ROWS(A$13:A13)),K$2:K$9,0)))
C13:G17	C13	=IF(COUNTIFS($J$2:$J$9,$A13&"|"&$B13,$A$2:$A$9,C$12),"Yes","")

 

[Glenn Kennedy]

Clear the stuff from the results area.

A13, copied down:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$1:$B$9)/(COUNTIFS($A$12:$A12,$B$1:$B$9,$B$12:$B12,$C$1:$C$9)=0),1)),"")

B13, copied down:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($B$1:$B$9)/(COUNTIFS($A$12:$A12,$B$1:$B$9,$B$12:$B12,$C$1:$C$9)=0),1)),"")

C13, copied across and down:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$1:$A$9)/($B$1:$B$9&$C$1:$C$9=$A13&$B13),COLUMNS($C13:C13))),"")

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

 

[Glenn Kennedy]

Clear the stuff from the results area.

A13, copied down:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$1:$B$9)/(COUNTIFS($A$12:$A12,$B$1:$B$9,$B$12:$B12,$C$1:$C$9)=0),1)),"")

B13, copied down:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($B$1:$B$9)/(COUNTIFS($A$12:$A12,$B$1:$B$9,$B$12:$B12,$C$1:$C$9)=0),1)),"")

C13, copied across and down:
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$1:$A$9)/($B$1:$B$9&$C$1:$C$9=$A13&$B13),COLUMNS($C13:C13))),"")

 

[Glenn Kennedy]

Nope. I have no idea how to upload it...