Build an Excel Worksheet that lists information from another worksheet in an opposite format

I have an Excel worksheet (TAB1) that lists items like this.

Class	Type
AA	A
AB	B
AC	A
AD	C

So, there are unique Class values, but the Type values may be associated with more than one Class.

I'm trying to build a second worksheet (TAB2) in the same workbook that lists the Type values and all of the Classes they are associated with.

Type	Classes
A	AA	AC
B	AB
C	AD

I'm able to build the Type column in the TAB2 worksheet by using the UNIQUE function like this.

=SORT(UNIQUE(FILTER('TAB1'!B:B,(TRIM('TAB1'!B:B)<>"Type")*(TRIM('TAB1'!B:B)<>""),"")))

But I haven't figured out a way to populate the "Classes" columns. Is there a function or combination of functions that will list all of the "Class" values of each "Type" across the rows?

It would also be acceptable to list the Type values in row 1 across the columns and have the classes listed below the type values they are associated with.

I would appreciate any help that someone could provide.

Solution

Try using one of the following, both of the formulas works with MS365 version of Excel.

Using MAKEARRAY()

• Formula used in cell A1

=LET(
     _type, 'TAB1'!B2:B5,
     _class, 'TAB1'!A2:A5,
     _uniqueType, UNIQUE(_type),
     _rows, ROWS(_uniqueType),
     _columns, MAX(COUNTIFS(_type,_type)),
     _listClasses, MAKEARRAY(_rows, _columns, LAMBDA(x,y,IFERROR(INDEX(FILTER(_class, INDEX(_uniqueType,x)=_type),y),""))),
     HSTACK(_uniqueType, _listClasses))

Or, Using REDUCE()

• Formula used in cell A1

=LET(
     _type, 'TAB1'!B2:B5,
     _class, 'TAB1'!A2:A5,
     _uniqueType, UNIQUE(_type),
     _listClasses, IFNA(DROP(REDUCE("", _uniqueType, LAMBDA(x,y, VSTACK(x, TOROW(FILTER(_class, _type=y))))),1),""),
     HSTACK(_uniqueType, _listClasses))

Both of the above formulas uses LAMBDA() helper functions to iterate the cells and uses the functions to return one single dynamic array output however, if you want to return the data using fill down method then you could try

• Formula used in cell A1

=SORT(UNIQUE(DROP(TOCOL('TAB1'!B:B,1),1)))

• And formula used in cell B1

=TOROW(FILTER('TAB1'!$A$2:$A$5,'TAB2'!$A1='TAB1'!$B$2:$B$5))

The above formula needs to fill down!

One more alternative way, with the advent of GROUPBY() function in MS365 version for Beta Users, one could use the following, convert the data into a Structured References aka Tables as it automatically resizes to store more data, and as the formula references the Table it automatically gets updated when new data is added, use as below:

=LET(
     α, GROUPBY(Table1[Type],Table1[Class],ARRAYTOTEXT,0,0),
     δ, TAKE(α,,-1),
     HSTACK(TAKE(α,,1),
     IFNA(TEXTSPLIT(TEXTAFTER(", "&δ,", ",SEQUENCE(,MAX(LEN(δ)-LEN(SUBSTITUTE(δ,", ",))))),", "),"")))

File can be downloaded from here: Excel