Forum Discussion

Tamra_Raleylostinexce's avatar
Tamra_Raleylostinexce
Copper Contributor
Mar 28, 2024
Solved

Combine Data

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

From this...

IDNAMETeacherClassTotal
12345ESTHERARDOIN,RAVID III11
12345ESTHERSARZYNSKI,Geometry12
73214RACHAELSOWELLS,JReading I AL9
18834CHIEMEZIEHOLCOMB,HHealth13
18834CHIEMEZIENESBIT,JAlgebraII AAC13
18834CHIEMEZIEMCGEE,AEng II AAC13
18834CHIEMEZIELI,MChem AAC13
18834CHIEMEZIELAU,BSpanish II13
18834CHIEMEZIEMCGEE,TWorld HistoryAP13
18834CHIEMEZIEHAASE,PSoccer 2 YR B13
26997RAAKINOZMENT,JUS Govt9
26997RAAKINDAVIS,AArt I9
26997RAAKINHERTEL,NENG IV D SM29
57111SHAHDPULIDO,TWorld Geo9
57111SHAHDRIVERA,SAquaSci9
57111SHAHDPYLES,AEng IV9
57111SHAHDTHOMAS,AChem I9
57111SHAHDZHU,WChinese II10
57111SHAHDOZMENT,JUS Govt11
57111SHAHDLACOUR,JAlgReason11

To this...

IDNAMETeacherClassTotalTeacherClassTotalTeacherClassTotalTeacherClassTotalTeacherClassTotalTeacherClassTotalTeacherClassTotal
12345ESTHERARDOIN,RAVID III11SARZYNSKI,Geometry12               
73214RACHAELSOWELLS,JReading I AL9                  
18834CHIEMEZIEHOLCOMB,HHealth13NESBITAlgebraII AAC13MCGEE,AEng II AAC13LI,MChem AAC13LAU,BSpanish II13MCGEE,TWorld HistoryAP13HAASE,PSoccer 2 YR B13
26997RAAKINOZMENT,JUS Govt9DAVIS,AArt I9HERTEL,NENG IV D SM29            
57111SHAHDPULIDO,TWorld Geo9RIVERA,SAquaSci9PYLES,AEng IV9THOMAS,AChem I9ZHU,WChinese II10OZMENT,JUS Govt11LACOUR,JAlgReason11

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

excel
Formulas and Functions
  • djclements's avatar
    djclements
    Mar 28, 2024

    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...

8 Replies

  • djclements's avatar
    djclements
    Silver Contributor
    Mar 28, 2024

    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's avatar
      Tamra_Raleylostinexce
      Copper Contributor
      Jan 08, 2025

      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 

    • Tamra_Raleylostinexce's avatar
      Tamra_Raleylostinexce
      Copper Contributor
      Mar 28, 2024

      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:

       

      18834CHIEMEZIEHOLCOMB,HHealth13NESBIT,JAlgebraII AAC13MCGEE,AEng II AAC13LI,MChem AAC13LAU,BSpanish II13MCGEE,TWorld HistoryAP

       

      Any ideas on what I did wrong?

       

      • djclements's avatar
        djclements
        Silver Contributor
        Mar 28, 2024

        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! 🙂

Resources