SOLVED

# Extracting common data into columns

Copper 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?

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

# Re: Extracting common data into columns

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.

# Re: Extracting common data into columns

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

# Re: Extracting common data into columns

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.

# Re: Extracting common data into columns

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

# Re: Extracting common data into columns

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.