Complex sorting issue from vertical to horizontal.

AWM21

AWM21

New Member
Joined
Aug 2, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello everyone, I may be asking too much.

I have a vertical table of data that needs to be redistributed horizontally to prep for a data set that Photoshop can read. Transpose isn't working the way I expected it would.

My data shows the attendees of who participated in certain events and as a result, earned credit hours.
The end product will organize the vertical data to a new sheet in a horizontal line, keeping the personal data unique, and the event/hours details populating to cells to the right incrementally, as needed.
So, If person_01 has attended (5) events, the data on those in the vertical sheet will need to display horizontal, without repeating the attendee name and company data.
Right now, I know I can do this manually by using a couple workarounds, but there are 1500 people and I feel there is a sorting formula solution I can use, but I just cant get my head around it.

I have (2) mini sheets showing a sample of the start product, and the desired end product.
Thanks for looking at this with me.

STARTING SHEET:
Help Master.txt
ABCDEF
1Attendee_NameOrganisationDateCredHourSesNumSesName
2Person_01person_01_company08/03/20211MGSEvent_A
3Person_01person_01_company08/26/20211R03Event_D
4Person_01person_01_company08/26/20211R08Event_F
5Person_01person_01_company08/26/20211R15Event_H
6Person_01person_01_company08/26/20211R23Event_L
7Person_01person_01_company08/26/20211RGSEvent_M
8Person_01person_01_company08/24/20211T04Event_P
9Person_01person_01_company08/24/20211T10Event_R
10Person_01person_01_company08/24/20211T13Event_T
11Person_01person_01_company08/24/20211T23Event_V
12Person_01person_01_company08/24/20211TGSEvent_X
13Person_01person_01_company08/25/20211W04Event_Z
14Person_01person_01_company08/25/20211W09Event_AB
15Person_01person_01_company08/25/20211W15Event_AF
16Person_01person_01_company08/25/20211W23Event_AI
17Person_01person_01_company08/25/20211WGSEvent_AJ
18Person_02person_02_company08/03/20211MGSEvent_A
19Person_02person_02_company08/26/20211R08Event_F
20Person_02person_02_company08/26/20211R13Event_G
21Person_02person_02_company08/26/20211RGSEvent_M
22Person_02person_02_company08/24/20211T08Event_Q
23Person_02person_02_company08/24/20211T18Event_U
24Person_02person_02_company08/24/20211T24Event_W
25Person_02person_02_company08/24/20211TGSEvent_X
26Person_02person_02_company08/25/20211W10Event_AC
27Person_02person_02_company08/25/20211W21Event_AH
28Person_03person_03_company08/03/20211MGSEvent_A
29Person_03person_03_company08/26/20211R02Event_C
30Person_03person_03_company08/26/20211R06Event_E
31Person_03person_03_company08/26/20211R17Event_I
32Person_03person_03_company08/26/20211R20Event_K
33Person_03person_03_company08/26/20211RGSEvent_M
34Person_03person_03_company08/24/20211T01Event_N
35Person_03person_03_company08/24/20211T11Event_S
36Person_03person_03_company08/24/20211T13Event_T
37Person_03person_03_company08/24/20211T24Event_W
38Person_03person_03_company08/24/20211TGSEvent_X
39Person_03person_03_company08/25/20211W04Event_Z
40Person_03person_03_company08/25/20211W06Event_AA
41Person_03person_03_company08/25/20211W19Event_AG
42Person_03person_03_company08/25/20211WGSEvent_AJ
43Person_04person_04_company08/03/20211MGSEvent_A
44Person_04person_04_company08/26/20211R01Event_B
45Person_04person_04_company08/26/20211R08Event_F
46Person_04person_04_company08/26/20211R19Event_J
47Person_04person_04_company08/26/20211RGSEvent_M
48Person_04person_04_company08/24/20211T02Event_O
49Person_04person_04_company08/24/20211T08Event_Q
50Person_04person_04_company08/25/20211W02Event_Y
51Person_04person_04_company08/25/20211W12Event_AD
52Person_04person_04_company08/25/20211W14Event_AE
53Person_04person_04_company08/25/20211WGSEvent_AJ
faux



DESIRED END PRODUCT:
Help Master.txt
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1Attendee_NameOrganisationtotalCredsDate0SesNum0SesName0Date1SesNum1SesName1Date2SesNum2SesName2Date3SesNum3SesName3Date4SesNum4SesName4Date5SesNum5SesName5Date6SesNum6SesName6Date7SesNum7SesName7Date8SesNum8SesName8Date9SesNum9SesName9Date10SesNum10SesName10Date11SesNum11SesName11Date12SesNum12SesName12Date13SesNum13SesName13Date14SesNum14SesName14Date15SesNum15SesName15
2Person_01person_01_company1608/23/2021MGSEvent_A08/24/2021T04Event_P08/24/2021T10Event_R08/24/2021T13Event_T08/24/2021T23Event_V08/24/2021TGSEvent_X08/25/2021W04Event_Z08/25/2021W09Event_AB08/25/2021W15Event_AF08/25/2021W23Event_AI08/25/2021WGSEvent_AJ08/26/2021R03Event_D08/26/2021R08Event_F08/26/2021R15Event_H08/26/2021R23Event_L08/26/2021RGSEvent_M
3Person_02person_02_company1008/23/2021MGSEvent_A08/24/2021T08Event_Q08/24/2021T18Event_U08/24/2021T24Event_W08/24/2021TGSEvent_X08/25/2021W10Event_AC08/25/2021W21Event_AH08/26/2021R08Event_F08/26/2021R13Event_G08/26/2021RGSEvent_M
4Person_03person_03_company1508/23/2021MGSEvent_A08/24/2021T01Event_N08/24/2021T11Event_S08/24/2021T13Event_T08/24/2021T24Event_W08/24/2021TGSEvent_X08/25/2021W04Event_Z08/25/2021W06Event_AA08/25/2021W19Event_AG08/25/2021WGSEvent_AJ08/26/2021R02Event_C08/26/2021R06Event_E08/26/2021R17Event_I08/26/2021R20Event_K08/26/2021RGSEvent_M
5Person_04person_04_company1108/23/2021MGSEvent_A08/24/2021T02Event_O08/24/2021T08Event_Q08/25/2021W02Event_Y08/25/2021W12Event_AD08/25/2021W14Event_AE08/25/2021WGSEvent_AJ08/26/2021R01Event_B08/26/2021R08Event_F08/26/2021R19Event_J08/26/2021RGSEvent_M
faux (2)
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
How about
+Fluff 1.xlsm
HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
2Person_01person_01_company1603/08/2021MGSEvent_A26/08/2021R03Event_D26/08/2021R08Event_F26/08/2021R15Event_H26/08/2021R23Event_L26/08/2021RGSEvent_M24/08/2021T04Event_P24/08/2021T10Event_R24/08/2021T13Event_T24/08/2021T23Event_V24/08/2021TGSEvent_X25/08/2021W04Event_Z25/08/2021W09Event_AB25/08/2021W15Event_AF25/08/2021W23Event_AI25/08/2021WGSEvent_AJ
3Person_02person_02_company1003/08/2021MGSEvent_A26/08/2021R08Event_F26/08/2021R13Event_G26/08/2021RGSEvent_M24/08/2021T08Event_Q24/08/2021T18Event_U24/08/2021T24Event_W24/08/2021TGSEvent_X25/08/2021W10Event_AC25/08/2021W21Event_AH
4Person_03person_03_company1503/08/2021MGSEvent_A26/08/2021R02Event_C26/08/2021R06Event_E26/08/2021R17Event_I26/08/2021R20Event_K26/08/2021RGSEvent_M24/08/2021T01Event_N24/08/2021T11Event_S24/08/2021T13Event_T24/08/2021T24Event_W24/08/2021TGSEvent_X25/08/2021W04Event_Z25/08/2021W06Event_AA25/08/2021W19Event_AG25/08/2021WGSEvent_AJ
5Person_04person_04_company1103/08/2021MGSEvent_A26/08/2021R01Event_B26/08/2021R08Event_F26/08/2021R19Event_J26/08/2021RGSEvent_M24/08/2021T02Event_O24/08/2021T08Event_Q25/08/2021W02Event_Y25/08/2021W12Event_AD25/08/2021W14Event_AE25/08/2021WGSEvent_AJ
6
Sheet3
Cell Formulas
RangeFormula
H2:I5H2=UNIQUE(FILTER(A2:B100,A2:A100<>""))
J2:J5J2=SUMIFS(D:D,A:A,INDEX(H2#,,1))
K2:BF2,K5:AQ5,K4:BC4,K3:AN3K2=LET(Fltr,FILTER($C$2:$F$100,$A$2:$A$100=H2),Rws,ROWS(Fltr)*3,Ary,INDEX(Fltr,SEQUENCE(Rws),{1,3,4}),INDEX(Ary,INT(SEQUENCE(,Rws,,1/3)),MOD(SEQUENCE(,Rws,3),3)+1))
Dynamic array formulas.
 
Upvote 0
Solution
Another way using Power Query.

Book2
HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
1Attendee_NameOrganisationCreditsDate1SesNum1SesName1Date2SesNum2SesName2Date3SesNum3SesName3Date4SesNum4SesName4Date5SesNum5SesName5Date6SesNum6SesName6Date7SesNum7SesName7Date8SesNum8SesName8Date9SesNum9SesName9Date10SesNum10SesName10Date11SesNum11SesName11Date12SesNum12SesName12Date13SesNum13SesName13Date14SesNum14SesName14Date15SesNum15SesName15Date16SesNum16SesName16
2Person_01person_01_company168/3/2021 12:00:00 AMMGSEvent_A8/26/2021 12:00:00 AMR03Event_D8/26/2021 12:00:00 AMR08Event_F8/26/2021 12:00:00 AMR15Event_H8/26/2021 12:00:00 AMR23Event_L8/26/2021 12:00:00 AMRGSEvent_M8/24/2021 12:00:00 AMT04Event_P8/24/2021 12:00:00 AMT10Event_R8/24/2021 12:00:00 AMT13Event_T8/24/2021 12:00:00 AMT23Event_V8/24/2021 12:00:00 AMTGSEvent_X8/25/2021 12:00:00 AMW04Event_Z8/25/2021 12:00:00 AMW09Event_AB8/25/2021 12:00:00 AMW15Event_AF8/25/2021 12:00:00 AMW23Event_AI8/25/2021 12:00:00 AMWGSEvent_AJ
3Person_02person_02_company108/3/2021 12:00:00 AMMGSEvent_A8/26/2021 12:00:00 AMR08Event_F8/26/2021 12:00:00 AMR13Event_G8/26/2021 12:00:00 AMRGSEvent_M8/24/2021 12:00:00 AMT08Event_Q8/24/2021 12:00:00 AMT18Event_U8/24/2021 12:00:00 AMT24Event_W8/24/2021 12:00:00 AMTGSEvent_X8/25/2021 12:00:00 AMW10Event_AC8/25/2021 12:00:00 AMW21Event_AH
4Person_03person_03_company158/3/2021 12:00:00 AMMGSEvent_A8/26/2021 12:00:00 AMR02Event_C8/26/2021 12:00:00 AMR06Event_E8/26/2021 12:00:00 AMR17Event_I8/26/2021 12:00:00 AMR20Event_K8/26/2021 12:00:00 AMRGSEvent_M8/24/2021 12:00:00 AMT01Event_N8/24/2021 12:00:00 AMT11Event_S8/24/2021 12:00:00 AMT13Event_T8/24/2021 12:00:00 AMT24Event_W8/24/2021 12:00:00 AMTGSEvent_X8/25/2021 12:00:00 AMW04Event_Z8/25/2021 12:00:00 AMW06Event_AA8/25/2021 12:00:00 AMW19Event_AG8/25/2021 12:00:00 AMWGSEvent_AJ
5Person_04person_04_company118/3/2021 12:00:00 AMMGSEvent_A8/26/2021 12:00:00 AMR01Event_B8/26/2021 12:00:00 AMR08Event_F8/26/2021 12:00:00 AMR19Event_J8/26/2021 12:00:00 AMRGSEvent_M8/24/2021 12:00:00 AMT02Event_O8/24/2021 12:00:00 AMT08Event_Q8/25/2021 12:00:00 AMW02Event_Y8/25/2021 12:00:00 AMW12Event_AD8/25/2021 12:00:00 AMW14Event_AE8/25/2021 12:00:00 AMWGSEvent_AJ
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Date", type text}}, "en-US"),{"Date", "SesNum", "SesName"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Group = Table.Group(Merge, {"Attendee_Name", "Organisation"}, {{"Credits", each List.Sum([CredHour]), type number}, {"D", each Text.Combine([Merged],";"), type text}}),
    Split = Table.SplitColumn(Group, "D", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
    Col= Table.ColumnCount(Split)-3,
    lst = List.Generate(()=>0, each _ <Col, each _+1, each if Number.Mod(_,3)=0 then "Date" & Text.From(Number.IntegerDivide(_,3)+1) else if Number.Mod(_,3)=1 then "SesNum" & Text.From(Number.IntegerDivide(_,3)+1) else "SesName" & Text.From(Number.IntegerDivide(_,3)+1)),
    org = List.Generate(()=>1, each _ <= Col, each _+1, each "D." & Text.From(_)),
    Rename = Table.RenameColumns(Split, List.Zip({org,lst}) )
in
    Rename
 
Upvote 0
Thank you both for these answers. I wish I could mark them both correct, but it will let me only choose one.
I was having a hard time getting my head around how to get the sessions to organize. I've never used the (=LET) formula before. I really appreciate the help. It not only helped me with my immediate issue, but I can think of a few times I could have used this in the past. It really helps make me a better user.

thanks again!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Fluff said:
How about
+Fluff 1.xlsm
HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
2Person_01person_01_company1603/08/2021MGSEvent_A26/08/2021R03Event_D26/08/2021R08Event_F26/08/2021R15Event_H26/08/2021R23Event_L26/08/2021RGSEvent_M24/08/2021T04Event_P24/08/2021T10Event_R24/08/2021T13Event_T24/08/2021T23Event_V24/08/2021TGSEvent_X25/08/2021W04Event_Z25/08/2021W09Event_AB25/08/2021W15Event_AF25/08/2021W23Event_AI25/08/2021WGSEvent_AJ
3Person_02person_02_company1003/08/2021MGSEvent_A26/08/2021R08Event_F26/08/2021R13Event_G26/08/2021RGSEvent_M24/08/2021T08Event_Q24/08/2021T18Event_U24/08/2021T24Event_W24/08/2021TGSEvent_X25/08/2021W10Event_AC25/08/2021W21Event_AH
4Person_03person_03_company1503/08/2021MGSEvent_A26/08/2021R02Event_C26/08/2021R06Event_E26/08/2021R17Event_I26/08/2021R20Event_K26/08/2021RGSEvent_M24/08/2021T01Event_N24/08/2021T11Event_S24/08/2021T13Event_T24/08/2021T24Event_W24/08/2021TGSEvent_X25/08/2021W04Event_Z25/08/2021W06Event_AA25/08/2021W19Event_AG25/08/2021WGSEvent_AJ
5Person_04person_04_company1103/08/2021MGSEvent_A26/08/2021R01Event_B26/08/2021R08Event_F26/08/2021R19Event_J26/08/2021RGSEvent_M24/08/2021T02Event_O24/08/2021T08Event_Q25/08/2021W02Event_Y25/08/2021W12Event_AD25/08/2021W14Event_AE25/08/2021WGSEvent_AJ
6
Sheet3
Cell Formulas
RangeFormula
H2:I5H2=UNIQUE(FILTER(A2:B100,A2:A100<>""))
J2:J5J2=SUMIFS(D:D,A:A,INDEX(H2#,,1))
K2:BF2,K5:AQ5,K4:BC4,K3:AN3K2=LET(Fltr,FILTER($C$2:$F$100,$A$2:$A$100=H2),Rws,ROWS(Fltr)*3,Ary,INDEX(Fltr,SEQUENCE(Rws),{1,3,4}),INDEX(Ary,INT(SEQUENCE(,Rws,,1/3)),MOD(SEQUENCE(,Rws,3),3)+1))
Dynamic array formulas.
Click to expand...
Hi Fluff, I have a question about the =LET formula you have here. I have to sort this by date, right now its sorted by session number. I have made an alteration to it though, I had to include the session hour in the output.
My updated formula is:
=LET(Fltr,FILTER(Table378[[Date]:[SesName]],Table378[Attendee_Name]=B2),Rws,ROWS(Fltr)*4,Ary,INDEX(Fltr,SEQUENCE(Rws),{1,2,3,4}),INDEX(Ary,INT(SEQUENCE(,Rws,,1/4)),MOD(SEQUENCE(,Rws,4),4)+1))

This seems to include the hour column and display the other data successfully, but I haven't figured out how to sort the out per user by date attended.
Any suggestions?
 
Upvote 0
AWM21 said:
Hi Fluff, I have a question about the =LET formula you have here. I have to sort this by date, right now its sorted by session number. I have made an alteration to it though, I had to include the session hour in the output.
My updated formula is:
=LET(Fltr,FILTER(Table378[[Date]:[SesName]],Table378[Attendee_Name]=B2),Rws,ROWS(Fltr)*4,Ary,INDEX(Fltr,SEQUENCE(Rws),{1,2,3,4}),INDEX(Ary,INT(SEQUENCE(,Rws,,1/4)),MOD(SEQUENCE(,Rws,4),4)+1))

This seems to include the hour column and display the other data successfully, but I haven't figured out how to sort the out per user by date attended.
Any suggestions?
Click to expand...
@Fluff
Nevermind! lol, I just had to sort the source table... Sorry and thanks again!!
 
Upvote 0
Glad you sorted it, but as you are now returning all 4 columns you can use
Excel Formula: 
=LET(Fltr,SORT(FILTER(Table378[[Date]:[SesName]],Table378[Attendee_Name]=H2)),Rws,ROWS(Fltr)*4,INDEX(Fltr,INT(SEQUENCE(,Rws,,1/4)),MOD(SEQUENCE(,Rws,4),4)+1))
which will also sort by date.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Copy non-empty cells from the same column from multiple sheets to a sheet "Breeders"
Replies
6
Views
72
mumps
mumps
C
  • Question Question
Sum Horizontal and Vertical
Replies
2
Views
203
ChetanPuri
C
ExcelNewbie2020
  • Solved
Convert horizontal data to vertical with running balance
Replies
4
Views
206
alansidman
alansidman
H
  • Solved
Copy or Transpose multiple columns into a single column.
Replies
2
Views
79
harzer
H
Rob_010101
  • Question Question
Weekly meeting tracker
Replies
6
Views
373
JerrExcel
JerrExcel

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top