0

I have this dynamically generated Google Sheet that has crops across columns and planting dates as rows. Each cell represents how many "beds" of that crop we need to plant on that date. What I'm trying to do is convert that layout into a TODO schedule layout like the following example:

Date      Crop      Beds
___________________________
Apr 4     Jicama    5
Apr 4     Radish    2
Apr 4     Beets     3
Aug 15    Carrot    12
Oct 1     Beets     3
Oct 1     Radish    8

I've tried a variety of things but can't seem to sort out how to take each non-zero cell and convert it into a date-crop-value pair to populate another sheet automatically. Since the crop columns and date rows are automatically generated I can't just hardcode it in. Is there a way to do this in Google Sheets? Prefer non-JavaScript solution, but if it needs to go that route that's fine.

Also, I have a really hard time trying to describe the problem so changes to question title and welcomed.

Rob Barreca
  • 641
  • 6
  • 11

2 Answers2

0

A attempt: 

=QUERY({A1:AH68},"select * where "&ARRAYFORMULA(JOIN(">0 OR ","Col"&ROW(1:34))& ">0")&"")
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks for the try. But, I want to "expand" any source sheet rows that have more than one non-zero value into multiple rows in the target sheet with the same date (but different crop). This QUERY doesn't seem to do that. Hrm... – Rob Barreca Oct 09 '17 at 22:54
0

You are using wide table. It will grow in 2 directions: right and down. I prefer using script in this case. Please try to search "how to unpivot table in Google Sheets" or see this answer:

How do you create a "reverse pivot" in Google Sheets?

After unpivot the table will be normalized and then use filter or query function

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81