Forum Discussion
Mazzarougle
Apr 22, 2024Copper Contributor
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?
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.
- OliverScheurichGold Contributor
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.
- djclementsBronze 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...
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.
- MazzarougleCopper ContributorThank you so much for everyone's responses, this one worked perfectly!!