Extracting data using a formula

[catherinemoore]

I have the following problem. I have a spreadsheet which I have taken a sample from below:

NUM TRANSTYPE TRANSDATE CURBAL
685BI REC 09/10/2006 13:22 8
685BI PCOUNT 09/10/2006 13:22 8
685BI REC 16/08/2006 13:50 8
685BI PCOUNT 16/08/2006 13:50 0
685BI INSERT 28/03/2006 13:29 0
686BJ REC 16/08/2006 13:51 7
686BJ PCOUNT 16/08/2006 13:51 0
686BJ INSERT 28/03/2006 13:34 0

For each unique NUM I need to take perform the following calculation: (CURBAL for TRANSTYPE PCOUNT with latest TRANSDATE) - (CURBAL for TRANSTYPE REC with latest TRANSDATE).

Does anyone have any ideas? I've tried using a Pivot Table and Subtotals, but can't seem to get anything near what I need.

Thanks.

 

[Richard Schollar]

Hi Catherine

1. Select your data (ie all of it
2. Go Data>Pivot Table and Pivot Chart
3. Click Finish (this will create your PT on a fresh new sheet)
4. Drag NUM into the row area, TRANSDATE into the row area (to the right of NUM), TRANSTYPE to the column area, and CURBAL to the Data area.
5. Then, on one of the dates in TransDate, right-click and go Field Settings. In the dialog that opens, click Advanced. In the next dialog that opens you want to click 'On' for the Top 10 AutoShow and modify it to show only the Top 1 item. Click OK.
6. You should now have the columns and numbers as shown below, which should allow you to create your formula in an adjacent column:

test.xls
	G	H	I	J	K	L
1	SumofCURBAL		TRANSTYPE
2	NUM	TRANSDATE	INSERT	PCOUNT	REC	GrandTotal
3	685BI	09/10/2006		8	8	16
4	686BJ	16/08/2006		0	7	7
5	GrandTotal			8	15	23
Sheet3

Best regards

Richard

 

[catherinemoore]

Richard

Thanks, that's great. The only problem I have now is that my dates / times are actually in the format dd/mm/yy hh:mm:ss now I've looked at them a bit closer. This means that the TRANSDATEs that look the same actually aren't because the number of seconds are different. Is there any way I can 'get rid' of the seconds? I tried the LEFT function, but that didn't work.

Thanks

Catherine

 

[Richard Schollar]

Catherine

In your original data sheet, select your date column, and go Data>TextToColumns and used Fixed Width and place a column separator between your date portion and your time portion, and click finish. You can then either delete the time column produced, or simply not use it in your pivot table.

Richard

 

[catherinemoore]

Richard

Fantastic! Thanks.

Catherine