Column Header basis Max Values

[chirayu]

Hi Guys,

I have a set of data which has:
- Vendor names in column A
- Occurrence data for reasons of some sort of non compliance in other columns

What I would like to achieve if possible is to find:
- Highest Value for a given Vendor
- 2nd Highest Value for the same Vendor
- 3rd Highest Value for the same Vendor

Basis these Values I would then like to know the column Headers for that column for that specific Vendor.

I know that The LARGE formula can be used to find such Values. But This would only really work if I had only unique numbers. E.g. 2 columns could have the same Value so finding the column Header is difficult.

This is also further complicated by the fact that a given Vendor may not necessarily have 3 reasons. They might have just 1 or 2.

I have attached a sample file for the same & hope you guys can help.

 

[Ramesh]

Hi,

Here you go.. here is the spreadsheet updated with the formula to fetch 1st, 2nd and 3rd highest values.

Hope it helps!!

Thanks,
Ramesh Kumar.P

 

[chirayu]

Hi Ramesh, Thanks for the quick reply but I don't need the actual Values. As stated in the post/file. I need the corresponding column Header for that value.

 

[Ramesh]

Here you go.. I have added a row right below to show the Reason header..

 

[chirayu]

Thanks Ramesh for your quick response, checked the output. It seems it is not outputting correctly.

For example select Vendor A. Comment 3 should be Reason 10 not Reason 5. I believe this is because of the -1 argument in the formula.

Secondly I also noticed that in the instance that Multiple columns have the same value, it disregards other matching values & moves onto the next lowest - this can be seen for Vendor B. Where Comment 3 picks Reason 2 instead of Reason 5 or 6