Google Spreadsheet Post #1034
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Feb 18, 2013
user Michael Smit said:(http://productforums.google.com/forum/?zx=4jqkdv8lkjz7#!category-topic/docs/spreadsheets/jrIOJTP7eGk)
How to transpose data from one column, only if certain conditions are met by each row in that column
user Michael Smit said:(http://productforums.google.com/forum/?zx=4jqkdv8lkjz7#!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.
Thanks!
---
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
It's getting there - I think my language in the initial question and the example I posted confused you. Having re-ordered some data, the need in this case is to only find those items that are TRUE, M, and F, and then list those in descending order of Sheet1 Column H (2,1 or 0). So the data should be 8,11,10 (as the item at ColumnA-number 11 has a value of 2 in column H, but ColumnA-number 10 has a value of 1 in column H).
ReplyDeleteThat's the sort function I'm trying to apply. Really appreciate it! Thanks
Hi Michael:
ReplyDeleteI have adjusted the formulas to apply search criteria to columns D,E, and F only -- and the result now is 11,10,8.
Make It A Great One.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Yogi - thanks so much, and I have to say very impressive your level of response and assistance. I don't mean to challenge what is really more help than I would have ever asked for, but I think it's still missing one piece. What I'm trying to do is filter the items based on the search criteria, and then list them in the order of column H, so those that are "2"s first, then the "1"s, and then the rest. So with this data:
ReplyDelete8 Men's Jeans TRUE M F nil 2
9 Glasses TRUE S C nil 0
10 Men's Slacks Pants TRUE M F nil 1
11 Board Shorts TRUE M F nil 2
Only 8,10,and 11 meet the match of TRUE, M, F. So a list of that would be 8,10,11. But then the SORT I am trying to apply would sort them by the last column (so those with a 2 first, then the 1, and then 0s if there were any). That would result in 8,11,10, and that's the output that is eluding me. Appreciate any thought you have on that, but certainly thank you your input this far!
Hi Micheal:
ReplyDeleteI think, finally we are there ... I have updated both the solutions ...
1) using the FILTER function, and
2) using the QUERY function
so check these out and let me know how it works out for you now.
Make It A Great One.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com