Combine Data

[Tamra_Raleylostinexce]

Looking for a way to take same ID data and place in 1 row

From this...

ID	NAME	Teacher	Class	Total
12345	ESTHER	ARDOIN,R	AVID III	11
12345	ESTHER	SARZYNSKI,	Geometry	12
73214	RACHAEL	SOWELLS,J	Reading I AL	9
18834	CHIEMEZIE	HOLCOMB,H	Health	13
18834	CHIEMEZIE	NESBIT,J	AlgebraII AAC	13
18834	CHIEMEZIE	MCGEE,A	Eng II AAC	13
18834	CHIEMEZIE	LI,M	Chem AAC	13
18834	CHIEMEZIE	LAU,B	Spanish II	13
18834	CHIEMEZIE	MCGEE,T	World HistoryAP	13
18834	CHIEMEZIE	HAASE,P	Soccer 2 YR B	13
26997	RAAKIN	OZMENT,J	US Govt	9
26997	RAAKIN	DAVIS,A	Art I	9
26997	RAAKIN	HERTEL,N	ENG IV D SM2	9
57111	SHAHD	PULIDO,T	World Geo	9
57111	SHAHD	RIVERA,S	AquaSci	9
57111	SHAHD	PYLES,A	Eng IV	9
57111	SHAHD	THOMAS,A	Chem I	9
57111	SHAHD	ZHU,W	Chinese II	10
57111	SHAHD	OZMENT,J	US Govt	11
57111	SHAHD	LACOUR,J	AlgReason	11

To this...

ID

NAME

Teacher

Class

Total

Teacher

Class

Total

Teacher

Class

Total

Teacher

Class

Total

Teacher

Class

Total

Teacher

Class

Total

Teacher

Class

Total

12345

ESTHER

ARDOIN,R

AVID III

11

SARZYNSKI,

Geometry

12

73214

RACHAEL

SOWELLS,J

Reading I AL

9

18834

CHIEMEZIE

HOLCOMB,H

Health

13

NESBIT

AlgebraII AAC

13

MCGEE,A

Eng II AAC

13

LI,M

Chem AAC

13

LAU,B

Spanish II

13

MCGEE,T

World HistoryAP

13

HAASE,P

Soccer 2 YR B

13

26997

RAAKIN

OZMENT,J

US Govt

9

DAVIS,A

Art I

9

HERTEL,N

ENG IV D SM2

9

57111

SHAHD

PULIDO,T

World Geo

9

RIVERA,S

AquaSci

9

PYLES,A

Eng IV

9

THOMAS,A

Chem I

9

ZHU,W

Chinese II

10

OZMENT,J

US Govt

11

LACOUR,J

AlgReason

11

The spreadsheet has 778 rows and I would love to find a quicker way to move like data to 1 row so I can do a mail merge

[#2 · Accepted solution]

@Tamra_Raleylostinexce Based on the sample data provided, and if you're using Excel for MS365, the following dynamic array formula should do the trick:

=LET(
    head, A1:E1, data, A2:E1000,
    id, TAKE(data,, 1),
    fData, FILTER(data, id<>""),
    students, SORT(UNIQUE(TAKE(fData,, 2))),
    keyId, TAKE(students,, 1),
    recordCount, COUNTIF(id, keyId),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    pvtHead, HSTACK(TAKE(head,, 2), TOROW(IF(SEQUENCE(maxCount), DROP(head,, 2)))),
    pvtData, HSTACK(students, WRAPROWS(TOCOL(DROP(SORT(VSTACK(fData, EXPAND(TOCOL(IFS(newRows>=SEQUENCE(, maxCount), keyId), 2),, 5, ""))),, 2)), maxCount*3)),
    VSTACK(pvtHead, pvtData)
)

Please see the attached sample workbook...

[#3]

@djclements 

This worked perfect until I realized it last 4 columns didn't show up.

This is an example of what showed up for the student below:

18834

CHIEMEZIE

HOLCOMB,H

Health

13

NESBIT,J

AlgebraII AAC

13

MCGEE,A

Eng II AAC

13

LI,M

Chem AAC

13

LAU,B

Spanish II

13

MCGEE,T

World HistoryAP

Any ideas on what I did wrong?

[#4]

Oh goodness, never mind..it was my error!!
You are amazing and saved me so much time.
THANK YOU

[#5]

@Tamra_Raleylostinexce I was just about to say that I wouldn't know for sure what went wrong without taking a look at your workbook. Glad to hear you figured it out... Cheers! :)

[#6]

Ok, it's me again. I just realized I am needing to add another column. Column F will be titled "HOURS"
I have tried to manipulate your formula but seem to be a little off. Can you please help

[#7]

@Tamra_Raleylostinexce In the second to last line (pvtData definition), where the EXPAND function is used, change the number of columns from 5 to 6. Also on the same line, change the wrap_count for the WRAPROWS function (the last argument at the far right) from maxCount*3 to maxCount*4:

=LET(
    head, A1:F1, data, A2:F1000,
    ...
    pvtData, HSTACK(students, WRAPROWS(TOCOL(DROP(SORT(VSTACK(fData, EXPAND(TOCOL(IFS(newRows>=SEQUENCE(, maxCount), keyId), 2),, 6, ""))),, 2)), maxCount*4)),
    ...
)

We could also add a few more variables to make the number of columns a little more dynamic...

=LET(
    head, A1:F1, data, A2:F1000,
    cols, COLUMNS(data),
    keyCols, 2,
    pvtCols, cols-keyCols,
    id, TAKE(data,, 1),
    fData, FILTER(data, id<>""),
    students, SORT(UNIQUE(TAKE(fData,, keyCols))),
    keyId, TAKE(students,, 1),
    recordCount, COUNTIF(id, keyId),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    pvtHead, HSTACK(TAKE(head,, keyCols), TOROW(IF(SEQUENCE(maxCount), DROP(head,, keyCols)))),
    pvtData, HSTACK(students, WRAPROWS(TOCOL(DROP(SORT(VSTACK(fData, EXPAND(TOCOL(IFS(newRows>=SEQUENCE(, maxCount), keyId), 2),, cols, ""))),, keyCols)), maxCount*pvtCols)),
    VSTACK(pvtHead, pvtData)
)

keyCols is referring to the first 2 columns containing the student ID and Name, and pvtCols is the remaining number of columns to be transformed and output to a single row for each student. Adjust the head and data ranges to meet your needs.

Basically, a bunch of blank rows are being inserted into the data range for each student, based on the student with the most records. For example, student 18834 and 57111 both have the most records (7 each), so no blank rows are inserted for them. However, student 73214 only has 1 record, so 6 blank rows are added. Likewise, student 12345 has 2 records (5 blank rows added), and student 26997 has 3 records (4 blank rows added). This is to make sure each student contains the same number of records. TOCOL then sends the "padded" data to a single column and WRAPROWS transforms the data into the correct number of rows and columns. The VSTACK and HSTACK functions are used to add the headers and key columns (unique list of student IDs and Names), generating the final output.

If you have any more questions, please let me know.

[#8]

@djclements this is great. Really appreciate you breaking it down for me. Excel is amazing and I’d love to learn more about it someday. 
Thank you again for your help and quick responses.