LUIS FREUND
asked on
Geting the MAX and MIN from a group and placing it on another sheet in Excel
So I have this data that is linked from my access db on my Data Sheet. I wanted to get the MAX and MIN of that group called Supplier 1 Lead Time thru Supplier 4 Lead Time for each part number.
From there I wanted to populate the MAX and MIN as well as the PN, RFQ # and REQD Date from the data sheet to the Timetable Sheet
on rows 7 and 8 as an example. I wanted to this to be automatic since the data from database is always changing.
C--Users-lfreund-Desktop-PO-Order_T.xlsx
From there I wanted to populate the MAX and MIN as well as the PN, RFQ # and REQD Date from the data sheet to the Timetable Sheet
on rows 7 and 8 as an example. I wanted to this to be automatic since the data from database is always changing.
C--Users-lfreund-Desktop-PO-Order_T.xlsx
in TimeTable worksheet, cell E7, try put in an array formula:
drag it down to apply formulas to cells below.
do the same for cell F7, use this formula and apply to cells below of it:
=MAX(IF((Data!A2:A921=Timetable!B7)* (Data!D2:F921<>"")*(Data!D2:F921<>"NO BID")*(Data!D2:F921<>"TBD")*(Data!D2:F921<>"wks"),VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Data!D2:F921,"wks",""),"wk",""),"kws",""))))
drag it down to apply formulas to cells below.
do the same for cell F7, use this formula and apply to cells below of it:
=IF(MIN(IF((Data!A2:A921=Timetable!B7)* (Data!D2:F921<>"")*(Data!D2:F921<>"NO BID")*(Data!D2:F921<>"TBD")*(Data!D2:F921<>"wks"),VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Data!D2:F921,"wks",""),"wk",""),"kws",""))))=E7,0,MIN(IF((Data!A2:A921=Timetable!B7)* (Data!D2:F921<>"")*(Data!D2:F921<>"NO BID")*(Data!D2:F921<>"TBD")*(Data!D2:F921<>"wks"),VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Data!D2:F921,"wks",""),"wk",""),"kws","")))))
C--Users-lfreund-Desktop-PO-Order_b.xlsx
If your Lead Time Values on the Data sheet (columns D to F) were just numeric rather than with suffix for weeks, you could use a pivot table to get the Max Nin values for each Part Number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is amazing!!! Thank you very much! Now to the next step to this worksheet and that has to do with dates.
i think it should be as 12wks instead.