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.

Andrée Starå

Hi @ScionoftheNight & @KDM

You can use the CHAR(10) as the delimiter, acting as a line break.

More info.

https://help.smartsheet.com/function/char

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

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.

Andrée Starå

Hi @ScionoftheNight & @KDM

You can use the CHAR(10) as the delimiter, acting as a line break.

More info.

https://help.smartsheet.com/function/char

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kelly Moore

Hey @Andrée Starå

I had tried CHAR(10), CHAR(13) , CHAR(10) and CHAR(13) last night and it never visually appeared the line break worked. When I just read the link you provided - at the very, very bottom, there is a note that says the column must be text-wrapped. <head slap> PEBCAK error. 😉

Thanks for the info because I was at a loss of why the CHAR(10) wasn't working.

cheers!

Andrée Starå

Hey @KDM

Haha!

Easy to miss!

You're more than welcome!

Have a fantastic weekend!

https://media.giphy.com/media/XeLcgh8gT8o0F5SQ8i/giphy.gif

ScionoftheNight

Thank you both!

Andrée Starå

@ScionoftheNight

You're more than welcome!