Dear Friends:
The scenario is as follow. In G3# I have a dynamic array, 2 cols, with an ID, and a value1. in J3#, another dynamic array, 2 cols, with an ID, and a value2.
I need to concat each value 1 with all corresponding values2 depending on the ID, and return an array in M3# listing all the results.
concat dynamic arrays via ids.jpg
Ideas anyone? Thank you so much.
Convert the arrays to tables. Bring them both into Power Pivot and join them on the common field. Create a measure and Pivot the Data using ConcatenateX function.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
@Alan Cannot. In the background, all is within a LET function dealing with arrays of values. Thanks.
The whole table (including the headers) in a single cell formula in one go:
Please empty all expected results and try in M2:Formula:
=REDUCE({"id","concatenation"},G3:G5,LAMBDA(i,x,VSTACK(i,IFNA(HSTACK(x,OFFSET(x,,1)&" "&FILTER(K3:K8,J3:J8=x)),x))))
Perfect as usual. Thank you Hans.
Thanks for the feedback and rep. Glad to have helped again..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks