0

I have the Fruit Count tables on the left. If at all possible, what formulae would dynamically provide the values in the highlighted F4:G6 range without script editor?

Screenshot of tables

Here is the live spreadsheet for copy.

6
  • I have thought about using something similar to the question but to no avail: webapps.stackexchange.com/q/95457/55520
    – Julio
    Jul 15 '20 at 20:15
  • Julio, your spreadsheet permission is currently "View Only." So there is no way for anyone to provide you with formula help. Please change the permission to "Anyone with the link can edit." Then I or someone else here will be happy to help you.
    – Erik Tyler
    Jul 16 '20 at 4:29
  • This can definitely be done by formula. But again, it would be a lot more work to share it here by explanation alone than directly in your sheet.
    – Erik Tyler
    Jul 16 '20 at 4:37
  • @ErikTyler Make a copy of the spreadsheet.
    – Tedinoz
    Jul 16 '20 at 10:43
  • @Tedinoz, I have. My point is that showing and explaining the solution would then require me to have to post my own copy of the spreadsheet, which, by protocol, would then need to stay attached here for future reference; or I'd have to spend longer explaining than if I simply had access to the sheet. It's standard practice to ask the OP to post an editable copy of the spreadsheet.
    – Erik Tyler
    Jul 16 '20 at 12:18
1

Here's the short version, Julio:

1.) First, you'll want to have "January 2020" instead of just "January," etc. If you only have the words, a pivot will try to order them alphabetically, since they are just words. This would leave "February" before "January," etc., which isn't what you want. In addition, once January 2021 starts, QUERYs would start to blend both sole "January" words together, etc., which, again, isn't what you want. So start by adding "2020" to all your month names in your raw data chart. That will be enough to convert them to dates, which can be ordered accurately, now and into the future.

2.) Place the following array formula anywhere in the sheet:

=ArrayFormula(QUERY(QUERY({IF(B3:B="",A3:A,VLOOKUP(ROW(B3:B),FILTER({ROW(A3:A),A3:A},A3:A<>""),2,TRUE)),SUBSTITUTE(SUBSTITUTE(B3:C,"Item",""),"Total","")},"Select * Where Col2 Is Not Null"),"Select Col1, MAX(Col3) Group By Col1 Pivot Col2"))

The formula will adapt itself as new raw data is added in Columns A and following.

If you're going to place it in a separate sheet, be sure to edit all range references in the formula to include the raw-data sheet name (e.g., Sheet1!B3:B, etc.).

3.) Most likely, the chart that is formed will show the dates along the left side in their raw form: as numbers in the 40000 range. Just format that range (or entire column) to show the dates as you like. (You can custom format them to be just the month name, in fact, but I'd still recommend something like "MMM yyyy."

4.) If you like you can further style the chart cells (e.g., bold the top and left-side labels, etc.

5.) This shows all fruits. If you truly only wanted "Apple" and "Banana," there are many ways to go about it. But probably the easiest to understand is to just wrap everything in yet another QUERY like this:

=ArrayFormula(QUERY(QUERY(QUERY({IF(B3:B="",A3:A,VLOOKUP(ROW(B3:B),FILTER({ROW(A3:A),A3:A},A3:A<>""),2,TRUE)),SUBSTITUTE(SUBSTITUTE(B3:C,"Item",""),"Total","")},"Select * Where Col2 Is Not Null"),"Select Col1, MAX(Col3) Group By Col1 Pivot Col2"),"Select Col1, Col2, Col3"))

So maybe not that short. Hope it gets you where you're trying to go.

1
  • Julio, if my answer and explanation worked for you, please take a moment to mark it as the correct answer. It allows me and others to know that the issue has been resolved and no longer needs additional solutions.
    – Erik Tyler
    Jul 18 '20 at 23:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.