CODEWITHSUNDEEP
excel
Kit Ng
Kit Ng

Reputation: 993

Excel - changing table layout

I want to have below conversion:

enter image description here

How to achieve? Thanks.

Upvotes: 0

Views: 29

Answers (1)

user4039065
user4039065

Reputation:

The formulas for retrieving multiple matches generally rely to some extent on the row number. In the case of a ListObject table, the row from the .DataBodyRange property must be adjusted byt the row of the .HeaderRowRange property.

In Table3 across the first row in the DataBodyRange,

=IFERROR(INDEX(Table2, AGGREGATE(15, 6, (ROW(Table2)-ROW(Table2[#Headers]))/
                  (Table2[class]=Table3[[#Headers],[a]]), ROW(1:1)), 2), TEXT(,))
=IFERROR(INDEX(Table2, AGGREGATE(15, 6, (ROW(Table2)-ROW(Table2[#Headers]))/
                  (Table2[class]=Table3[[#Headers],[b]]), ROW(1:1)), 2), TEXT(,))
=IFERROR(INDEX(Table2, AGGREGATE(15, 6, (ROW(Table2)-ROW(Table2[#Headers]))/
                  (Table2[class]=Table3[[#Headers],[c]]), ROW(1:1)), 2), TEXT(,))

The formulas should autofill into the remaining ListObject object cells. Be sure to expand the table vertically to catch all potential matches.

    transpose_tables_listobjects

I'm not quite sure why Tom was not listed in your samepl as being from class C so I;ve assumed it was a typo.

Upvotes: 1

Related Questions