Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
I have a list of machines (MACHINE LIST) and another list of dates (DATE
LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
With no knowledge of your sheet's actual layout, you'll have to interpret this.
A1-A6 = Machine1,Machine2,Machine3, etc.... B1-B6 = the last date the machine was used (we'll come back to this) C1-C100 = machine names D1-D100 = dates the machines in "c" were used In B1, enter this array formula: =MAX(IF($C$1:$C$100=A1,$D$1:$D$100,"")) ....confirmed with CTRL-SHIFT-ENTER. Curly braces { } will appear around your formula and the last date for Machine1 will appear. Now copy that cell down through B6. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "jkiser" wrote: I have a list of machines (MACHINE LIST) and another list of dates (DATE LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
Suppose I have the machin list from Col A starting from row 1 and the dates
in ColB. Cell C1 = Machine 1 In D1 enter the below formula. If the machine is not found it will return "NOT USED" =IF(COUNTIF(A$1:A1$00,D1)0,SUMPRODUCT(MAX(($A$1:$ A$100=D1)*($B$1:$B$100))),"NOT USED") If this post helps click Yes --------------- Jacob Skaria "jkiser" wrote: I have a list of machines (MACHINE LIST) and another list of dates (DATE LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
How can the date list be longer than the machine list?
It would have to be like this: Machine1...date ...................date ...................date Machine2...date ...................date ...................date ...................date If you can fill in the blanks so that you have this: Machine1...date Machine1...date Machine1...date Machine2...date Machine2...date Machine2...date Machine2...date Then it would be fairly easy to get the max date. It's also fairly easy to fill in the blanks if you need to. -- Biff Microsoft Excel MVP "jkiser" wrote in message ... I have a list of machines (MACHINE LIST) and another list of dates (DATE LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
Forgot to mention to format D1 as Date.
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Suppose I have the machin list from Col A starting from row 1 and the dates in ColB. Cell C1 = Machine 1 In D1 enter the below formula. If the machine is not found it will return "NOT USED" =IF(COUNTIF(A$1:A1$00,D1)0,SUMPRODUCT(MAX(($A$1:$ A$100=D1)*($B$1:$B$100))),"NOT USED") If this post helps click Yes --------------- Jacob Skaria "jkiser" wrote: I have a list of machines (MACHINE LIST) and another list of dates (DATE LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
Both your and Jacobs responses worked perfectly! I've used array formulas in
the past but I never really took the time to learn how to use them well. Thanks so much. "JBeaucaire" wrote: With no knowledge of your sheet's actual layout, you'll have to interpret this. A1-A6 = Machine1,Machine2,Machine3, etc.... B1-B6 = the last date the machine was used (we'll come back to this) C1-C100 = machine names D1-D100 = dates the machines in "c" were used In B1, enter this array formula: =MAX(IF($C$1:$C$100=A1,$D$1:$D$100,"")) ...confirmed with CTRL-SHIFT-ENTER. Curly braces { } will appear around your formula and the last date for Machine1 will appear. Now copy that cell down through B6. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "jkiser" wrote: I have a list of machines (MACHINE LIST) and another list of dates (DATE LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max Value of conditional list
I got confused and but your formula worked great when I changed the "D1"
references need to be "C1" and followed your directions. Now I gotta go figure out why it works. Thanks so much for your help!! "Jacob Skaria" wrote: Forgot to mention to format D1 as Date. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Suppose I have the machin list from Col A starting from row 1 and the dates in ColB. Cell C1 = Machine 1 In D1 enter the below formula. If the machine is not found it will return "NOT USED" =IF(COUNTIF(A$1:A1$00,D1)0,SUMPRODUCT(MAX(($A$1:$ A$100=D1)*($B$1:$B$100))),"NOT USED") If this post helps click Yes --------------- Jacob Skaria "jkiser" wrote: I have a list of machines (MACHINE LIST) and another list of dates (DATE LIST). The DATE list has the dates that each machine was used . The DATE list is much longer than the MACHINE list because each machine has been used multiple times on different dates. I want to make a report that shows the last date each machine was used. I could accomplish this by sorting the DATE list and then manually comparing the two lists. But, I'd have to do that every time they were updated. I want to automate the process. Is there a formula that would give me the max date from the DATE list for each entry on the MACHINE list.....something like a "MAXIF" Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional list | Excel Worksheet Functions | |||
conditional formatting from a list | Excel Worksheet Functions | |||
Conditional List | Excel Worksheet Functions | |||
Conditional List | Excel Discussion (Misc queries) | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) |