Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run data analysis
I don't know if this is possible or not:
I import data from a manufacturing process. Column A - Date Column B - Product Name I want to have a table that displays the date (column A) when Product Name (Column B) changes. Data Example 11/01/08 8:00am 23LIN 11/01/08 8:30am 23LIN 11/01/08 9:00am 23LIN 11/01/08 9:30am 33LIN 11/01/08 10:00am 33LIN 11/01/08 10:30am 33LIN What I'm looking for is the automatic calculation of a table that would say 11/01/08 8:00am 23lin 11/01/08 9:30am 33lin I've tried everything that I can think of and haven't come up with anything that's fully automated. Am I asking the impossible? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run data analysis
Hi,
Try =MAX(IF(C$2:C$7=B16,A$2:A$7+B$2:B$7,"")) Where the Dates are in A2:A7, the times B2:B7 and the item you want to look up in the range C2:C7. In B16 I entered 23LIN If this helps, please click the Yes button Cheers, Shane Devenshire "Papergal" wrote: I don't know if this is possible or not: I import data from a manufacturing process. Column A - Date Column B - Product Name I want to have a table that displays the date (column A) when Product Name (Column B) changes. Data Example 11/01/08 8:00am 23LIN 11/01/08 8:30am 23LIN 11/01/08 9:00am 23LIN 11/01/08 9:30am 33LIN 11/01/08 10:00am 33LIN 11/01/08 10:30am 33LIN What I'm looking for is the automatic calculation of a table that would say 11/01/08 8:00am 23lin 11/01/08 9:30am 33lin I've tried everything that I can think of and haven't come up with anything that's fully automated. Am I asking the impossible? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run data analysis
It doen's do exactly what I need, but has got me thinking. One note is that
the date and time are in a single cell, but that doesn't really affect anything. Using the max formula, I only get a value when the formula is in the same row as the cell that I'm testing. Another twist is that there are muliple occurances of product name in a list, so in theory this would give only the date of the last run. Thanks for the help, I'll keep playing with it and see if I can come up with something. "Shane Devenshire" wrote: Hi, Try =MAX(IF(C$2:C$7=B16,A$2:A$7+B$2:B$7,"")) Where the Dates are in A2:A7, the times B2:B7 and the item you want to look up in the range C2:C7. In B16 I entered 23LIN If this helps, please click the Yes button Cheers, Shane Devenshire "Papergal" wrote: I don't know if this is possible or not: I import data from a manufacturing process. Column A - Date Column B - Product Name I want to have a table that displays the date (column A) when Product Name (Column B) changes. Data Example 11/01/08 8:00am 23LIN 11/01/08 8:30am 23LIN 11/01/08 9:00am 23LIN 11/01/08 9:30am 33LIN 11/01/08 10:00am 33LIN 11/01/08 10:30am 33LIN What I'm looking for is the automatic calculation of a table that would say 11/01/08 8:00am 23lin 11/01/08 9:30am 33lin I've tried everything that I can think of and haven't come up with anything that's fully automated. Am I asking the impossible? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run data analysis
Hi. Here's one idea.
I'll assume your data in in A1:B7. In C1, give it a heading, say "Change" In C2, place an "X" In C3, use a formula like =IF(B2=B3,"","X") and copy down. Basically, an "X" shows up where the data changes. Then, go to Data | Filter, and display only those rows with an "X" - - - HTH Dana DeLouis Papergal wrote: It doen's do exactly what I need, but has got me thinking. One note is that the date and time are in a single cell, but that doesn't really affect anything. Using the max formula, I only get a value when the formula is in the same row as the cell that I'm testing. Another twist is that there are muliple occurances of product name in a list, so in theory this would give only the date of the last run. Thanks for the help, I'll keep playing with it and see if I can come up with something. "Shane Devenshire" wrote: Hi, Try =MAX(IF(C$2:C$7=B16,A$2:A$7+B$2:B$7,"")) Where the Dates are in A2:A7, the times B2:B7 and the item you want to look up in the range C2:C7. In B16 I entered 23LIN If this helps, please click the Yes button Cheers, Shane Devenshire "Papergal" wrote: I don't know if this is possible or not: I import data from a manufacturing process. Column A - Date Column B - Product Name I want to have a table that displays the date (column A) when Product Name (Column B) changes. Data Example 11/01/08 8:00am 23LIN 11/01/08 8:30am 23LIN 11/01/08 9:00am 23LIN 11/01/08 9:30am 33LIN 11/01/08 10:00am 33LIN 11/01/08 10:30am 33LIN What I'm looking for is the automatic calculation of a table that would say 11/01/08 8:00am 23lin 11/01/08 9:30am 33lin I've tried everything that I can think of and haven't come up with anything that's fully automated. Am I asking the impossible? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Analysis Plus | Charts and Charting in Excel | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) |