Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting the Occurances and find the Latest Date
Hello to All,
I have a large spreadsheet that I need to condense down. Each record on the spreadsheet is an item and in the first column there is an item number and in Column N there is a the date the item was ordered. The items repeat for every time they were ordered. So if an item was ordered 500 times there will be 500 records for that item with dates, and the dates could repeat if the item was ordered multiple times in the same day. I've looked around and haven't quite found what I need. I am hoping there is a way to automate this as the people who use this aren't so computer savvy. I hope I'm being clear, but I'll say it again. Column A has the item number and they can repeat, I need to count how many times an item is ordered (for each item on the spreadsheet, there could be hundreds of different items) and then take the latest date that the item was ordered. Thanks in advance for any advice. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting the Occurances and find the Latest Date
Forgot to mention that once I have the total number of occurances and the
latest date I need to place the number next to the date column and remove all the other records - they are no longer needed. "yoshimarine" wrote: Hello to All, I have a large spreadsheet that I need to condense down. Each record on the spreadsheet is an item and in the first column there is an item number and in Column N there is a the date the item was ordered. The items repeat for every time they were ordered. So if an item was ordered 500 times there will be 500 records for that item with dates, and the dates could repeat if the item was ordered multiple times in the same day. I've looked around and haven't quite found what I need. I am hoping there is a way to automate this as the people who use this aren't so computer savvy. I hope I'm being clear, but I'll say it again. Column A has the item number and they can repeat, I need to count how many times an item is ordered (for each item on the spreadsheet, there could be hundreds of different items) and then take the latest date that the item was ordered. Thanks in advance for any advice. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting the Occurances and find the Latest Date
first use Data/AutoFilter to extract a unique list of items
lest say the list is in column X then in the cell to the right, in Y add this formula =COUNTIF(N:N,X1) and replicate down we can code it, but a worksheet method works easily here. "yoshimarine" wrote in message ... Hello to All, I have a large spreadsheet that I need to condense down. Each record on the spreadsheet is an item and in the first column there is an item number and in Column N there is a the date the item was ordered. The items repeat for every time they were ordered. So if an item was ordered 500 times there will be 500 records for that item with dates, and the dates could repeat if the item was ordered multiple times in the same day. I've looked around and haven't quite found what I need. I am hoping there is a way to automate this as the people who use this aren't so computer savvy. I hope I'm being clear, but I'll say it again. Column A has the item number and they can repeat, I need to count how many times an item is ordered (for each item on the spreadsheet, there could be hundreds of different items) and then take the latest date that the item was ordered. Thanks in advance for any advice. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting the Occurances and find the Latest Date
On May 21, 10:51*am, yoshimarine
wrote: Hello to All, I have a large spreadsheet that I need to condense down. *Each record on the spreadsheet is an item and in the first column there is an item number and in Column N there is a the date the item was ordered. *The items repeat for every time they were ordered. *So if an item was ordered 500 times there will be 500 records for that item with dates, and the dates could repeat if the item was ordered multiple times in the same day. *I've looked around and haven't quite found what I need. *I am hoping there is a way to automate this as the people who use this aren't so computer savvy. I hope I'm being clear, but I'll say it again. *Column A has the item number and they can repeat, I need to count how many times an item is ordered (for each item on the spreadsheet, there could be hundreds of different items) and then take the latest date that the item was ordered. * Thanks in advance for any advice. Yoshimarine, This is assuming that your data starts on row 4 and even though the formulas listed contain a small range, simply modify the formulas as needed to fit your data set. A2, B2, and C2 contain the following data and/or formulas, respectively: item number (this is the desired item number to "lookup," manually enter this), =COUNTIF(A4:A9,A2), and =IF(A4:A9=A2,MAX(N4:N9),"") - note that the "IF" formula is an array formula, which is commissioned with Ctrl + Shift + Enter, pressed simultaneously. Best, Matthew Herbert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting the Occurances and find the Latest Date
items in A
dates in N data / autofiltered list of items in X In Y, to count items =COUNTIF(A1:A500:,X1) in Z to get max date, this ARRAY Formula: =MAX((A1:A500=X1)*(N1:N500)) for the max date, again formula so in Z =MAX((N1:N500=X1)*(E)) "Patrick Molloy" wrote in message ... first use Data/AutoFilter to extract a unique list of items lest say the list is in column X then in the cell to the right, in Y add this formula =COUNTIF(N:N,X1) and replicate down we can code it, but a worksheet method works easily here. "yoshimarine" wrote in message ... Hello to All, I have a large spreadsheet that I need to condense down. Each record on the spreadsheet is an item and in the first column there is an item number and in Column N there is a the date the item was ordered. The items repeat for every time they were ordered. So if an item was ordered 500 times there will be 500 records for that item with dates, and the dates could repeat if the item was ordered multiple times in the same day. I've looked around and haven't quite found what I need. I am hoping there is a way to automate this as the people who use this aren't so computer savvy. I hope I'm being clear, but I'll say it again. Column A has the item number and they can repeat, I need to count how many times an item is ordered (for each item on the spreadsheet, there could be hundreds of different items) and then take the latest date that the item was ordered. Thanks in advance for any advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting latest date in two columns | Excel Worksheet Functions | |||
Sumproduct or Countif - Counting Occurances within a Date Range | Excel Worksheet Functions | |||
Normaliziing date and counting # of of occurances | Excel Discussion (Misc queries) | |||
Counting date occurances | Excel Worksheet Functions | |||
Counting Date Occurances | Excel Worksheet Functions |