SOLVED

Returning list from 3D array

Copper Contributor

I have a weekly (Saturday) market and 40 vendors. I have a matrix with months as the rows and Saturday dates as the columns (1-5). I ask vendors to check the dates they want to be there. I create worksheets 1-40 for the vendors, where the vendors are either the worksheet name or in a NAME field on the worksheet. The worksheets have identical arrays, showing which Saturdays a vendor has checked.

How do I return a list of vendors who’ve checked a given Saturday (I’m clicking on)?

(a) Is there a more elegant way to do this?

(b) What function do I need to generate that list?

 

17 Replies

Paul-

 

I think this is a better setup. 

 

Try these modifications:

 

1. Have one worksheet for all vendors

2. Have a Vendor Dropdown

3. Have a month Drop Down

4. Have a weekend Dropdown

5. Have a Query Worksheet that will tell you, who will perform or be in attendance on X date.

 

I've set it up where the dates of the Saturday's will narrow down to a specific month when the month is selected.  i.e. If you select February it will only show Saturday's in February.  If you select March it will only show Saturday's in March.

 

Use an array formula to help identify the vendors for a particular Saturday:

={IFERROR(INDEX(Data!$A$2:$C$100,SMALL(IF(Data!$C$2:$C$100=Criteria,ROW(Data!$A$2:$C$100)-1),ROW(2:2)),COLUMN()),"")}

 

Saturday.png

 

 

Please see the attached file for an example

 

 

 

Thnk you! I will brood on this after coffee.
A quick question: why doesn’t vendor 1 show up in the list for 2018-01-06? The Query seems as if it should return them as well.

Paul-

 

I went ahead and updated the formula.  There was a reference issue that was preventing the formula from pulling in Vendor 1.  It should work without issue now.  I used 1/6 and 1/20 as test cases.

 

 

Thank you - I’ll get my second cup of coffee and look at the change in the query - I used mostly nested functions back in the day, and I need a bit of time to get my head around the new stuff.

Paul-

 

Here are a few resources to help learn about array formulas.  The most important thing is that you enter them using the key combination CTRL + SHIFT + ENTER.  It may also help to go to the formulas tab and use the evaluate formula option:

 

https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-468...

 

https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/

Oh, beautiful! This would have been very useful 30 years ago, when I was picking my way through ranges in nested functions.
Thank you!

Hi Matt Mickle,

I’m sorry - I must have expressed myself badly. I need to enter all the data in advance for the vendors. As it stands, I seem to be able to enter data in the “data” worksheet for a given date, and pull up the vector of vendors who’ll be there on that date in the “query” worksheet - provided that all the vendors in the “data” worksheet are showing that date.

I don’t seem to be able to enter a matrix of dates for the vendors (vendor 1 will be there on June 02, 09, 16, 23, July 07, 14, 21 etc, vendor 2 will be there on June 02, July 07 etc) and subsequently click on a date in the dropdown menu in the “query” worksheet and get a vector of vendors FOR THAT DATE.

Here’s the example I’ve been working with.

Am I missing a step somewhere, please?

Best,

Paul

Hey Paul-

 

As you mentioned I believe there is a disconnect:

 

1. Can you identify the inputs and what format you would like to enter them? 

2. Can you please provide an example of how you would like the data displayed?

3. A non-sensitive example of the data may help

 

Basically I need a before and after.  What you plan to enter and what you expect to get back.

What you sent in the first place looked as if it did what I needed! The data isn’t sensitive, and you have most of it already. What I want to enter is the list of vendors (1-40), which I assumed I could do in worksheet 3. You’d already entered the dates, which I truncated to show May-December.

What I want - and thought I could do - is to select a vendor in worksheet 1, select a month and then select the dates which the vendor applied for (for example, the first Saturday in each month, first and third Saturday, Saturdays between October and December for seasonal items …). 

I then expected to be able to select a Saturday in worksheet 2 and see a list of vendors who applied for that date, select another Saturday and see another list.

Repeating (to be sure I’m clear this time): I want to pick a vendor from the dropdown list in w/sheet 1 and use the dropdown lists to enter a series of Saturdays (the vendor’s ideal selection) which are then stored. After entering all the vendors and Saturdays, I want to select a Saturday in w/sheet 2 and get a list of vendors requesting that Saturday.

Input: vendor applications, checking requested Saturdays in a 5-week by 8-month grid.

Manual entry in Excel.

Output: list of vendors for a given Saturday (this is why my first thought was the 3D array).

Ideal additional output (can be done manually, but …): list of vendors for a given month showing the number of days requested (as a basis for monthly billing).

I hope I got it right this time!

This is the file with a worksheet showing my ideal output.

Paul-

 

Try using the setup on the "Query" Sheet.  I believe it matches your Ideal Output #1.

It does, but it only shows vendors if I have just entered them in “data” for the specific date. If I enter another date, that vendor disappears from the “query” output.

As the goal is to enter all the dates the vendors want to participate and then use the “query” output for individual dates to see how many (and which) have applied for a given date (will the market be too crowded, do we need to expand street closures etc), this doesn’t quite work for this purpose.

I really did a lousy job describing my problem - sorry!

Paul-

 

Try looking at the file I have attached.  I have provided all three outputs you requested.  Hope this helps.

best response confirmed by Paul Danaher (Copper Contributor)
Solution

Paul-

 

Just wanted to check back in and see if you were able to resolve your issue with the sample file provided?

Thank you, yes! Also, the information on the “new” functionality was very helpful.

1 best response

Accepted Solutions
best response confirmed by Paul Danaher (Copper Contributor)
Solution

Paul-

 

Just wanted to check back in and see if you were able to resolve your issue with the sample file provided?

View solution in original post