Formula for obtaining data from a pivot table where there is 1 column with 2 headings

[sikhwizard]

Hi All,

I have some data (about 45,000 lines) which has 4 columns, DATE, NUMBER 1, NUMBER 2, GRADE,

See example below.

[TABLE="width: 215"]
<COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=2 width=84><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><TBODY>[TR]
[TD="class: xl66, width: 75, bgcolor: silver, align: center"]Date[/TD]
[TD="class: xl67, width: 84, bgcolor: silver, align: center"]Number 1[/TD]
[TD="class: xl67, width: 84, bgcolor: silver, align: center"]Number 2[/TD]
[TD="class: xl68, width: 44, bgcolor: silver, align: center"]Grade[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]01/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl71, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]01/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]01/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959976[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765328[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]01/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]01212783637[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]01203875905[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]02/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl71, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]03/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]11[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]03/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]04/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]05/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]2[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]05/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765615[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]6[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]05/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]06/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]5[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]06/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959972[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765369[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]08/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]6[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]11/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]5[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]16/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]4[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]18/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765617[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]5[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: center"]18/01/2014[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02476959973[/TD]
[TD="class: xl70, width: 84, bgcolor: transparent, align: center"]02078765615[/TD]
[TD="class: xl72, width: 44, bgcolor: transparent, align: center"]6[/TD]
[/TR]
</TBODY>[/TABLE]

I find it easy putting this in a pivot table as the data looks as below, and its easy to relate that where;

Number 1 = 01212783637, then this has a value of 1 where it is associated to Number 2 = 01203875905

Number 1 = 02476959973, then this has a value of 6 where it is associated to Number 2 = 02078765615 & a value of 30 where it is associated to Number 2 = 02078765617
[TABLE="width: 194"]
<COLGROUP><COL style="WIDTH: 97pt" span=2 width=129><TBODY>[TR]
[TD="class: xl68, width: 129, bgcolor: #dce6f1"]Row Labels[/TD]
[TD="class: xl68, width: 129, bgcolor: #dce6f1"]Sum of Grade[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]01212783637[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]01203875905[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]02476959972[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]02078765369[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]02476959973[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]36[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]02078765615[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]02078765617[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]02476959976[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]02078765328[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[/TR]
</TBODY>[/TABLE]

However, I want to get the data to look like below;
[TABLE="width: 271"]
<TBODY>[TR]
[TD="align: center"]Number 1</SPAN>[/TD]
[TD="align: center"]Number 2</SPAN>[/TD]
[TD="align: center"]Sum of Grade</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]01212783637</SPAN>[/TD]
[TD="align: center"]01203875905</SPAN>[/TD]
[TD="align: center"]1</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]02476959972</SPAN>[/TD]
[TD="align: center"]02078765369</SPAN>[/TD]
[TD="align: center"]27</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]02476959973</SPAN>[/TD]
[TD="align: center"]02078765615</SPAN>[/TD]
[TD="align: center"]6</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]02476959973</SPAN>[/TD]
[TD="align: center"]02078765617</SPAN>[/TD]
[TD="align: center"]30</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]02476959976</SPAN>[/TD]
[TD="align: center"]02078765328</SPAN>[/TD]
[TD="align: center"]1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Am I going about this the wrong way and if I was to continue with the Pivot table, how would i separate the 2 headings in the same column?


Thanks in advance.

 

[pplstuff]

Right click the pivot table, click options, go to display tab, check the box 'Classic Pivot Table View".

 

[brian.wethington]

ah. I see now what you have done... removed my suggestion.

 

[sikhwizard]

Thanks thats fantastic guys.

I now have a related question, if there is a Number 1 has 2 or more associated values for Number 2, how can this be displayed for each line?

Where ???? would be 02476959973</SPAN>. I have seen where you can merge the cells, but I want a 1:1 relationship if that makes sense.

[TABLE="width: 215"]
<TBODY>[TR]
[TD]Sum of Grade</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Number 1</SPAN>[/TD]
[TD]Number 2</SPAN>[/TD]
[TD]Total</SPAN>[/TD]
[/TR]
[TR]
[TD]01212783637</SPAN>[/TD]
[TD]01203875905</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]02476959972</SPAN>[/TD]
[TD]02078765369</SPAN>[/TD]
[TD="align: right"]27</SPAN>[/TD]
[/TR]
[TR]
[TD]02476959973</SPAN>[/TD]
[TD]02078765615</SPAN>[/TD]
[TD="align: right"]6</SPAN>[/TD]
[/TR]
[TR]
[TD]????</SPAN>[/TD]
[TD]02078765617</SPAN>[/TD]
[TD="align: right"]30</SPAN>[/TD]
[/TR]
[TR]
[TD]02476959976</SPAN>[/TD]
[TD]02078765328</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[pplstuff]

This cannot be done within the PivotTable. You can copy the data from the pivot table into a new range, but then you lose the ability to slice your data...

If you don't mind losing the pivot...

Copy all data, paste special values into a different area of the workbook, select the entire range, use 'go to special' and go to blanks (hot keys: alt, h, fd, s, k, enter), then hit "=" then up-arrow, then hit ctrl+enter.

That might be hard to follow as it reads, but it works.

 

[brian.wethington]

This cannot be done within the PivotTable. You can copy the data from the pivot table into a new range, but then you lose the ability to slice your data...

If you don't mind losing the pivot...

Copy all data, paste special values into a different area of the workbook, select the entire range, use 'go to special' and go to blanks (hot keys: alt, h, fd, s, k, enter), then hit "=" then up-arrow, then hit ctrl+enter.

That might be hard to follow as it reads, but it works.

This can be done in XL 2007 and later by the following:

1) Select the pivot table
2) Go to the Design menu
3) Click Report Layout >> Show in Tabular Form
4) Click Report Layout >> Repeat All Item Labels

[TABLE="class: cms_table, width: 215"]
<tbody>[TR]
[TD]01212783637[/TD]
[TD]01203875905[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]02476959972[/TD]
[TD]02078765369[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]02476959973
[/TD]
[TD]02078765615[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]02476959973
[/TD]
[TD]02078765617[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]02476959976[/TD]
[TD]02078765328[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

Assuming this is what you are going for.

 

[pplstuff]

This can be done in XL 2007 and later by the following:

4) Click Report Layout >> Repeat All Item Labels

Only in 2010+

 

[brian.wethington]

I thought I used the feature when I was still using 2007, but I stand corrected, I just looked it up. You are correct, 2010+

 

[sikhwizard]

Thanks All,

Brain,

Thats exactly what I wanted.

Much appreciated.