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.
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
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.
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.
View 24 Replies View RelatedHow 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)
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)
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...
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".
View 4 Replies View RelatedSolution 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] ...........
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....
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
View 4 Replies View RelatedI 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 )
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.
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?
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.
I have a list of fixtures and results arranged by date for a soccer league and have a forumla that I drag down beside each game which creates a value for each side that was competing.
I need for the result of those calculations to become the starting point of the next calculation involving each team, and so on. Is this at all possible?
Would I need a macro to do this or can I get excel to recognise these values and enter them for me?
I've got a multi column sheet containing, amonst other headings, "Date", "Transaction" and then a rolling "Balance". I have multiple rows with the same date and what I'm simply trying to do is have a formula at the top of my spreadsheet that finds the very last row that has data entered so that I can then extract & display the balance as at that point.
Date / Transaction / Value / Balance
01-12-09 / Salary / +£1,000 / +£2,600
01-12-09 / Fuel / -£50 / +£2,550
01-12-09 / Gift / -£100 / +£2,450
03-12-09 / Food / -£30 / +£2,420
03-12-09 / Interest / +£2 / +£2,422
In this example I'd want to return row 5 and then do some kind of lookup to retrieve the value £2,422...
see attached for the sample data. I'm looking for: Sat Score (from column F), that is from the latest/max Report Date (column A), for a Project (column B), THAT IS NOT BLANK
In other words: For each project, we want to know the score from the latest report. But sometimes the latest report doesn't have a score, so we want the score from the latest week that actually has a score. If none of the weeks have a score, return blank.
Sheet 1 is filled with names(column C) and dates(column X). On the sheet 2 I want the latest date for each names. I tried the below formulas. But it is not working.
=IF(ISBLANK(EWS!$X$2:$X$101)," ",MAX(VLOOKUP(Lookup!$D2,EWS!$E$2:$X$101,20,0)))
= MAX(VLOOKUP(Lookup!$D2,EWS!$E$2:$X$101,20,0))[code]....
I'm needing to finding the latest date, of each unique ID (and Name, Criteria).
In my attachment ('Table'), the "Newest" column with the result of "Yes" is what my calculated result should be.
Any formula here (outside of a Pivot)? Because, I then need to delete all of my "No" results from the list.
I have a sheet (attached, Excel 2003) that I need an assist on. What I want to do is search the 'All Games' worksheet for the ten latest games a specific player has played.
That by its self might not be so hard, but I want to take those 10 rows (once identified) and take the sum of column G and divide by the sum of column F for those rows only.
I already have a concatenate cell in the 'All Games' sheet for each row that I use to as a link of sorts to get data from column F and G onto the 'Spring 2009' sheet. The way I have the concatenate set up, I can manually sort by it and get the 'latest 10' values I need, but I need to get it all into a formula to place on the 'Spring 2009' sheet without the manual sort.
Column A | Column B | Column C |
UMB-RPT-01 | 0 | In Review |
UMB-RPT-01 | 0 | Pending Markup |
UMB-RPT-01 | A | Comments - Correct and Resubmit |
UMB-RPT-01 | A | Review Complete |
UMB-RPT-01 | B | Approved |
UMB-RPT-01 | B | Review Complete |
I need to have a macro run through a spreadsheet and look through column a, which is sorted, and then highlight the row that has the latest record. IN the case above it would be 0 with Pending Markup
So the latest value in Column B, whether its alpha or numeric, with a status in Column C of Pending Marking.
I would like to create a macro that looks in a specific directory "h:data"
It finds the 6 most recently modified files in this directory (csv files)
Then imports these 6 files into seperate worksheets in the same workbook "sorter.xls"
i have this kind to table
200
100
250
65
and i want the latest value "65" will appear in the follwoing cell
and when more values added like if the value "70" added then in the cell the value "70" will be appear.
XYZ001
XYZ002XYZ003
XYZ004XYZ003XYZ004XYZ001XYZ002XYZ004
I have data in above format (including Dup). Is there anyway I can get the postion of latest entry. say if it is xyz011, it should return 8. Match will start counting from first cell and go downward. How can do a inverse counting?
I have a data field of about 2000 rows & 30 columns.
These contain records on accidents for 56 offices.
One of the columns contains the date an individual had the accident.
There can be a number of records for each office as there are a number of employees in each office who have had accidents over the last few years.
I need to be able to run a macro or set up filters or formulae to show the row containing the most recent accidentin each office, by office. I also need to display the total number of days since the last accident occurred at the end of each row.
The offices are listed in Column B & the date of the last accident is listed in Column V.
If I have a row full of dates and I want to find the most recent date, how do I do that?
View 2 Replies View RelatedHow to find the latest price by each sku in the list, the list contains many skus.
View 13 Replies View Relatedformula that can pull the latest date for a product, if there are multiple dates attached to the product?
For example, in the attached, I have a Customer and Deal Date list, for Customer A, there are 3 dates, but I only want the latest date: 2/14/2014.
I have a list of values which also has duplicates. I want to remove the duplicates but after comparing it to the dates. Retain the latest date and remove the old dates Eg. there are two duplicates, one has a date of 31st Jan 2013 and the other one has a date of 25 jan 2013. I want to remove the duplicate which has the older date ( 25 Jan 2013) and keep the one which has the latest date.
View 2 Replies View Related