Monday, February 18, 2013

yogi_Transpose And Join Data In One Column Of Sheet1 Based On Specified Conditions In Sheet1

                                          Google Spreadsheet   Post  #1034
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Feb 18, 2013
user Michael Smit said:(!category-topic/docs/spreadsheets/jrIOJTP7eGk)
How to transpose data from one column, only if certain conditions are met by each row in that column

Hi - I have a very large spreadsheet of various items and related info (will be over 1000 rows, and frequently being added to or updated). I need to transpose the item names in column A to distinct datasets. For ex:

"Men's Jeans" TRUE M F nil 2 75 "Men's Fashion" MensJeans
"Men's Slacks" TRUE M F nil 2 70 "Men's Fashion" MensSlacks
"Board Shorts" TRUE M F nil 1 45 "Men's Fashion" BoardShorts
"Woman's Belt" TRUE M C nil 0 35 "Women's Fashion" WomansBelt

Needs to then transpose to, for example:

ItemListTRUEMF2 = {"Men's Jeans", "Men's Slacks"}
***(because these items meet the condition of Column B = TRUE, Column C = M, Column D = F, and Column F =2)

I simply can't figure it out. I can create multiple sheets and then transpose those, (for each of the eventual sub-types), but that's no where near as efficient or scalable.

Here's a sample example of where I was hitting a barrier:

the first sheet is the sample dataset, then you can see what formula is applied for the 3 layers of filter, sort, and join:
For whatever reason, I can't get the data to sort, on the 3rd and 4th sheets (it should be 8,11,10 if sorting the output descending on column H, but it still shows as 8,10,11).  Maybe my column H reference has poor syntax?
sorry, you are having difficulty getting this sorted out ... following is an illustrative solution