Find the position of an element in a dropdown list

I have two columns:

Column One: It's a multi-select dropdown list with the names of products (the quantity of selected products can be very long)
Column Two: It has the quantities of each product in the same order as Column One, separated by commas.

Example of one row:

Column One (dropdown list with multiple choices): Chair - Table - Tank
Column Two (text): "2,4,6"
This means that there are 2 Chairs, 4 Tables, and 6 Tanks.

Therefore, to make this match, I have to get the position of the product in the dropdown list. (It's like the FIND function, but instead of a string, it's a list).

Any advice on how to find the position of the product on the list would be very helpful! Thank you!

Find more posts tagged with

Sheets

Accepted answers

All comments

KPH

Hi @Simon Easton

I think we can possibly do something using SUBSTITUTE. There is an optional element that lets you change the nth iteration of a character. So we could take your multi-select and look for CHAR(10) which is the line feed between the selections in the drop down list. Then change the nth line feed to a character that does not appear in your drop down, such as * or ~, or |. Then we can use a MID function to return the text between the nth and n+1 special character.

However, this all relies on being able to use =[Column one]@row which appears to alter the order. In your drop down (see Column one below) the items will appear in the order they are in the drop down list. However, the output of this formula appears to change them to alphabetical order. So this is only going to work if your dropdown is alphabetical to start with.

If that looks like an option for you, here is the formula to find the 2nd item in the list:

=IFERROR(MID([Column one]@row, FIND("*", SUBSTITUTE([Column one]@row, CHAR(10), "*", 1)) + 1, FIND("*", SUBSTITUTE([Column one]@row, CHAR(10), "*", 2)) - FIND("*", SUBSTITUTE([Column one]@row, CHAR(10), "*", 1)) - 1), " ")

If you iterate the numbers in bold you can find, 3rd, 4th, 5th, etc.

We need to do something different for first and last. Adding CHAR(10) to the start and end of the string gets a little weird with multiselect lists. But I am going to stop here for today as the sort order might rule out this solution anyway.

Simon Easton

Thank you very much for your detailed response.

This idea works perfectly.

As you said, It is Important that the dropdown list is in alpahabetical order. The problem with this is that the dropdown list is very large and is being updated regularly (it is an inventory).

This is the only way that I thought of to match an item to its quantity (other than creating a huge amount of columns, that would mean that i would have to update each of the columns when the list is updated).

I need some way to do this match efficiently in a form, where I can select many thiferent products from many different options.

Any other ideas would be very helpful.

Thank you!

DKazatsky2

Hi @Simon Easton,

I have a solution that may work for you if you can make a change to the sheet with the item and quantity. I propose creating a help column that is the item and quantity combined.

The formula for this is: =Quantity@row + " " + Item@row + "(s)"

Next, on the other sheet you can use the following formula.

=JOIN(COLLECT({Item and Quantity}, {Item}, HAS(Item@row, @cell)), ",")

The result would like this.

This won't help you if the requirement is that the column must be numbers only.

Just a thought - hope this helps,

Dave

kokokoko

@KPH Your solution is working super well for an issue I'm having, but I still can't figure out how to get to the last item on the list. Can you help point me in the right direction?

KPH

Hi @kokokoko

I'm glad that you are finding it helpful. To find the last option in the list, we can add a line feed to the end of the text.

=IFERROR(MID([Column one]@row, FIND("*", SUBSTITUTE([Column one]@row + CHAR(10), CHAR(10), "*", 3)) + 1, FIND("*", SUBSTITUTE([Column one]@row + CHAR(10), CHAR(10), "*", 4)) - FIND("*", SUBSTITUTE([Column one]@row + CHAR(10), CHAR(10), "*", 3)) - 1), " ")

These three parts in bold do that. Now the formula is looking in the cell in Column 1 and adding a line feed to that data, then looking for the nth line feed, in this case line feed 3, line feed 4, and line feed 3. It will extract the text between line feed 3 and 4. This will work if 4 is the "fake" one we added by putting the extra CHAR(10) in the formula.

kokokoko

Thank you that worked!

KPH

Excellent news, I'm pleased that helped!

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, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.