Ask Your Question

Many-to-many references in Calc

asked 2018-01-18 17:00:49 +0100

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?

edit retag flag offensive close merge delete


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

Jim K gravatar imageJim K ( 2018-01-18 22:20:42 +0100 )edit

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

EasyTrieve gravatar imageEasyTrieve ( 2018-01-18 22:27:15 +0100 )edit

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.

EasyTrieve gravatar imageEasyTrieve ( 2018-01-18 22:28:39 +0100 )edit

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.

SM_Riga gravatar imageSM_Riga ( 2018-01-19 08:08:39 +0100 )edit

@SM_Riga, this might be way over your head right now, but it might provide insight I suspect you'll tinker with databases more later. But don't start with hard problems, get the basics down first. Good luck.

EasyTrieve gravatar imageEasyTrieve ( 2018-01-21 06:59:19 +0100 )edit

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 ;)

SM_Riga gravatar imageSM_Riga ( 2018-01-29 17:19:47 +0100 )edit

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

EasyTrieve gravatar imageEasyTrieve ( 2018-02-14 04:14:57 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-01-18 23:18:41 +0100

Jim K gravatar image

updated 2018-01-18 23:36:47 +0100

  1. Copy all distinct values from column A to column C with Data -> More -> Standard Filter, as described at
  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.

Part1, Part2, Part3 in cell D1

edit flag offensive delete link more


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.

SM_Riga gravatar imageSM_Riga ( 2018-01-19 07:34:35 +0100 )edit

answered 2018-01-18 22:28:34 +0100

JohnSUN gravatar image

What about formula array like as

{=TEXTJOIN(", ";1;IF(A2=A:A;B:B;""))}
edit flag offensive delete link more


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.

SM_Riga gravatar imageSM_Riga ( 2018-01-19 07:43:24 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2018-01-19 09:58:44 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-18 17:00:49 +0100

Seen: 71 times

Last updated: Jan 18 '18