# Last Transaction Value

Jul 15, 2009

I have an excel file here that has a list of values. What I would like to do is write a formula that finds me the last transaction value of a particular item code. eg. the final list should show :
30100 cost 1.04 (June Value)
30103 cost 1.25 ( Apr- Value)

I am trying to identify each month the last transaction value of a particular item.

## Date Of Last Transaction

Oct 1, 2009

I'm dealing with 2 spread sheets. The first, "Transactions", lists transactions by date, but they are grouped by series. So the 7 series dates are ascending but when you continue to the 8 series it starts at the first date of transaction and ascend to the latest. The second spread sheet, "Sheet1" is an inventory and lists date of last transaction, part #, Part name, sorted ascending by part #. I need a function that will reference the Part # in sheet1 and yeild the date of the last transaction made with that part #. I have not been able to figure it out.

## Getting The Latest Transaction

Mar 5, 2007

i want to create a function / macro for this scenario:

if ID123456 has the status open dated 01/05/07 and the user later decided to change the status of ID123456 to closed on 03/05/07. The graph should count the number of closed then lessen the count of open because the previously open ID is already closed.

## List Transaction With A Sum Value

Jul 20, 2009

How to group a list of cells with sum value shown.

Example,
A1 to A10 = Dates - D/M/Y (May)
A11 to A15 = Dates - D/M/Y (June)
and so on

B1 to B10 = Value for May transactions
B11 to B15 = Value for June transactions
and so on

What I wish to do is, group all May transaction a sum value of B1 to B10 so that it will appears this way (A1 = May ; B1 = Sumvalue). I also wish to have a way to expand and collapse. Something like clicking on a button or something to expand a list of transaction in the month of May. In this case, a list of transaction from A1 to B10 would appear. Clicking the button again will fold the list and will show (A1 - May and B1 - sumvalue)

## Subtotal Under Each Transaction

Nov 21, 2006

Having n issue explaining a statement to a customer. I want to list his incoming payments and invoices in an Excel sheet. So going in order of the statement I'll list the in and outs but I would like a subtotal under each transaction so he can follow easier. Example

\$68,920.77
Subtotal \$68,920.77
\$13,812.43
Subtotal \$82,733.20
\$12,563.14
Subtotal \$95,296.34
\$(20,000.00)
Subtotal \$75,926.34

Is this possible using the subtotal command? Here is what I'm getting
\$68,920.77
\$68,920.77 Total \$68,920.77
\$13,812.43
\$13,812.43 Total \$13,812.43
\$12,563.14
\$12,563.14 Total \$12,563.14
\$(20,000.00)
\$(20,000.00) Total \$(20,000.00)

## Calculating Aging For A Particular Transaction?

Jan 5, 2013

I have tried this. actully i am calculating aging for a particular transaction.

This is my needs. if A1 = between or equal to 0-2 B1 = 0-2 days
A1 = between or equal to 3-5 B1 = 3-5 days
A1 = between or equal to 5-10 B1 = 5 to 10 days
A1 = greaterthan 10 B1 = More than 10 days

Acrully am triying this formula =IF(A1<2, "0-2 Days","5-6 Days") if more than 2 scnario i cant...

Oct 24, 2009

I have a form that requests random info. All this information is saved to a worksheet. I would like to give each transaction a reference number. In order to do so I would like each time a new record is saved to add 1 to the previous reference number. Also I would like to start the transaction with a unique identifier "ie. rn1001".

## Keep Track Of Bank Transaction In Budget - In And Out

Mar 19, 2014

Solution to keep track of bank transaction in a budget - both in and out

With:

=SUMPRODUCT(--(Table[[CAT]:[CAT]]=\$C5);--(Month(Table[[DATE]:[DATE]])=G\$3);--(Table[[IN]:[IN]]))

Table1:
DATE | CAT | IN | OUT
----------------------

The transaction CATegory and Date place the amount from table - column IN to the corresponding cell in my budget.
I would like my bank account to reflect both income an expenses from 2 columns in Table: IN and OUT

Workbook example: [URL] ...........

## Finding Earliest Date And Transaction By Name

Apr 2, 2012

I'm working through a large database on sheet 1 that has multiple transactions for each person, what i am trying to do is find the earliest date and the first amount that each person deposited money. The findings will go into a table on sheet 2

This is a dummy data of what i have: Sheet 1 - database....

## Get Grouped Data Corresponding Last Transaction Date

Apr 26, 2008

I am trying to automatically show the date of the last transaction per part number. I have attached an example. The formula should also consider the data in WH, BRN, and TRNTYP columns

## Automated Data Entry / Transaction Recording?

Apr 13, 2012

I have it all set up already with macros and formulas and such. My next step is to record the sales data ( selected cells ) from the invoice to a seperate sheet I call Cashup. The idea being to record all relevant sales data for the day/trading session on one sheet so the manager can cash up the till run by 2-4 different people during the day. Its targeting a small family run business so security isn't currently the main concern although for future proofing I'm trying to build that in as far as possible. Long story short: What would be a good method for recording each successive sale's data from the same page each time the Finish button is hit , to a seperate sheet in a log format? Currently I have these problems:

1:The operation should record into the next open row on the Cashup sheet and not overwrite the previous record.

2:The record must ignore all formulas and record only the values in the cells . Currently a normal copy and paste results in " N/A" recorded in the cells in the Cashup screen.....( probably since all the data that needs recording is formula generated to begin with )

## Showing Latest Transaction On Product List

May 31, 2007

I have a spreadsheet and it shows a raw data dump of all sales transactions per product. This has been transferred via ODBC and I need some sort of query to filter out the data.

Is there any way I can write a query to only bring in the latest transaction based on the sale date per product?

Columns are:

Product Code|Last Sales Date|Cost Price

## Identifying The Same Transaction With Multiple Rows Of Data

Sep 21, 2009

I currently have a database that is similar to the example I've pasted below:

TypeDateTimeA10/01/200910:00B10/01/200910:00C10/01/200910:00D10/01/200912:00E10/01/200912:00

As you can see the Date and Time are repeated for several rows. This is how the data I receive comes through as A,B and C refer to a single transaction and D & E refer to another transaction.

Ideally what I would like to acheive is:

TypeDateTimeTransactionA10/01/200910:001B10/01/200910:00C10/01/200910:00D10/01/200912:002E10/01/200912:00

Where Excel can compare the date and times of each row and look for matching rows above and below it and then fill in a column next to it indicating that x number of rows are linked to a single transaction - preferably labelling them in some order to I can tell how many transactions there are.

## IRR- XIRR - Annualized Rate Of Return On Single Transaction

Dec 2, 2009

Here's my problem:
A) This is regarding a real estate transaction.

B) Assume the funds to acquire the investment total \$60,000.

C) Assume the purchase date is 1/1/10.

D) Assume the sales price (including costs) would total \$76,000.

E) Assume the sales closing date is 4/1/10.

F) What formula function would allow me to show the Annualized Rate of Return on this transaction?

I Know this is something I should have been able to figure out -- but when I use the XIRR function, I end up with a figure of 167% -- and common sense indicates that's just not correct....?

What I need to do is just find a way to show either the IRR or the Annualized Rate of Return for investment consideration purposes. Is it possible to do this based on one single transaction that occurs in a fraction of a year?

## If Statement/indexing: Column To Adjust Based On The Date Of The Transaction/trip

Jun 17, 2009

I have an employee expense voucher with a column that calculates mileage. I also have a column that lists what the mileage rate is. I'd like for that column to adjust based on the date of the transaction/trip.

Here's an example. Trip between 7/1/08 - 12/31/08 = .585 mileage. Trip between 1/1/09 - 12/31/09 = .405 mileage. If someone turns in a receipt for a trip taken on 11/13/08, I want the form to adjust the mileage rate to .585. Wasn't sure if this is an indexing kind of thing, or if there's an if statement I could use.