Question on Index Match

ScionoftheNight

Hi,

I have the following fringe scenario that I need to build out a process for:

On Monday, a user uses a form and submits the following entry:

[Sales Order Column] 1234-5

[Clone] A4

On Friday, that user submits the following entry:

[Sales Order Column] 1234-5

[Clone] B8

On another sheet, there is an Index Match formula that is looking for 1234-5 to out put the Clone column. However, if there are two entries, it is only going to return the first entry (A4) and not the second entry (B8).

TLDR: Same Identifier submitted at 2 different times with 2 different contents in the same sheet. I need the content from both times to be contained within 1 point so that I can properly Index that from a separate sheet.

My plan is to do an Index Match on the first entry to collect the contents of the second entry into one column. Then have the other sheet reference that column that contains both dates. The problem I am facing is how do I get the [Clone] Information from the later date entry into the earlier date entry.

Kelly Moore

Hey

Maybe this is what you're looking for.

I believe your data is set up like this with your Target sheet that needs all the Clone information, and the Source sheet where the data originates. The formula is placed in the Target Sheet Clone column

=JOIN(COLLECT({Source Sheet Clone}, {Source Sheet Sales Order}, [Target Sheet Sales Number]@row), " - ")

I haven't found a direct way to insert a carriage return/line break as the JOIN delimiter but there is a work-around with a Helper column found here, if you need it. If you used the work around, you might also have to add a helper column in your source sheet for appending a designated character on the end of the CLONE value in order to have a consistent character to use in the substitution. You would then end up referencing this new appended Clone column instead of the original source sheet column.