Hi there!
Feel sorry if topic is discussed already, was not able to find similar discussion.
I have a part numbers’ compatibility table in Calc, looks like this:
Col. A - Col. B
Item1 - Part1
Item2 - Part1
Item1 - Part2
Item1 - Part3
Item2 - Part3
… - …
There are many compatible Part cells for each Item, and each Part can match many Items. What I need to achieve is to get all compatible parts for each Item in one row:
Col. A - Col. B
Item1 - Part1, Part2, Part4, Part 10…
Item2 - Part1, Part3, Part4, Part5…
Item3 - Part2, Part7 …
and so on…
By now, I see 2 ways of doing it:
1). Use Autofilter to go through all Items, Copy filtered Parts and Paste Special with Transpose option to get Parts in one row, then Concatenate them. Bet there are about 40k rows in document, 1.5k unique items - to much routine…
2). Sort unique Items withot dublicate and then for each items use Match to find first occurrence in Parts array and to get this cell address, then in next column use Match again, but for array only after first match, editing this array with Indirect and then calculate cell’s address where second match is found, then extend formulae to the next column and match third occurrence, considering previous array address change, calculate cell address for new match and so on… Pretty complicated and unclear formulas - easy to make mistake.
I am pretty sure that there should be some easier way of achieving this goal. Any ideas to guide me the right way?