Forum Discussion

Mazzarougle's avatar
Mazzarougle
Copper Contributor
Apr 22, 2024
Solved

Extracting common data into columns

Hi,    I wonder if anyone can help explain how I can create an alternative view for a big data set I have.   For the sake of this question, I have created an illustration of what I am trying to a...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Apr 22, 2024

    Mazzarougle 

    Let's say the first sheet is named Data Sheet.

    In A1 on the second sheet, enter the formula

     

    =TRANSPOSE(SORT(UNIQUE('Data Sheet'!A2:A9)))

     

    In B1:

     

    =FILTER('Data Sheet'!$B$2:$B$9,'Data Sheet'!$A$2:$A$9=A1)

     

    Fill to the right.

    See the attached demo workbook.

djclements's avatar
djclements
Bronze Contributor
Apr 22, 2024

Mazzarougle A dynamic array variant for MS365 could be:

 

=LET(
    data, Sheet1!A2:B1000,
    category, INDEX(data,, 1),
    catId, TOROW(SORT(UNIQUE(category)), 1),
    recordCount, COUNTIF(category, catId),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    rows, SEQUENCE(maxCount),
    results, WRAPCOLS(DROP(SORT(VSTACK(FILTER(data, category<>""), EXPAND(TOCOL(IFS(newRows>=rows, catId), 2),, 2, ""))),, 1), maxCount),
    VSTACK(catId, results)
)

 

Adjust the data range reference accordingly. Please see the attached workbook, if necessary...

Resources