0

In Google Sheets I'm trying to to transpose a list of items and their prices (displayed in rows) to automatically display in an adjacent section as columns. The problem is I need to only include the items which have prices. This demonstrates what I'm trying to do:

Intended Result:

Intended Result

I have a list of items with prices in a "Plan" section where items and prices are listed and need to transpose only the data for rows where items have a price and display only the items and their corresponding prices in the "Details" section.

Here's a mock-up of what I currently have working:

Current Version:

Current Version

The problem is that it's listing items whether they have a price or not. It is also listing those items and their prices in unrelated columns.

Currently I am using Named Ranges: Category1_Items and Category2_Items and Price_1 and Price_2 in the following formulas: 

=ARRAYFORMULA(SPLIT(JOIN("~~~",Category1_Items,Category2_Items,),"~~~"))
=ARRAYFORMULA(SPLIT(JOIN("~~~",Price_1,Price_2,),"~~~"))

I know this is not very close to what I need, but it's as close as I have been able to get so far as I'm somewhat of a newbie with Sheets.

I'd like to achieve this without resorting to scripts if at all possible!

Community
  • 1
  • 1
Geoff
  • 21
  • 1
  • 7

1 Answers1

1

There might be subtle details omitted that prevent this from working, but perhaps try:

=TRANSPOSE(QUERY({Category1_Items,Price_1;Category2_Items,Price_2},"select * where Col2 != ''",0))

Note, if the prices are actually numerical values (rather than strings as in your example), you will need to change != '' to is not null.

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • That worked like a charm! I got that part working but I have a follow-up question. Is there a way to also multiply the result of the TRANSPOSE function? Here is the formula that works: `=IF(ISNUMBER(O7),TRANSPOSE(QUERY({Giving_All},"select Col5 where Col5 is not null",1)),"")` But when I add in the multiplication step it stops working. Here is what I tried: `=IF(ISNUMBER(O7),TRANSPOSE(QUERY({Giving_All},"select Col1 where Col5 is not null",1)*O7),"")` Now it includes the multiplication step but only returns the first cell in the series. Is there a way to fix this? – Geoff May 08 '15 at 23:01
  • Wrap the whole formula in `ArrayFormula()`, which you can do by confirming the formula with Ctrl+Shift+Enter (rather than just Enter). – AdamL May 10 '15 at 23:29
  • Thanks! That really helps. But I guess I over simplified my question. I was hoping to multiply by one of the cells in the referenced range. So, say if Col5 is 5, and Col 1 is 10%, then have it return the result from 5*10% as the result. Using ArrayFormula works great but I'm only getting errors when I try to reference anything from the named range. – Geoff May 15 '15 at 16:53
  • You might need to edit the question and explain a bit further, Geoff. You can use `"select Col1 * Col5..."` in the QUERY, but that might be again over-simplifying what you require. – AdamL May 18 '15 at 22:49