Data Analysis using Index Match or Pivot Tables

[rbhandair]

Hi,

I have a spreadsheet where there is information about the purchased items in a plant with the following data headings. See attachment. data.xlsx



Job # Promised Date Manufacturer Name ASSEMBLY



For each assembly, I need to know the manufacturer name of the item with the most late promised date. Any ideas as to how this can be best accomplished. I have tried pivot tables, V look-Up and index match but hasn't helped

Thanks

[ChemistB]

By "Assembly", I assume you mean "Supplier SO"? I see multiple Manufacturer names for a single assembly. How did you want to handle that?

[Arty_1]

Maybe this is what you're looking for?

[rbhandair]

Hello Chemist- Thanks for the reply. Yes there are multiple manufacturers for the same assembly but I want to return the manufacturer with the most late promised date. Is that possible to do?

I got very close using the following formula but still not quite right as the same promised date can be for different assemblies and manufacturers:

=INDEX($D$2:$D$20,MATCH(LARGE(($B$2:$B$20)*($E$2:$E$20 =E2),1),$B$2:$B$20,0))

Thanks Arty but the pivot table doesn't help because I only want to see the longest or most late promised dates and manufacturer for an assembly. There may be multiple ordered items under each assemmbly but I am only interested in seeing which ones are delayed. I hope I have described fully what I want.

[rbhandair]

Yes, Supplier SO means Assembly sorry for the confusion. This is my first post ever

[rbhandair]

Sorry the formula is as below. The row and column references are alll messed up in my earlier post

=INDEX($D$2:$D$20,MATCH(LARGE(($B$2:$B$20)*($E$2:$E$20 =E2),1),$B$2:$B$20,0))

Remember the above is an array formula and need to be entered using ctrl+shft+Entr

[ChemistB]

Okay, see spreadsheet, attached

First I copied E to G and used "Remove Duplicates" to make a unique list.
Then in H2 is this Arrayed Formula

=INDEX($B$2:$B$218, MATCH(2, 1/($E$2:$E$218=G2)))

I assume that promise dates for a specific assembly will increase as you go down the page.
Does this work for you?

[rbhandair]

Yes Perfectly. Your are a genius!! I will add to your reputation.

[rbhandair]

It works but I can't understand the logic behind it. Looks like you are looking up for a match of 2 in array of 1 divided by the range of cells matching the range that matches the assembly). I am just curious how you made this work. I could never have figured this out after spending years of time. I am glad I posted it.!!

[rbhandair]

Hello Chemist- Actually we started celebrating too early. Your formula works when we entered it initially but after we updated the date in the "Original Promised Delivery" date field, then it fails. In case of assembly "77634-1600-000", (See attached file)error.jpg, the manufacturer should have changed to McMaster Carr because it now has the most late delivery date of 2014-05-16 (I have highlighted it red) but your formula is still calculating it as "Canadian Shunt" which is incorrect.

[ChemistB]

I cannot see your images. Can you upload an Excel file? Did you modify the ranges in the formulas? You might want to make Dynamic named ranges.

How it works:

=INDEX($B$2:$B$218, MATCH(2, 1/($E$2:$E$218=G2)))

MATCH(2, 1/($E$2:$E$218=G2)) is, as you pointed out, looking for a two. The array $E$2:$E$218=G2 will either return false (0) or true(1) which when inverted gives either a 1 or an error. Since we are not looking for an exact match (no final argument of 0), when it doesn't find a 2, it goes back to the last value it found less than 2 which would be the last entry with that assembly.

[rbhandair]

Hi Chemist- No I did not change the range. Please see the atatched file Thankserror.jpg

[rbhandair]

Actually when I read again what you wrote in your last post, It seems like this formula will work only when the promised dates are sorted prior to applying the formula. Since the most late delivery date ( or the highest value) would be the last one it finds with that assembly.

After I sorted by the promised date (lowest to highest), then your formula works but when the dates are not sorted, then it displays incorrectly beacuse the most late item is not necesarily the last one. Is there a solution which will work even dates are not sorted? I can do it through VBA but I am looking for a simpler solution.

Thanks

See attached

[ChemistB]

Yes, my solution only works if the promise dates for a specific assembly go down the page in chronological order. It's back to the drawing board.

[rbhandair]

I finally found a solution although seems very complex:

=INDEX($D$2:$D$1635,MATCH(1,(($B$2:$B$1635 = LARGE(($E$2:$E$1635=E2)*($B$2:$B$1635),1))*($E$2:$E$1635=E2)),0),1)

I am sure all you excel junkies out there can definately find a better solution than mine. I should add to my reputation as well