split and organize values from array to new array

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.

I'm part way there... but am unable to go further because your ssample has only two rows. So I added one.

The COMMON bits are done (yellow cell)

I have done the specific bit for the comparison of rows 1 &2 of the array... and need to know what the ENTIRE set of results for the specific bit should look like.

see file for work-in-progress.

DJ... that's NOT the expected answer (the explanation is rather confused and the formula in B16 is irrelevant.). the expected answer is a spill array that looks like B10:B11

If Fluffs doesn't work out, I >>nearly<< have an slternative, bilut have to leave now for a "long lunch" with friends!!

In C17

Formula:

Please Login or Register  to view this content.

Better formula.
In C17

Formula:

Please Login or Register  to view this content.

Finally... getting back to this, and just for completeness, my take on it. Try:

Formula:

Please Login or Register  to view this content.

Ditto... You're welcome.