SOLVED

Extracting common data into columns

Copper Contributor

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_0-1713780680699.png

 

4 Replies
best response confirmed by Mazzarougle (Copper Contributor)
Solution

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

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

 
 
Thank you so much for everyone's responses, this one worked perfectly!!
1 best response

Accepted Solutions
best response confirmed by Mazzarougle (Copper Contributor)
Solution

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

View solution in original post