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.

Date	Number 1	Number 2	Grade
01/01/2014	02476959972	02078765369	1
01/01/2014	02476959973	02078765617	3
01/01/2014	02476959976	02078765328	1
01/01/2014	01212783637	01203875905	1
02/01/2014	02476959972	02078765369	3
03/01/2014	02476959972	02078765369	11
03/01/2014	02476959972	02078765369	3
04/01/2014	02476959972	02078765369	3
05/01/2014	02476959973	02078765617	2
05/01/2014	02476959973	02078765615	6
05/01/2014	02476959972	02078765369	3
06/01/2014	02476959973	02078765617	5
06/01/2014	02476959972	02078765369	3
08/01/2014	02476959973	02078765617	6
11/01/2014	02476959973	02078765617	5
16/01/2014	02476959973	02078765617	4
18/01/2014	02476959973	02078765617	5
18/01/2014	02476959973	02078765615	6

<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>
</TBODY>

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

Row Labels	Sum of Grade
01212783637	1
01203875905	1
02476959972	27
02078765369	27
02476959973	36
02078765615	6
02078765617	30
02476959976	1
02078765328	1

<COLGROUP><COL style="WIDTH: 97pt" span=2 width=129><TBODY>
</TBODY>

However, I want to get the data to look like below;

Number 1</SPAN>	Number 2</SPAN>	Sum of Grade</SPAN>
01212783637</SPAN>	01203875905</SPAN>	1</SPAN>
02476959972</SPAN>	02078765369</SPAN>	27</SPAN>
02476959973</SPAN>	02078765615</SPAN>	6</SPAN>
02476959973</SPAN>	02078765617</SPAN>	30</SPAN>
02476959976</SPAN>	02078765328</SPAN>	1</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

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.

Sum of Grade</SPAN>
Number 1</SPAN>	Number 2</SPAN>	Total</SPAN>
01212783637</SPAN>	01203875905</SPAN>	1</SPAN>
02476959972</SPAN>	02078765369</SPAN>	27</SPAN>
02476959973</SPAN>	02078765615</SPAN>	6</SPAN>
????</SPAN>	02078765617</SPAN>	30</SPAN>
02476959976</SPAN>	02078765328</SPAN>	1</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[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

01212783637	01203875905	1
02476959972	02078765369	27
02476959973	02078765615	6
02476959973	02078765617	30
02476959976	02078765328	1

<tbody>
</tbody>

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.