• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulas to Avoid Loops?

P

phillygirl777

Member
Hello,

I have a list of participating stores and products that the company carries (please see attached - it's highlighted in yellow). Not all stores have all products available.

What I need to get is a list of available products for each store displayed next to the name of the store (no lbs) - see the desired outcome starting cell A19, and the list of stores that carry a specific product (again, regardless of volume - as long as it's >0) - see J19.

Ideally, I would like to find a way to avoid macros because it will be a part of a shared file on a much bigger scale and with much more confidential information. If it is not possible to avoid a macro with this loop, then also, any help is greatly appreciated. My VBA is very rusty.

Thank you!
 

Attachments

  • Example 10.20.23.xlsx
    16.5 KB · Views: 10
Hi,
Please see the attached file if it helps for you in your answer sheet
 

Attachments

  • Example 10.20.23.xlsx
    19.3 KB · Views: 12
This is an exploration of 365 dynamic array formulas. Since it requires 365 it very likely does not meet the OP requirements but may interest others heavily in to 365 methods. It starts with the simple solutions of filtered 1D ranges dragged down or across as appropriate.

It is tempting to then use BYROW and BYCOL to generate the array of results from one formula but Microsoft's failure to specify the functionality correctly means that the array of array #CALC! error is returned despite the proposed solutions being logically correct. The remaining formula use recursion to stack the rows or columns as appropriate. The final solution do this using recursive bisection which is both faster and can address larger problems before running out of resources.

I appreciate these solutions are only relevant for a tiny proportion of Excel users and apologise for that, but a tiny proportion of 700 000 000 users worldwide could still be a reasonable number!

1698015479986.png
 

Attachments

  • Example 10.22.23 Multiple filters.xlsx
    36.4 KB · Views: 10
Naresh Yadav said:
Hi,
Please see the attached file if it helps for you in your answer sheet
Click to expand...
Hi Naresh,

Once again, thanks for such a fast and efficient reply.

Just one quick question (again, sorry - my apologies, I haven't used the AGGREGATE function in years), but when you get a chance, could you please explain the role of a slash "/" in the formula? Thank you!
 
phillygirl777 said:
Hi Naresh,

Once again, thanks for such a fast and efficient reply.

Just one quick question (again, sorry - my apologies, I haven't used the AGGREGATE function in years), but when you get a chance, could you please explain the role of a slash "/" in the formula? Thank you!
Click to expand...
Also, is it possible to reference the entire columns because my actual data sets includes hundreds of rows and dozens of columns? Thanks again!
 
phillygirl777 said:
Hi Naresh,

Once again, thanks for such a fast and efficient reply.

Just one quick question (again, sorry - my apologies, I haven't used the AGGREGATE function in years), but when you get a chance, could you please explain the role of a slash "/" in the formula? Thank you!
Click to expand...
The AGGREGATE function with parameter15 is essentially SMALL on steroids. The next parameter of 6 sets AGGREGATE to ignore errors. The
1/(INDEX...
construct inserts errors where the original has blanks and so filters out the values you do not require. Makes me thankful I have 365 though that too can be a struggle if you wish to return entire arrays using a single formula cell.
 
Peter Bartholomew said:
The AGGREGATE function with parameter15 is essentially SMALL on steroids. The next parameter of 6 sets AGGREGATE to ignore errors. The
1/(INDEX...
construct inserts errors where the original has blanks and so filters out the values you do not require. Makes me thankful I have 365 though that too can be a struggle if you wish to return entire arrays using a single formula cell.
Click to expand...
Thanks! I have 365 both at home and at work. Unfortunately, the formula didn't work with the large set of data. Do you think I can have column references, i.e., instead of Data!$B$2:$J$9, Data!$B:$J ;and instead of Data!$A$2:$A$9, just Data!$A:$A? Also, in the first part of the example, the AGGREGATE function has COLUMN($B$1:$J$1) embedded, but the end of the formula has the COLUMNS function (COLUMNS($B$1:B1). For some reason, the formula reads something else. Should the COLUMN function and COLUMNS function reference the original Data! sheet as well?

Thanks again for your help and patience!
 
No. Do not use entire column references with array formulas. If you add further data on the 'Data' sheet then it should be converted to an Excel Table and use structured references which are dynamic.

Since you have 365, I would recommend moving to use the new functionality it offers. New solutions need bear little resemblance to traditional workbook techniques, as you may have noticed from the workbook I posted. For example @naresh used the formula fragment COLUMNS($B$1:B1). As you drag the formula cell across from cell B1 that will generate the sequence of numbers {1,2,3,...,9} and on. The 365 equivalent is SEQUENCE(1,9) which returns exactly nine values as an array.

My recommendation would be to steel yourself against the culture shock and study the 365 solutions in the workbook I posted. In particular, the formulas with green headers should be reasonably accessible. The formula in the left hand table produces a single row array which then needs to be dragged down to give the complete 'ragged right' 2D array. The term "@store" returns a different region on each line. The entire array SHOULD be obtained simple by omitting the "@" but, unfortunately Microsoft fouled up big time in what was otherwise a brilliant set of developments, and the solution will show a "#CALC!" error instead of the result.

The magenta headed table offers a workaround for the problem but is very difficult to understand and it not something I would draw attention to if I were trying to 'sell' dynamic arrays. One day Microsoft may correct their error of judgement in specifying the way nested arrays, arrays of arrays and arrays of ranges are treated.
 
Thank you! Unfortunately, even with absolute references, the formulas don't work properly with the large set of data, returning values that have 0's in the original data sheet
 
Without formulae, tables on Answer sheet, should the data in the yellow table change, right click each of these new tables and choose Refresh.
 

Attachments

  • Chandoo55226Example 10.20.23.xlsx
    31.8 KB · Views: 4
p45cal said:
Without formulae, tables on Answer sheet, should the data in the yellow table change, right click each of these new tables and choose Refresh.
Click to expand...
Thanks, but the problem was not that I had changed the data. The answers that I was getting when I was trying to apply this formula to a much larger data sets (over 400 rows and 70 columns) were wrong, with static data. The data is not going to change because I am dealing with historical data, so making the formula work is an issue.
 
I am not convinced that size is a problem of itself. If you are not using array formulas then every formula is a potential source of error (array formulas are more 'all or nothing' in terms of errors. I tried one of my array formulas with 100 000 rows and the FILTER function took 30ms. Where Excel runs into problems is formulas in which every calculated value requires a complete scan of the range. Then it is better to limit the size of the problem to 1000s of rows.
 
phillygirl777 said:
Thanks, but the problem was not that I had changed the data. The answers that I was getting when I was trying to apply this formula to a much larger data sets (over 400 rows and 70 columns) were wrong, with static data. The data is not going to change because I am dealing with historical data, so making the formula work is an issue.
Click to expand...
I only mentioned how to refresh the data in the tables because there are no formulae. You would want to check that the results are correct, so to test, you might alter some of the data in the yellow table (adding store names, adding values where they were zero, changing some values which were zero to a positive value, adding/removing different fruit columns) and review the results to check that they properly reflected your changes.
The solution I offered is a Power Query solution. Power Query is designed for big data, and is built-in to Excel.
If you want to explore the solution, right-click on one of the result tables, choose Table and then Edit Query…
I doubt very much your yellow table is representative of your data, so the queries (and any formula solutions) will need adjusting to your real data.
 
@p45cal
Why does the data source show in normalised format in your PQ rather than a crosstab. Where does the unpivot happen?
There are some ideas I could borrow for Excel formulas, namely that of appending items to a comma-separated (or fixed character count) list.
 
After all the complexity of the array solutions I posted above, I realised that I could achieve the same result by a bit of array shaping (TOCOL) and a straightforward SORTBY
Code: 
= LET(
    listStores,  TOCOL(IF(ISNUMBER(quantities), store)),
    listProduct, TOCOL(IF(ISNUMBER(quantities),product)),
    available?,  TOCOL(SIGN(quantities)),
    sorted,      SORTBY(IF(available?, listProduct, ""), listStores, 1, available?,-1),
    WRAPROWS(sorted, COUNTA(product))
  )
and
Code: 
= LET(
    listStores,  TOCOL(IF(ISNUMBER(quantities), store)),
    listProduct, TOCOL(IF(ISNUMBER(quantities),product)),
    available?,  TOCOL(SIGN(quantities)),
    sorted,      SORTBY(IF(available?, listStores, ""), listProduct, 1, available?,-1),
    WRAPCOLS(sorted, COUNTA(store))
  )
 
You must log in or register to reply here.
Back
Top