One VLOOKUP cell gives me a column list of returns

SRenner

I came up with this formula to help search the run name. but i need for the other rows below the top one to give me the next store in sequence, not the same store. I thought INDEX would let me do this manually but not what i was thinking.

=INDEX({DRIVER STORE MASTER 02242020 Range 2}, MATCH($[Run Name]$2, {DRIVER STORE MASTER 02242020 Range 3}, 1))

Thanks

Scott

Paul Newcome

That is where the MATCH comes into play. You can use it to automate the row number and/or the column number for your INDEX function. Let me "re-familiarize" myself with this thread, and I will see if I can help with some more specific info if you need further.

Paul Newcome

Do you have a column on your master sheet that replicates the row number? If so, you could use an INDEX/MATCH to search the run column based on a SMALL/COLLECT pulling row numbers (if that makes any sense). If you do have row numbers established on the master sheet, I can give you some more detail into that.

If you do not have row numbers, I have a parsing solution we could tailor to suit your needs where we join all of the store numbers into a string and then parse it back out on your other sheet.

Let me know which you would prefer.

SRenner

Paul,

I have a column on the master that is the delivery order so for example I would have; the unloading point resets at the start of each run. If that is what you mean. I do not have a column that counts each one unique for each customer. if that makes sense. If i need to have that to make it work, i can easily add a column with numbers 1-4800 in it.

I would prefer the index match small collect formula if possible. if not possible, the string theory might be the way to go. lol.

Please understand that the master list is not stagnate. it is updated monthly with the same format but stores could be on other runs or different order. I plan to copy and paste the master which is why Im using the other sheet to formulate from the master.

Thanks!

Paul Newcome

Ok. So the easiest way to make this work is to have a column on the master sheet that replicates the row number.

Then we will move to the metrics sheet where we need to do a little bit of planning ahead. You are going to need to enter the numbers 1 through whatever down a column to cover the maximum number of stores you anticipate having for a run plus a little buffer just in case. So if you think the most stores you will need to pull is 50, then go ahead and prefill numbers 1 - 75. If you think the max is going to be 200, maybe go to 250.

You would need to plan ahead for both solutions, but the INDEX solution is definitely going to be easier to set up and manage.

Paul Newcome

Once you get that set up, it would be very helpful to have a screenshot of both the master and the metrics sheets so that at least the column names are visible and an indication of which sheet is which/which columns we are going to be referencing. That will help me get a more detailed solution to you to help avoid confusion when we get into the more complicated (but not too complicated) parts of this.

I will also provide a walk-through of how the solution is built so that hopefully you can tweak, expand, or replicate in the future without having to wait on someone else to get back to you.

SRenner

Here is what I have set up to get us started. I have this in a smartsheet community workspace that i would be happy to share with you if you want.

This first screenshot is the store master. i numbered the Store count column to 4499 all the way down.

On my metrics sheet i have the run names as drop downs and numbered the store count to 25. there is a max of 18 stores on a run. then i have the store# column where i would like to populate the sequence of stores for that run.

I hope this helps. send me your email if you would like access to the workspace.

thanks

Scott

Paul Newcome

Perfect.

Note for the [Store Count] column in your master sheet...

I am not sure how you populated the numbers, but what I always do is

=COUNTIFS([Other Column]$1:[Other Column]@row, OR(@cell = "", @cell <> ""))

This will automate the count and truly duplicates the row number. If you manually enter the numbers and then sort, everything will be all mixed up. If you add a new row, you have to manually add the number.

With the formula, Row 1 is ALWAYS going to have a 1, Row 2 will always have a 2, so on and so forth. It should also autofill the formula when new rows are added.

Just a little note that may make life easier in the future in case you didn't already know that one.

Now on to the solution...

So we know we want an INDEX function to pull our data from the other sheet, and we already know what our range is, so we can go ahead and get that started...

=INDEX({Master Sheet Store # Column},

But now we need to find a way to automate the row number that it pulls from. Since we already have the row numbers listed in the Master Sheet, we can pull those. But how do we determine which one to pull, and how do we pull all of them?

We will start with a COLLECT function since we know we want to collect specific row numbers based on the [Run Name].

COLLECT({Master Sheet Store Count Column}, {Master Sheet Run Name Column}, [Run Name]$1)

Now that we have collected the row numbers, we need to sort them back out. That's where the SMALL function comes into play.

The SMALL function allows us to evaluate a group of numbers and pull which one is the first smallest, second smallest, third smallest, etc. We can automate this first, second, third etc ranking by leveraging the numbers you have on your sheet already in the [Store Count] column.

SMALL(group_of_numbers, [Store Count]@row)

SMALL(COLLECT({Master Sheet Store Count Column}, {Master Sheet Run Name Column}, [Run Name]$1))

As we dragfill this formula down, it will automatically update to pull whatever row number is in the other sheet that is the first/second/third/etc smallest from the row numbers that were pulled based on the [Run Name].

Now that we have finally established our row number for the INDEX function, we can pull it all together, drop it in row 1, then dragfill on down the line.

=INDEX({Master Sheet Store # Column}, SMALL(COLLECT({Master Sheet Store Count Column}, {Master Sheet Run Name Column}, [Run Name]$1)))

This will also keep the stores in the same order that they appear in the Master Sheet. f you have any type of sorting applied to it, your Run Listing will follow suit.

SRenner

Awesome! I am having a couple issues though....

First, I am not understanding the store count formula. I understand the function of it but not sure how to apply it.

=COUNTIFS([Other Column]$1:[Other Column]@row, OR(@cell = "", @cell <> ""))

Am i putting this formula in cell 1 of the store count column? also what is the "other column" I am referencing for these cells to keep the same as the row number?

Second, I entered the formula as you laid it out and im getting an incorrect argument set.

i changed my ranges to explain what columns Im using from the master.

Could it be because i dont have the row formula set up from the first part?

thanks

scott

Paul Newcome

For the Store Count formula on your MASTER SHEET, you can reference any other column you want.

Basically it starts counting at Row 1 and continues to count to @row. The OR portion says to count if the row is blank or not blank.

This basically replicates the row number so that it can be referenced directly which allows us to use the SMALL function.

The #INCORRECT ARGUMENT SET error is my fault. The parenthesis got me. We SHOULD be closing out the COLLECT function and THEN specifying the number in the SMALL function. Like this...

=INDEX({Master Sheet Store # Column}, SMALL(COLLECT({Master Sheet Store Count Column}, {Master Sheet Run Name Column}), [Run Name]$1))

SRenner

I was able to get the row count figured out, thanks to a previous thread you were in. lol.

But I am still having issues with the second part. Im making it like your correction but still am getting the in correct argument set.

Paul Newcome

That's odd. Hmmm... Double check your ranges. Sometimes the sheet takes a minute to load on the back-end. What happens then is it will let you click on the column header, but then will jump back (without warning) to the cell in the top left corner of your sheet.

If that isn't it, I have been noticing a trend these past few days where the solution is to log out, clear cookies and cache, then log back in and the formula works. Sometimes even something as simple as leaving the sheet then returning it has fixed it.

Paul Newcome

I will throw a quick test together as well to see if I can get it to replicate the issue.

SRenner

yeah i know what you mean. but in this case, all of my ranges are correct per the formula and accounting for the entire column.

Here is my master sheet with the info. i have the row count formula in place.

Here is the metrics page again. the ranges are = to what i named them (i double checked)

sorry im not getting it. hope this helps. thank you!!!

Paul Newcome

Did you try the logging out steps yet? I haven't had a chance to put together a test run to try to replicate it yet.