AWM21
New Member
- Joined
- Aug 2, 2021
- Messages
- 13
- Office Version
- 365
- Platform
- 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:
DESIRED END PRODUCT:
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 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Attendee_Name | Organisation | Date | CredHour | SesNum | SesName | ||
2 | Person_01 | person_01_company | 08/03/2021 | 1 | MGS | Event_A | ||
3 | Person_01 | person_01_company | 08/26/2021 | 1 | R03 | Event_D | ||
4 | Person_01 | person_01_company | 08/26/2021 | 1 | R08 | Event_F | ||
5 | Person_01 | person_01_company | 08/26/2021 | 1 | R15 | Event_H | ||
6 | Person_01 | person_01_company | 08/26/2021 | 1 | R23 | Event_L | ||
7 | Person_01 | person_01_company | 08/26/2021 | 1 | RGS | Event_M | ||
8 | Person_01 | person_01_company | 08/24/2021 | 1 | T04 | Event_P | ||
9 | Person_01 | person_01_company | 08/24/2021 | 1 | T10 | Event_R | ||
10 | Person_01 | person_01_company | 08/24/2021 | 1 | T13 | Event_T | ||
11 | Person_01 | person_01_company | 08/24/2021 | 1 | T23 | Event_V | ||
12 | Person_01 | person_01_company | 08/24/2021 | 1 | TGS | Event_X | ||
13 | Person_01 | person_01_company | 08/25/2021 | 1 | W04 | Event_Z | ||
14 | Person_01 | person_01_company | 08/25/2021 | 1 | W09 | Event_AB | ||
15 | Person_01 | person_01_company | 08/25/2021 | 1 | W15 | Event_AF | ||
16 | Person_01 | person_01_company | 08/25/2021 | 1 | W23 | Event_AI | ||
17 | Person_01 | person_01_company | 08/25/2021 | 1 | WGS | Event_AJ | ||
18 | Person_02 | person_02_company | 08/03/2021 | 1 | MGS | Event_A | ||
19 | Person_02 | person_02_company | 08/26/2021 | 1 | R08 | Event_F | ||
20 | Person_02 | person_02_company | 08/26/2021 | 1 | R13 | Event_G | ||
21 | Person_02 | person_02_company | 08/26/2021 | 1 | RGS | Event_M | ||
22 | Person_02 | person_02_company | 08/24/2021 | 1 | T08 | Event_Q | ||
23 | Person_02 | person_02_company | 08/24/2021 | 1 | T18 | Event_U | ||
24 | Person_02 | person_02_company | 08/24/2021 | 1 | T24 | Event_W | ||
25 | Person_02 | person_02_company | 08/24/2021 | 1 | TGS | Event_X | ||
26 | Person_02 | person_02_company | 08/25/2021 | 1 | W10 | Event_AC | ||
27 | Person_02 | person_02_company | 08/25/2021 | 1 | W21 | Event_AH | ||
28 | Person_03 | person_03_company | 08/03/2021 | 1 | MGS | Event_A | ||
29 | Person_03 | person_03_company | 08/26/2021 | 1 | R02 | Event_C | ||
30 | Person_03 | person_03_company | 08/26/2021 | 1 | R06 | Event_E | ||
31 | Person_03 | person_03_company | 08/26/2021 | 1 | R17 | Event_I | ||
32 | Person_03 | person_03_company | 08/26/2021 | 1 | R20 | Event_K | ||
33 | Person_03 | person_03_company | 08/26/2021 | 1 | RGS | Event_M | ||
34 | Person_03 | person_03_company | 08/24/2021 | 1 | T01 | Event_N | ||
35 | Person_03 | person_03_company | 08/24/2021 | 1 | T11 | Event_S | ||
36 | Person_03 | person_03_company | 08/24/2021 | 1 | T13 | Event_T | ||
37 | Person_03 | person_03_company | 08/24/2021 | 1 | T24 | Event_W | ||
38 | Person_03 | person_03_company | 08/24/2021 | 1 | TGS | Event_X | ||
39 | Person_03 | person_03_company | 08/25/2021 | 1 | W04 | Event_Z | ||
40 | Person_03 | person_03_company | 08/25/2021 | 1 | W06 | Event_AA | ||
41 | Person_03 | person_03_company | 08/25/2021 | 1 | W19 | Event_AG | ||
42 | Person_03 | person_03_company | 08/25/2021 | 1 | WGS | Event_AJ | ||
43 | Person_04 | person_04_company | 08/03/2021 | 1 | MGS | Event_A | ||
44 | Person_04 | person_04_company | 08/26/2021 | 1 | R01 | Event_B | ||
45 | Person_04 | person_04_company | 08/26/2021 | 1 | R08 | Event_F | ||
46 | Person_04 | person_04_company | 08/26/2021 | 1 | R19 | Event_J | ||
47 | Person_04 | person_04_company | 08/26/2021 | 1 | RGS | Event_M | ||
48 | Person_04 | person_04_company | 08/24/2021 | 1 | T02 | Event_O | ||
49 | Person_04 | person_04_company | 08/24/2021 | 1 | T08 | Event_Q | ||
50 | Person_04 | person_04_company | 08/25/2021 | 1 | W02 | Event_Y | ||
51 | Person_04 | person_04_company | 08/25/2021 | 1 | W12 | Event_AD | ||
52 | Person_04 | person_04_company | 08/25/2021 | 1 | W14 | Event_AE | ||
53 | Person_04 | person_04_company | 08/25/2021 | 1 | WGS | Event_AJ | ||
faux |
DESIRED END PRODUCT:
Help Master.txt | |||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | |||
1 | Attendee_Name | Organisation | totalCreds | Date0 | SesNum0 | SesName0 | Date1 | SesNum1 | SesName1 | Date2 | SesNum2 | SesName2 | Date3 | SesNum3 | SesName3 | Date4 | SesNum4 | SesName4 | Date5 | SesNum5 | SesName5 | Date6 | SesNum6 | SesName6 | Date7 | SesNum7 | SesName7 | Date8 | SesNum8 | SesName8 | Date9 | SesNum9 | SesName9 | Date10 | SesNum10 | SesName10 | Date11 | SesNum11 | SesName11 | Date12 | SesNum12 | SesName12 | Date13 | SesNum13 | SesName13 | Date14 | SesNum14 | SesName14 | Date15 | SesNum15 | SesName15 | ||
2 | Person_01 | person_01_company | 16 | 08/23/2021 | MGS | Event_A | 08/24/2021 | T04 | Event_P | 08/24/2021 | T10 | Event_R | 08/24/2021 | T13 | Event_T | 08/24/2021 | T23 | Event_V | 08/24/2021 | TGS | Event_X | 08/25/2021 | W04 | Event_Z | 08/25/2021 | W09 | Event_AB | 08/25/2021 | W15 | Event_AF | 08/25/2021 | W23 | Event_AI | 08/25/2021 | WGS | Event_AJ | 08/26/2021 | R03 | Event_D | 08/26/2021 | R08 | Event_F | 08/26/2021 | R15 | Event_H | 08/26/2021 | R23 | Event_L | 08/26/2021 | RGS | Event_M | ||
3 | Person_02 | person_02_company | 10 | 08/23/2021 | MGS | Event_A | 08/24/2021 | T08 | Event_Q | 08/24/2021 | T18 | Event_U | 08/24/2021 | T24 | Event_W | 08/24/2021 | TGS | Event_X | 08/25/2021 | W10 | Event_AC | 08/25/2021 | W21 | Event_AH | 08/26/2021 | R08 | Event_F | 08/26/2021 | R13 | Event_G | 08/26/2021 | RGS | Event_M | ||||||||||||||||||||
4 | Person_03 | person_03_company | 15 | 08/23/2021 | MGS | Event_A | 08/24/2021 | T01 | Event_N | 08/24/2021 | T11 | Event_S | 08/24/2021 | T13 | Event_T | 08/24/2021 | T24 | Event_W | 08/24/2021 | TGS | Event_X | 08/25/2021 | W04 | Event_Z | 08/25/2021 | W06 | Event_AA | 08/25/2021 | W19 | Event_AG | 08/25/2021 | WGS | Event_AJ | 08/26/2021 | R02 | Event_C | 08/26/2021 | R06 | Event_E | 08/26/2021 | R17 | Event_I | 08/26/2021 | R20 | Event_K | 08/26/2021 | RGS | Event_M | |||||
5 | Person_04 | person_04_company | 11 | 08/23/2021 | MGS | Event_A | 08/24/2021 | T02 | Event_O | 08/24/2021 | T08 | Event_Q | 08/25/2021 | W02 | Event_Y | 08/25/2021 | W12 | Event_AD | 08/25/2021 | W14 | Event_AE | 08/25/2021 | WGS | Event_AJ | 08/26/2021 | R01 | Event_B | 08/26/2021 | R08 | Event_F | 08/26/2021 | R19 | Event_J | 08/26/2021 | RGS | Event_M | |||||||||||||||||
faux (2) |