Question on Index Match

Options

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.

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @ScionoftheNight & @KDM

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

    More info.


    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!