Array Formula

[Max Alejandro Molina]

Hello excel Ninjas,

I want to be able to drag down the array formula and get the biggest date (if dates next to each other are the same I want excel to get the one to the very right side) from the biggest purchase order number related with distribution center and item info from the columns: A and B, I have included an example I did manuallyof what i want this to end up looking like.

Thank you all so much!

 

[Luke M]

See attached. Solution uses 1 helper column which has an array, other 3 columns use regular formulas.

 

[Max Alejandro Molina]

Luke,

Great solution,also I want excel the choose the date that it is located to the very right from the dates column, in the scenario that there are 2 equal dates next to each other please see attachment.

 

[Luke M]

Ah. Change formula in E4 to be:
=MID(LOOKUP(D4,INDEX($P$4:$T$122,MATCH(C4,$I$4:$I$122,0),),$P$3:$T$3),FIND(".",LOOKUP(D4,INDEX($P$4:$T$122,MATCH(C4,$I$4:$I$122,0),),$P$3:$T$3)),999)

Copy down as needed

 

[Max Alejandro Molina]

Thanks Luke! im going to bother you just one more time; the text in red from cell G5 should say "Colocar PO" i dont know where i messed up, do you see the error?

 

[Luke M]

".Entrega" is 8 characters long, you are only comparing it to first 7.
Change first part of formula to be:
=IF(LEFT(F5,8)=".Entrega",...

 

[Max Alejandro Molina]

Thanks Luke!

 

[Max Alejandro Molina]

Luke,

The whole example is for just one supplier, i have to apply the formula to other 15 supplier which are in different sheets within a workbook:



Of course the formula would be so huge, a solution I was given was to 6 columns for each supplier out of the 15:



Is there anyway in way to avoid 90 cells with formulas and only have 1 formula in a cell and drag it down??

 

[Luke M]

Which information do you want? Your last example had several different pieces of information being returned. I don't understand how this could be reduced to a single formula, unless some of the columns were not really necessary.

 

[Max Alejandro Molina]

Which information do you want? Your last example had several different pieces of information being returned. I don't understand how this could be reduced to a single formula, unless some of the columns were not really necessary.

Those 4 columns of course will not be mixed up to create a single formula because the type of info being returned is totally different, however my question is: imagine I had in column A more items that would not appear in range M3:R3, but rather they appear in other worksheet with the same info structure as K3:W122 (in my real case i have 15 worksheets with different suppliers and same clomun structure as K3:W122) so having stated this I wanna know if it is possible to have the formulas in D4,E4,F4,G4,H4,I4, to look up the info in all the 15 different worksheets and just drag down? Did I make myself clear?

 

[Luke M]

Yes, that is clear. Answer would be no, not without having a long formula that basically repeats itself for each sheet. This is why it's good to get all your data on a single sheet.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

If you want help creating a summary sheet, perhaps one of the examples here would help:
http://www.rondebruin.nl/win/section3.htm

 

[Max Alejandro Molina]

Thanks for the links and for the help!

 

[Max Alejandro Molina]

Luke, I found another issue I could not find the solution to :S, in the scenario from the file attached; excel takes the maximum PO number which i though it was fine however this would not always apply. I need to tell excel to get the maximum 2 orders and then compared them to each other and if one order has the second or first order has the 5 dates cells filled then excel takes the date from the other one that has not all the cell dates filled and both maximum PO number have not lack the 5 date cells filled then i want excel to take the one that has the lowest date, can this be done? Please see my example in red