Calc: Combine Data from Two Sheets

truematic · October 10, 2020, 11:08pm

I don’t know where to start or what this functionality is called and will try to describe it accurately with an example.
I have one sheet with columns containing data called “Models” and “Pounds” and another sheet that has only “Pounds” column with data but “Models” column is blank.

image description

How can I use some function to fill the second sheet column “Models” with a list of models that are related to the Pounds in an ordered, comma separated list like this:
image description

From my limited experience, it seems like I could use vlookup if there was only one value, but there are multiple matching Models. There are many entries, so doing this manually is not practically feasible. Thanks in advance for any help.

anon73440385 · October 11, 2020, 2:51pm

Hello,

may be the following sample sheets helps to solve your problem.

DataReorg.ods

The formula to be used is an array formula based on regular expressions and reads like:

{=TEXTJOIN(", ";1;IF(NOT(ISNA(REGEX(B$3:B$7;"\b" & C3 & "\b")));A$3:A$7;""))}
(assuming everything is - per the sample - starting with data in range A3:B7)

Please note:

If your resulting table should be on another sheet, you need to adapt A$3:A$7 and B$3:B$7 to $Sheet1.A$3:A$7 and $Sheet1.B$3:B$7(assuming the original data are in Sheet1).

Ref.:
For more information about functions used in the sample file, see:

Hope that helps.

truematic · October 11, 2020, 9:06pm

Opaque, thank you for providing this specific answer and help. I really appreciate it, although I have a lot to learn about Regex and these functions.

In the sample sheet, the results in Models column cells has every model listed: DG1, DG2, DG3, DG4, DG5. This is not the desired result. Should this array formula be returning the results I specified in my original post?

anon73440385 · October 11, 2020, 10:10pm

Yes - and as I wrote: You need to enter the formula using CTRL+SHIFT+ENTER. This means (all described in the help link provided):

Enter: =TEXTJOIN(", ";1;IF(NOT(ISNA(REGEX(B$3:B$7;"\b" & C3 & "\b")));A$3:A$7;"")) and then
Press CTRL+SHIFT+ENTER instead of ENTER as you would do with standard functions.

If doing so the formula turns to have the curly brackets. If you don’t do so, you get what you get. My sample file doesn’t show DG1, DG2, DG3, DG4, DG5, so you must have edited the the formula, without finalizing with CTRL+SHIFT+ENTER.

Sample file shows: ![image description](upload://kwNvciTHGN7aH3PIyMaZoEa6dkD.png)

truematic · October 16, 2020, 4:03am

Hello Opaque, I found out that this behavior exists when opening the file in LO 5.3. I updated to 6.4 and it is working as shown. Thank you for your patient instruction.

truematic · October 16, 2020, 7:13am

I don’t see a way to DM/PM you, is it possible?

anon73440385 · October 16, 2020, 9:48am

DM/PM you, is it possible

What does that mean? If this should mean, whether there is a way to contact me directly: No, this site doesn’t support any kind of private messaging.

truematic · October 16, 2020, 4:19pm

I am trying to apply your array formula on two large sets of data. My computer is well equipped, but LO hangs when I copy and paste the formula to a large number of cells, likely because it is having to poll through so much data. How can I solve this in a non-manual way, like programmatically apply the formula in batches or sequentially?

Yes, I meant direct/private message. I appreciate your help, value your time, and am open to meet with you for professional advice.

anon73440385 · October 16, 2020, 4:35pm

That’s to be expected that there is a drawback with this non-macro based solution (and I should have mentioned that in my answer) and this drawback is: performance - I don’t see any other solution than to write a special macro for that case - but in fact per my opinion such problems should

– either-- put into into a databas
–or-- differently organized in calc sheets (1 line per each combination of Model/Pounds and using Filters)

truematic · October 18, 2020, 5:20am

Thanks for your explanation. Can you help me with 1) the macro or 2) with putting this data into a database that is simple to use? Please let me know how I should contact you.

Lupp · October 11, 2020, 7:51pm

This question was alao posted at Calc: Combine Data from Two Sheets (View topic) • Apache OpenOffice Community Forum.
Regard the answer by “Villeroy” there.

Also:
Having the two basic tables mentionmed by “Villeroy” per “Model” and “Pounds” (meant should be “Weight” or “Mass”) which both may contain additional information concerning the items, it is simple to have a sequence of pairs defining the many-to-many relation. Based on such a representation, it is simple to create the compound representations if actually needed. The automation of the reverse proceeding is blocked by many problems -or at least difficult and inefficient.

truematic · October 11, 2020, 9:09pm

Lupp, yes, I asked forgiveness for cross-posting there and I will do it here as well: please forgive the cross post.

Do you also advise to use Base instead of Calc? What do you mean by the following: “The automation of the reverse proceeding is blocked by many problems -or at least difficult and inefficient.”

Lupp · October 12, 2020, 1:01pm

You needn’t apologize for the cross-posting. You only should do it including links to each other post.

Having a many-to-many relation represented one-row-per-related-pair, you can easily sort it either by the first contributor or by the second, and then get the respective compound representation sometimes preferred for a print/overview/report with the help of TEXTJOIN() e.g.
There are no standard tools for the reverse task.
Yes. Database tasks are better done using database software.
However, I personally don’t like databases exactly for the reasons making them so indispensable, and I’m therefore not the “expert” to help you with a possible migration. I’m glad to no longer need databases.
I’m no sure if Villeroy likes DB, but I’m sure he’s competent.

Lupp · October 12, 2020, 1:13pm

You see, the sums and averages and counts… calculated for reports are accumulated (consolidated) data extracted from one-to-many or from many-to-many relationships. Databases are made for efficiently creating selections and reports concerning a wide range of thinkable questions.
Even disregarding some additional shortcomings (safety/security: rights management, backups…) of spreadsheet documents insofar, their software only covers a small subset. Trying to make them fitter for one of the additional purposes may additional aggravate their usability for others.