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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com