Forum Discussion

Mazzarougle's avatar
Mazzarougle
Copper Contributor
Apr 22, 2024

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

 

There are two columns (Category & Value) in a data set that I need to appear on a separate sheet, but instead the Category needs to be the column title and the values that fall within this category listed below it - is this possible please?

 

 

  • 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

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

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

    • Mazzarougle's avatar
      Mazzarougle
      Copper Contributor
      Thank you so much for everyone's responses, this one worked perfectly!!

Resources