Concatenate, Join, and Index

R Brown

I am looking for a way to pull data from Sheet A into a helper sheet, concatenate and join the data, and then push the result into Sheet B. Sheet A might have 1 parent row and up to 10 children or 1 parent only or any combination thereof. I am having trouble hitting on the right formula without it being a manual process. Any help would be much appreciated.

SoS | Dan Palenchar

Hi,

some screenshots of your sheet would help. It sounds like you want 3 sheets here? Sheet A, helper sheet, and Sheet B? I wonder why the helper is needed... How many columns are you wanting to concatenate and join? I think just JOIN would accomplish this...

R Brown

This is a generic example of Sheet A. I can only pull data from that sheet as I will not be allowed to add any columns, etc. The helper sheet will be used in place of any helper columns on this sheet.

The end product should look something like this:

I thought about using an Index/Match expression to pull the data, but I'm having trouble coming up with an expression pull the data into one cell.

Genevieve P.

Hi @R Brown

It sounds like what you're looking for would be a JOIN/COLLECT formula. This would join together into one cell all the data that meets a certain criteria. The issue here is that cross-sheet formulas aren't able to identify a Parent in the same way that an in-sheet formula can.

Normally I would suggest adding a helper column into the source sheet (that you can hide) which identifies the Parent content for each individual row. Then when that's a part of each row, you can use it as criteria to pull over and Join together the Child content from a different column in one cell in your other sheet.

Is there a possibility that the sheet owner would be able to build this helper column for you? Or is there some other identifier on each row that we can use as the criteria to tell which Solution goes with which Customer?

Thanks!

Genevieve