Using INDEX/DISTINCT with OR function spread across multiple ranges

I use the following formula to condense data from source sheet to destination sheet with the "distinct" feature possibly combined with an OR function. Something like

=Index(Distinct(OR({FLOWER UID}), Number@row, ({SHAKE UID}), Number@row))

=Index(Distinct(OR({FLOWER UID}, {SHAKE UID}), Number@row))

Source sheet with two different columns containing ID's I'd like pulled in to the destination sheet

SOURCE SHEET for multi column distinct pull.png

Destination sheet pulling in all 4 distinct numbers from two different columns from source sheet. @ rows are created in destination sheet. One with the FLOWER UID and one with the SHAKE UID

Screen Shot 2022-06-23 at 12.06.00 AM.png

I tried this formula, but I'm probably way off base. Is it even possible?

Screen Shot 2022-06-22 at 11.53.23 PM.png

______________________________________________________________________________

Once DISTINCT UID is inside the destination sheet, I'd like to use a regular INDEX(MATCH) to pull in the corresponding "HARVEST BATCH NAME" from the source sheet. That will require another OR function, to pull it in from one of the other columns in the source sheet. Basically each harvest batch will be pulled in twice. Once with the FLOWER ID and once with the SHAKE ID. Two distinct rows per harvest batch rather than one. I hope that makes sense.

INDEX MATCH with OR to pull in H BATCH ID to output log.png

Thank-you,

Sarah

Find more posts tagged with

Formulas

Accepted answers

All comments

Mike TV

@Sarah_lee123

Hi Sarah. Here's an example of what I think you're trying to do.

For the Harvest Batch Name column, for the first 2 rows you're doing to want this formula:

=IFERROR(INDEX(DISTINCT({Harvest Batch Name}), 1), "")

For the next 2 rows you'd use:

=IFERROR(INDEX(DISTINCT({Harvest Batch Name}), 2), "")

And keep filling out your sheet for as many unique Harvest Batch Names as you think you'll ever need. You just increase that number by 1 each time entering two rows. When it's captured all of the unique Harvest Batch Names it'll just return a blank cell instead of a unique value.

Then for the UID column for each row with Flower as the Output Type you'll need this formula:

=INDEX({Flower UID}, MATCH([Harvest Batch Name]@row, {Harvest Batch Name}, 0))

{Flower UID} should be a cross-sheet reference to the entire Flower UID column on the other sheet. {Harvest Batch Name} will be the same cross-sheet reference to the entire Harvest Batch Name column on the other sheet.

For the UID column on the Shake output types you'll need this formula:

=INDEX({Shake UID}, MATCH([Harvest Batch Name]@row, {Harvest Batch Name}, 0))

You can copy-paste those into the appropriate cells in the UID column for shake and flower until you have them all in there. If you enter quite a bit into the sheet you'll be set for awhile without needing to fiddle with the sheet or fomulas.

Sarah_lee123

Ok, thanks so much for this. I am looking for something that I can set and forget, but I think I am reaching a limitation with the Smartsheet program wrt trying to batch things wioth multiple parts.

The formulas you suggested work great! But it's too much sheet management for my purposes. It will get forgotten about somewhere down the road and my future manager won't likely catch it to fix it later.

Yhanks so much for your response and support on this.

Sarah_lee123

@Mike TV

I have another question as I attempt a different way of doing this.

My goal now is to pull in the harvest batch name from a row. This can be based on a number in the FLOWER UID column or a number in the SHAKE UID column. I am trying to craft an "OR" statement in conjunction with INDEX/MATCH.

I am really bad a AND and OR functions, so I would love support on this.

Attempted equation

=IFERROR(OR(INDEX({HARVEST BATCH}, MATCH([Input Material UID #2]@row, {SHAKE UID}, [Input Material UID #2]@row, {FLOWER UID}, 0)), ""))

In the below screenshots you will see that I am trying to return Harvest Batch Name "F0024" when I enter either Flower UID "123" OR Shake UID "321"

Thank-you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.