Many-to-many references in Calc

[SM_Riga]

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?

[jimk]

Perhaps the solution involves TEXTJOIN as described in many posts by @Lupp lately.

[EasyTrieve]

did you mean, "Item1 - Part1, Part2, Part3, Part 10… “? (you wrote, …1, Part2, Part4…”)

[EasyTrieve]

You can use a hammer to paint a wall, but it’s easier with a brush. You can use a spread sheet to process tables, but it’s easier with a database.

[SM_Riga]

Thanks for comments, EasyTrieve. I do fully agree on your ‘Chose the right tool’ metaphor. Unfortunately, I am familiar only with the very basics of Base and SQL, so I would prefer Calc, where my knowledge is a bit better. JohnSUN’s and Jim’s solutions are elegant and easy to implement in Spreadsheet. Nevertheless, I would be very grateful, if you can past here Base solution for this simple database with one table with two columns - just for educational purposes.

[EasyTrieve]

@SM_Riga, this might be way over your head right now, but it might provide insight How to use GROUP BY to concatenate strings in SQL Server? - Stack Overflow I suspect you’ll tinker with databases more later. But don’t start with hard problems, get the basics down first. Good luck.

[SM_Riga]

Dear EasyTrieve. Sorry for such late response, was busy last days. You are right, the provided topic is just over my SQL knowledge at this moment, but the link is bookmarked for my to-learn list

[EasyTrieve]

@SM_Riga, We’re all learning, and struggling. Take care.

[jimk]

1. Copy all distinct values from column A to column C with Data → More → Standard Filter, as described at Find distinct values in a column formula for calc - #14 by librebel.

2. In cell D1, enter the following Array formula (similar to @JohnSUN’s answer) by pressing Control+Shift+Enter.

   =TEXTJOIN(", ";1;IF(A$1:A$5=C1;B$1:B$5;""))

3. Hold the Control key and drag the formula down column D as far as needed.

[SM_Riga]

Many thanks, Jim and JohnSUN. Exactly the way a formula should be - elegant. Was not familiar with array formulas till today, so my gratitude to both of you for not just writing the right formula, but much more for showing new methods to learn and use.

[JohnSUN]

What about formula array like as

{=TEXTJOIN(", ";1;IF(A2=A:A;B:B;""))}

[SM_Riga]

Dear JonhSUN. As I’ve mentioned above, many thanks to you for pointing the way to search for solution. I’ve voted for both answers as they are correct. Jim’s reply is just more informative for others searching on similar topic.

[JohnSUN]

Yes, you’re right - for this reason I voted for Jim’s answer too.