Forum Discussion
Solved
Combine Data
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 7321...
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...
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...
Hello, you helped me last year with a formula and I’m hoping you can assist again.
You gave me the formula to take same id info and move to 1 row as seen in the first image. Now I’m needing to do the opposite, im needing to go from image 2 and organize like the 1st one