Extracting data using a formula

catherinemoore

New Member
Joined
Oct 10, 2006
Messages
10
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Oct 10, 2006
Messages
10
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
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,136,309
Messages
5,674,996
Members
419,541
Latest member
freddyboots

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top