Formula to find large value and pull non repeatiing value from adjacent column

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Another way array entered then copied down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=INDEX($A$3:$A$16,MATCH(LARGE(IF(FREQUENCY(IF($C$3:$C$16<>"",$B$3:$B$16),$B$3:$B$16),$B$3:$B$16),ROWS($3:3)),IF($C$3:$C$16<>"",$B$3:$B$16),0))


Edit: Something I failed to mention above. Your in-workbook note states

Data lenght can go to 1000's also

If you are not aware of it array formulas are resource heavy.

If your expected returns are also in the 1000's this formula might be slow to calculate. I have tried to find a non-array formula solution, but I do not think that I can do it without one or more helper columns.

Are you open to using helper columns?

I went ahead and did another version that uses a helper column F. None of the formulas are array entered.

The helper column can be hidden or placed out of sight to the extreme right ... even on another sheet.

In the attached the helper column formula is

Formula:

Please Login or Register  to view this content.

The final formula in column D can then be

Formula:

Please Login or Register  to view this content.

Your profile says Excel 2003. You uploaded an *.xlsx file. The last formula will need to be modified if this has to work in 2003. Please take the time to update your profile. Members can then tailor solutions with that in mind.

Re: The changed reference range. Try array entering this in G3 of your latest upload.

Formula:

Please Login or Register  to view this content.

but i have concern, is in case data in A column is updated like in B10, if i change value from 'B' to say 'Z'. Results are not auto updated.

As I understand it that is what it is supposed to happen. Of the column B values (no duplicates) corresponding with non-blanks in column C the first of the second largest resulting numbers (now 67) is still in B4 and therefore should return A4 or "B". Why should "Z" in A10 change anything? 67 remains in B10 and is a duplicate of the B4 number and therefore not considered.

What am I missing?

Edit I interpreted something wrongly on the duplicates part. I'll need to sleep on this one.

OK.

You did not mention before the blank cells after the data in columns A, B and C. That complicates the formula.

So this time I made use dynamic named ranges for those columns, a dynamic named formula and a helper column.

The dynamic named ranges are:

Product:

Formula:

Please Login or Register  to view this content.

Weightage:

Formula:

Please Login or Register  to view this content.

Equipment_1:

Formula:

Please Login or Register  to view this content.

And an indexing named formula

Indx:

Formula:

Please Login or Register  to view this content.

Then this array entered formula in E3 to serve as a helper column

Formula:

Please Login or Register  to view this content.

and the final formula in D3 array entered

Formula:

Please Login or Register  to view this content.

The idea behind the "zzzzzzz" is to supply a sufficiently large text value to do an approximate match on the column and force a match on the last row. The thought is that there is not likely to be a string that high alphabetically. It is good for text ranges.

The same idea is behind the 1E+306. Supply a sufficiently large number to coerce an approximate match on the last row in a numeric range.

Please feel free to come back if there are further difficulties.

Edit: "Atorva calcium 25" would be treated as text. Use the "zzzzzzzzzzz" string on such a match.

Here is another way. Two helper columns non-array, a final simplified array formula and the same dynamic named ranges and named formula.

In F3 and filled down

Formula:

Please Login or Register  to view this content.

In E3 and filled down

Formula:

Please Login or Register  to view this content.

and array entered in D3 and filled down.

Formula:

Please Login or Register  to view this content.

You hadn't mentioned addition of extra columns before.

This complicates things a little.

The additional helper columns can be set far to the right to make room for additional 'Equipment' columns and output columns.

Is there a limit to how many columns there will be?

You will need to redefine the now more complex range references in the helper and final formulas as they are added.

In the attached I copied the 'Equipment' headers to cells J1, L1 and N1.

The helper formulas are now ... in J3 and filled down

Formula:

Please Login or Register  to view this content.

In I3 and filled down

Formula:

Please Login or Register  to view this content.

Select both of those helper column ranges, copy and paste into cells K1 and M1.

Then array enter this new final formula in F3. Copy down and across column H. The range references will need to be adjusted as new columns
are added to this as well.

Formula:

Please Login or Register  to view this content.

If there is a simpler way to do this it hasn't occurred to me yet.

By the way I failed to mention in my post #17 above that this setup is designed to adapt to added 'Equipment' columns as they occur.

If the number of 'Equipment' columns has a know limit then I think I can come up with something simpler.

Originally Posted by rakeshgarg1977

Hello Sir,
Thanks again.
Maximum up to 40 columns it may go. Now the formulas are gone beyond my RAM, I am hang now.
Appreciate your patience on my last minute additions and thanks for ur continued support.

Also the in-workbook notes indicated 1000's of rows. Yikes!

I am not surprised this exceeds limits, and I can not think of a formula work around for this.

I am not VBA proficient, but your solution may require it.

Let me see if I can get us some help.

I can almost make multiple Pivot tables work.

However when there are ties in 'Weightage' I am unable to get 'Products' to sort the way you want them to.

Here's another approach. I managed to do all formulas non-array. I have not done a painstaking check. At least the results are the same. Check it out.

In F3 filled across column H and down this helper formula.

Formula:

Please Login or Register  to view this content.

Then in I3 filled across column K and down this formula.

Formula:

Please Login or Register  to view this content.

You are very welcome. Yes my solution requires a helper column for each column of output.

I was disappointed that I could only almost come up with a pivot table solution. That would have been ideal from a resource use standpoint.