Excel Multiple PeopleID, linked to multiple Metadata

user_15290076 0
Viewed 38

I have a issue, where i have a record of 1,000 People ID duplicated for each metadata.

Is it possible to use excel to change Table A into table B (below) using a formular?

enter image description here

excel-formula excel
1 Answers

You may try, assuming you are using MS365, then,

FORMULA_SOLUTION

• Formula used in cell E3

=LET(u,UNIQUE(A3:A11),
w,BYROW(u,LAMBDA(a,TEXTJOIN(" | ",,FILTER(B3:B11,A3:A11=a)))),
CHOOSE({1,2},u,w))

Or, using VSTACK() & HSTACK() exclusively works with MS365 Office Insiders Beta Version.

• Formula used in cell E10

=LET(u,UNIQUE(A3:A11),
w,BYROW(u,LAMBDA(a,TEXTJOIN(" | ",,FILTER(B3:B11,A3:A11=a)))),
VSTACK(A2:B2,HSTACK(u,w)))

Or,

• Formula used in cell E18

=ListMetadata(A2:B11)

The above formula is a custom & reusable formula created using LAMBDA() with a friendly name and used in name manager,

=LAMBDA(array,
LET(one,DROP(array,1,-1),
two,DROP(array,1,1),
header,TAKE(array,1),
u,UNIQUE(one),
w,BYROW(u,LAMBDA(a,TEXTJOIN(" | ",,FILTER(two,one=a)))),VSTACK(header,HSTACK(u,w))))

Note: Functions TAKE(), DROP(), VSTACK() and HSTACK() are not in regular channels of MS365 exclusively available to MS365 Office Insiders Beta Version Only.

user_81625200
Related