tonkerthomas
Board Regular
- Joined
- Feb 12, 2014
- Messages
- 56
Good morning everybody
I have a large table which I need to reorganise. It has two key fields and one of data, thus:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Line[/TD]
[TD]Charge ID[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]10010016[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]10010932[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]2[/TD]
[TD]10020624[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]1[/TD]
[TD]10000883[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010703[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010244[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010243[/TD]
[/TR]
</tbody>[/TABLE]
I need to aggregate that data by contract and type BUT keep the clauses separate (i.e. not sum or count them) AND keep them in order:
[TABLE="width: 800"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Line[/TD]
[TD]Charge ID[/TD]
[TD]Charge ID[/TD]
[TD]Charge ID[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]10010016[/TD]
[TD]10010932[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]2[/TD]
[TD]10020624[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]1[/TD]
[TD]10000883[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010703[/TD]
[TD]10010244[/TD]
[TD]10010243[/TD]
[/TR]
</tbody>[/TABLE]
Any given Contract will have between 2 and 10 Lines, and any given Line can have between 1 and 20 Charge IDs.
A pivot table doesn't appear to be capable of this (you have to aggregate somehow) and I've had a play around in "Get Data" but I don't really know what I'm doing. I'm sure there's a straightforward answer to this, one way or another, but I can't find it. Can somebody help me?
Thanks, as ever, in advance for your help.
Cheers
Jeff<body id="cke_pastebin" style="position: absolute; top: 364.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]10010244[/TD]
[/TR]
</tbody>[/TABLE]
</body>
I have a large table which I need to reorganise. It has two key fields and one of data, thus:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Line[/TD]
[TD]Charge ID[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]10010016[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]10010932[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]2[/TD]
[TD]10020624[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]1[/TD]
[TD]10000883[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010703[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010244[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010243[/TD]
[/TR]
</tbody>[/TABLE]
I need to aggregate that data by contract and type BUT keep the clauses separate (i.e. not sum or count them) AND keep them in order:
[TABLE="width: 800"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Line[/TD]
[TD]Charge ID[/TD]
[TD]Charge ID[/TD]
[TD]Charge ID[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]10010016[/TD]
[TD]10010932[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]2[/TD]
[TD]10020624[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]1[/TD]
[TD]10000883[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]10010703[/TD]
[TD]10010244[/TD]
[TD]10010243[/TD]
[/TR]
</tbody>[/TABLE]
Any given Contract will have between 2 and 10 Lines, and any given Line can have between 1 and 20 Charge IDs.
A pivot table doesn't appear to be capable of this (you have to aggregate somehow) and I've had a play around in "Get Data" but I don't really know what I'm doing. I'm sure there's a straightforward answer to this, one way or another, but I can't find it. Can somebody help me?
Thanks, as ever, in advance for your help.
Cheers
Jeff<body id="cke_pastebin" style="position: absolute; top: 364.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]10010244[/TD]
[/TR]
</tbody>[/TABLE]
</body>
