Forum Discussion

Paul Danaher's avatar
Paul Danaher
Copper Contributor
May 20, 2018
Solved

Returning list from 3D array

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 wor...
Formulas & Functions
functions
  • Matt Mickle's avatar
    Matt Mickle
    Jun 08, 2018

    Paul-

     

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

Paul Danaher's avatar
Paul Danaher
Copper Contributor
May 21, 2018
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.
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 21, 2018

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.

 

 

  • Paul Danaher's avatar
    Paul Danaher
    Copper Contributor
    May 21, 2018
    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.
    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      May 21, 2018

      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-4686-9372-ecfd5caa57c7

       

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

      • Paul Danaher's avatar
        Paul Danaher
        Copper Contributor
        Jun 05, 2018

        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

Resources