formula to find large value and pull non-repeatiing value from adjacent column

[rakeshgarg1977]

While working and challenging a formula that a forum expert graciously helped me with, i stuck-up with one combination of data where formula seems not giving the desired results. Overall the formula worked perfect for all other possibility. As it is difficult to explain the situation here, attaching the workbook with the formula and tried to explain my difficult.

Request further help on the same.

Link to old thread helped by Mr. Flame Retired is

https://www.excelforum.com/excel-for...ml#post4601010


Thanks

[JohnTopley]

Try

=IF(C$3:C$20<>"",COUNTIFS($B$3:$B$20,">="&$B3)+ROW()/10^3)

[rakeshgarg1977]

Hello Mr. JohnTopley,
Thanks for ur quick response. My requirement was to get Unique Product list for the products having column C is filled in descending order order of weightage (Column-B). When i applies ur formula, i am getting repeated product name. U can refer the attached sheet highlighted column. To give a try, change any product name in Column A which is having column C filled and weighatge is on higher side to 'N', u will find 'N' is repeated in the list of product in highlighted column.

[rakeshgarg1977]

Can somebody help on the problem please.

[JohnTopley]

In L & M please show manually calculated expected results and for what columns.

[rakeshgarg1977]

I am attaching the workbook with details of requirement in text box along with the required data in 'Yellow' coloured column. Also 'Orange coloured column indicates the existing solution.

Thanks

[FlameRetired]

Withdrawn by FR. Found a better formula.

[FlameRetired]

This formula replaces the original helper formula and solves the N problem. The sample you provided omits Z although it meets requirements. I am assuming that is an oversight. This formula will return Z also.

Please Login or Register  to view this content.

[rakeshgarg1977]

Dear Mr. Flame Retired Sir,

I was little reluctant to send you the problem directly as you have helped in to a greatest extent for the same issue. But during my trial; i faced this problem.
I cannot express my thanks just by writing. Your step-in proactively is really appreciable and your continuous improved versions of solutions to this problem is helping me to come out of the labyrinth .

I will take some trials and revert to you. Primarily it seems it is working.
GRAND THANKS SIR.

[FlameRetired]

You contacted me PM indicating the problem is still not resolved.

I am confused.

Please attach a new upload show exactly what you want the output to be.

Indicate item by item why the output is what it is and item by item why the output my formula returns is not what you want. That way others will see, too and perhaps someone will understand.

Thanks,
Dave

[rakeshgarg1977]

Dear Sir
Thanks for responding.
Still i am facing problem with the solution u suggested. In the file attached in the link below, IF any name and highest weightage are same and together appears before for the equipment column empty, that equipment name and weighatge number will not appear in the list. For examplein the attched sheet, please see cells A15 which is made 'N' & 'B15-16 as '68'. Cell in D15 is made empty and D16 is filled with same weighatge and product name. Result in column K & L ignores the product 'N' and weightage '68' even though in cell A16 & B16 also has Product 'N' & weightage '68' .

Hope i could explained it properly. Please help

Thanks

[FlameRetired]

Try putting this in F3:H18 (the helper columns).

Please Login or Register  to view this content.

Now column I:L returns this:

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
3	A	66			RMG	FALSE	FALSE	9.003	N	68	N	68
4	Z	66				FALSE	FALSE	FALSE	O	68	O	68
5	C	66	RMG	FBD		9.005	9.005	FALSE	W	68	C	66
6	D	61	RMG			11.006	FALSE	FALSE	A	67	E	36
7	E	36		FBD		FALSE	16.007	FALSE	C	66	I	36
8	N	42			RMG	FALSE	FALSE	13.008	D	61
9	G	36			RMG	FALSE	FALSE	16.009	D	61
10	A	67	RMG 50L			6.01	FALSE	FALSE	L	43
11	I	36		FBD		FALSE	16.011	FALSE
12	D	61	RMG 50L			11.012	FALSE	FALSE
13	K	68			RMG	FALSE	FALSE	5.013
14	L	43	RMG 50L			12.014	FALSE	FALSE
15	N	68				FALSE	FALSE	FALSE
16	N	68	RMG 50L	FBD		5.016	5.016	FALSE
17	O	68	RMG 50L	FBD		5.017	5.017	FALSE
18	W	68	RMG 50L			5.018	FALSE	FALSE

Note: D now returns twice in column I. Is this acceptable? Both C6 and C12 are filled both A6 and A12 are D and B6 and B12 have identical values ... 61.

[rakeshgarg1977]

Dear Sir,

D is fulfilling both conditions i.e. C is filled, B is relatively large and and hence has to appear twice. I will check further and provide you the feedback.

THANK YOU SIR.
Cheers

[FlameRetired]

Good deal.

[rakeshgarg1977]

Dear Sir,

Just to explore possibility to use this formula in different scenario I have; is it possible to avoid repeat of D & 61. If it can appear only for once?

Thanks

[FlameRetired]

Yes. Make the helper formula in F3:H18

Please Login or Register  to view this content.

PS Please also take time to update your profile. Members tailor solutions with things like Excel version(s) in mind. Ex. if Excel 2003 was your only version as your profile indicates this formula wouldn't work.

[rakeshgarg1977]

Thank you Flame Retired sir. Now it is working for me. I have taken various trial for my purpose and its working. I used the last formula #16.

[FlameRetired]

Good deal! Thank you for letting me know.