Dear friends:
I have an issue with Excel dynamic arrays.
I start with a spilling array inherited in B6. I have no control over the size of this array. In my example attached, 2 rows, but it could be more.
Each row represents a list of pairs label/value.
Pairs separators are "/" for rows (label), "|" for columns (value).
The pairs are not sorted. I have no control over how they are supplied to B6. Each row may be sorted differently, and possibly contains not common labels with other rows.
My intention is to split all the pairs available in the initial array and provide as result a 2-rows spilling array as follow:
- in a first row, we "merge" the common pairs (same label, same value), shared by all row of the initial array
- in a second row, we isolate the specifics, and we keep the distinction between the distinct initial rows with a "***" separator.
test.jpg
What do you think about this?
At first sight it looked not complex, but I ended up with a REDUCE within a REDUCE within a REDUCE ang got lost.
Thanks for your input.
Bookmarks