# 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

## 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.

## Pull Latest Date For A Product?

Jan 14, 2014

formula 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.

## Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

## Showing Earlier And Latest Departure Times

Jan 26, 2012

I have a list of data which contains the destination and departure time. In a cell I would like to type in the destination name and in the two adjoining cells I need the earliest and latest departure times to be shown. Example if I enter Carlisle then the results would be 18:20 and 20:55.

ToDepart
Birmingham20:35
Birmingham20:55
Birmingham21:35
Birmingham22:11
Bristol20:30
Carlisle18:20
Carlisle18:20
Carlisle18:20
Carlisle18:20
Carlisle19:30
Carlisle19:45
Carlisle20:15
Carlisle20:55

## 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)

## Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank

Jun 20, 2008

I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

## Extracting Summary List Of Larger List Showing Only Items That Have Quantities?

Apr 11, 2014

It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.

What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.

I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.

I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.

## Find Latest Price By Each SKU In The List?

Dec 24, 2013

How to find the latest price by each sku in the list, the list contains many skus.

## Return Latest Attempt At Exam From A List?

Mar 10, 2014

I have a list of students that have sat a number of exams on different dates. Can I pick out from the list the last exam for each person. I can sort by id number and then by date but how do I then show the record showing the latest date for each person?

Example
Mary 1/2/2012
john 2/3/2012
john 1/3/2012
mary 1/1/2012
mary 4/4/ 2012

I want to select the record showing the latest date for each person. the result would be
mary 4/4/2012
John 2/3/2012

## Removing Duplicates From List Based On Latest Date?

Jan 6, 2014

I am working on a large data file (leasing file), that has many duplicates. The names on the file are duplicated due to the various variable costs associated with leasing. I need to remove the duplicates names based on the latest contract end date.

## Dynamic Named Range For The Product Name On The Product Details Sheet

Dec 24, 2008

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

## Sum Product To Count Data In A Date List

Feb 23, 2010

I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc...

I now need to amend the formula to count how many times a date appears in a range of dates.

I know when you use the sumproduct...dates must be input and formatted as text.

example

A1
01-Jan
02-Jan
5-Feb
7-Feb
12-Feb

formula to only count dates with feb in?

## Advanced Filter - Extract List Of All Items But Only With Latest Date Of Each

Jan 20, 2012

What criteria can I use to filter a list on and item and the latest date?

I need to extract a list of all items but only with the latest date of each ...

## Pulling Earliest And Latest Times From A List Using Lookup / Formula?

Jul 25, 2014

I'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.

## Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

## How To Count Every 5th Occurrence Of Product Code In Inconsistent List

Aug 31, 2012

I need to identify every 5th occurence of a product code (In column B)

the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51

So I would need to identify the one in Row 51 in the above example,

WorksOrderNumber
Product Code
DateEntered
Column1

56
80100811
01/08/2012 00:00

[code]......

## List Not Showing In Form

Sep 30, 2008

I have a form that is built based on the number a person enters. For Example - if the person enters the number 2 then 2 ComboBoxes are entered on the form.

The problem that I am having is that the list is not showing when the form is loaded. In the Project Screen all is OK until the final step when the form is Shown.

## Add-in Not Showing In Macro's List

Aug 17, 2009

I have recently discovered the beauty of add-ins, but I've got a small problem :

I saved my add-in workbook as an xla in the appropriate folder and I enabled the add-in in the workbooks, but when I open a new workbook, the sub I had made does not appear in the Macro's list (Alt+F8).

When I go in the VBA environment (Alt+F11), I see my sub in the workbook [Name].xla.xls...

Is there anyway I can make the sub appear in the Macro's list?

## Product Look Up: Look Up A Product Based On The Number And The Qty

Feb 20, 2009

i need to look up a product based on the number and the qty. see the example attached.

On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.

## List Box Showing Empty Cells?

May 1, 2009

I have a list box looking at range IB5:IB100 I only have about 25 names listed. However the list box opens up about 3/4 of the way down with the list of names that are not there. The user then needs to scroll up to see the names.

How can I get the list box to show only the names in the list? or at least start at the top of the list (IB5)

I have the ignore blank button checked.

## Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column

Jul 16, 2006

In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.

## Showing List Of Data From Total Purchases

Jan 29, 2014

I have a basic knowledge of spreadsheets, involving typical business needs (Average, Sum, And, Or, Logical Operators) but after roughly 35 minutes searching for a simple solution I'm stumped.

If I was to calculate the most popular (lets say fruit), I know it would be the MAX function, but what would the formula to show the Name of the most popular fruit and not just the amount of sales?

Name
Total Sales
Most Popular
How many Apple 100

[Code]....

## Pivot Table Placed In Existing Sheet Not Showing Full List Of Values

Sep 16, 2013

I'm trying to place a pivot table in an existing sheet in order to have a list of names next to a P&L. I've got several criteria to filter the names, but when I apply the filters to the pivot table, not all the people show up. The weird thing is that the total at the bottom of the pivot table, which is a simple sum of time in a given month per person, calculates the correct number as if all the employees are there.

When I do the same table in a fresh sheet, in the same file, it shows correctly. It's only when I try to put the table in an existing sheet that it abbreviates the list.

## Multiple Lookup: List Of Part Numbers Based On The Product Part Code

Feb 12, 2009

I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

## 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.

## Vlookup With Multiple Criteria: Creating A Drop Down List Showing Alternative Choices With Sex

Aug 22, 2007

I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:

There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.

Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....

## 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.

## 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...