Aggregating records with date-stamps into years (or months)

[sdspieg]

I have a spreadsheet with 3 columns (the first one has a date like '20100224', the second one a text string like 'european union' and the third one a numeric string with a value like '3') and 250'000 rows. Is there a way to aggregate and reorganize these data in such a way that I get sthg like this - i.e. with all dates aggregated into years, and those years then displayed vertically in declining order and horizontally next to each other first a column with the top text string for year x and in the next column the sum of all the values for that text string for that year; and then in the next two columns the same but then for year x+1, etc.?

 

[sdspieg]

Ok - so I now have a pivot table with all of my text strings vertically in the first column, then the individual dates horizontally in the next ones, and the values in the cells. So far so good. But now how do I aggregate the individual dates into years?

 

[cyrilbrd]

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

add one column to your row data let's say in Cell C1 type 'Year' and in C2 type =LEFT(A2,4) copied down till needed.

In your pivot select 'change source' select whole range including newly created column.

Place "year" in 'Column Labels'.

Filter to the period of your choice.

would that work for you?

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>

 

[sdspieg]

I'm still stuck. I have figured out a way to group these dates veretaically (i.e. with the dates in row), but not horizontally (with dates in columns). And the problem is that I have way more than 17000 text strings, so excel won't let me put those in the columns. I'd really appreciate any help people might be able to give me on this one...

 

[sdspieg]

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

add one column to your row data let's say in Cell C1 type 'Year' and in C2 type =LEFT(A2,4) copied down till needed.

In your pivot select 'change source' select whole range including newly created column.

Place "year" in 'Column Labels'.

Filter to the period of your choice.

would that work for you?

<tbody>
</tbody>

I understand what you're pointing at and I guess that might indeed work. But my date format now looks like this 2/24/2010, and so when I use your formula on that I get '4023'.

 

[cyrilbrd]

Oh... no it would work for a format such as 20000810 (in your original file...), for 2/24/2010 just use =year(A2)
This is how the pivot would look like:

Row Labels	2009	2010	2011	2012
, like brief			2
"india-france: partnership future			2
"indo-european research innovation				2
(central europe), mr.			2
(central europe):		4
(eurasia) , like			2
(europe		4
(europe west):				6

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[sdspieg]

Yup! That one works. Thanks so much! It's still amazing that Tableau did the pivot 'right' automatically (it somehow 'guessed' that I'd want to group all dates into years), whereas excel doesn't seem to be able to do it, except for through workarounds like the one you just (creatively!) suggested.

 

[cyrilbrd]

Oh you are using Tableau as well? Well Tableau's strength is set for easy dashboard creation with fast graphical presentation... Glad it somehow worked for you.
Thanks for the feedback.