# Many-to-many references in Calc

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 close merge delete

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

( 2018-01-18 22:20:42 +0100 )edit
1

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

( 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.

( 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.

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

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

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

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

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

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

Sort by » oldest newest most voted
1. Copy all distinct values from column A to column C with Data -> More -> Standard Filter, as described at https://ask.libreoffice.org/en/questi....
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.

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.

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

What about formula array like as

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

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.

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

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

( 2018-01-19 09:58:44 +0100 )edit

## Stats

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

Seen: 71 times

Last updated: Jan 18 '18