sikhwizard
New Member
- Joined
- Apr 24, 2014
- Messages
- 8
Hi All,
I have some data (about 45,000 lines) which has 4 columns, DATE, NUMBER 1, NUMBER 2, GRADE,
See example below.
<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
<COLGROUP><COL style="WIDTH: 97pt" span=2 width=129><TBODY>
</TBODY>
However, I want to get the data to look like below;
<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.
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.