Extracting data using a formula

catherinemoore

New Member
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.

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Richard Schollar

MrExcel MVP
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
GHIJKL
1SumofCURBALTRANSTYPE
2NUMTRANSDATEINSERTPCOUNTRECGrandTotal
3685BI09/10/20068816
4686BJ16/08/2006077
5GrandTotal81523
Sheet3

Best regards

Richard

catherinemoore

New Member
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

MrExcel MVP
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

New Member
Richard

Fantastic! Thanks.

Catherine

Replies
2
Views
382
Replies
6
Views
613
Replies
33
Views
2K
Replies
0
Views
189
Replies
2
Views
1K

1,172,169
Messages
5,879,442
Members
433,431
Latest member
LikeablePringle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back