Friday, November 22, 2013

yogi_Compute Count Of Colors By Date From Data In Columns A And B And Cross Tab Count Of Colors By Color And Date

                                          Google Spreadsheet   Post  #1426
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 22, 2013
question by ajta@0316 (https://productforums.google.com/forum/#!topicsearchin/docs/after$3A2013$2F10$2F31$20AND$20-is$3Aduplicate$20AND$20-is$3Aresponded/docs/CpyQOVQ5h0k)
Query function
im really a lazy bastard and dont like to make toooo many formula.. so here i go.. 

Data on the Table 1. plus the =query formula gives me the result on the right


=QUERY(A1:C, "select B, sum(C) group by B pivot A label R 'Name'")

Table 1.
ABC
DATENAME#RESULT ↓↓↓
16-Novred1
16-Novorange2Name2013-11-162013-11-172013-11-18
16-Novblue3blue332
16-Novyellow1indigo21
17-Novindigo2orange213
17-Novred3red132
17-Novorange1yellow121
17-Novyellow2
17-Novblue3
18-Novindigo1
18-Novred2
18-Novorange3
18-Novyellow1
18-Novblue2

but what if there is no column C and the table looks like Table 2.
what (simple) =query formula should i do/use to achieve the same exact result?

Table 2.
AB
DATENAME
11/16/2013red
11/16/2013orange
11/16/2013blue
11/16/2013yellow
11/17/2013indigo
11/17/2013red
11/17/2013orange
11/17/2013yellow
11/17/2013blue
11/18/2013indigo
11/18/2013red
11/18/2013orange
11/18/2013yellow
11/18/2013blue
11/16/2013orange
11/16/2013blue
11/16/2013blue
11/17/2013indigo
11/17/2013red
11/17/2013red
11/17/2013yellow
11/17/2013blue
11/17/2013blue
11/18/2013red
11/18/2013orange
11/18/2013orange
11/18/2013blue
11/16/2013orange
11/18/2013blue
11/16/2013orange


i find =query really helpful for lazy bastards like me in making a table.. 
and one more thing, how can i format the date?
been struggling for hours now..  
query language link > https://developers.google.com/chart/interactive/docs/querylanguage is very helpful, 
but i think i need some human intervention now.. 

thank you!
------------------------------------------------------------------------------------------------------------------------------------------


No comments:

Post a Comment