Duplicate values, sum, rank, value, and ordering

[hartjonathand]

Hi guys,

Here's what I need help with.

I have 5 columns,

in column 1 I need something that looks like (but obviously works)
=IF(ISBLANK(U22),"",IF(COUNTIF($S$22:$S$408,S22)=1,VALUE(RANK.AVG(U22,$U$22:$U$87)),VALUE(RANK.AVG(SUMIF($S$22:$S$408,S22=$S$23:$S$408,$U$22:$U$408),$U$22:$U$408,0))))

To simplify, U would be column 5 and is where I have all the numerical values. In the column I am entering the command, I need a command that will rank values based on their absolute value size and if the value (e.g. a person) is listed twice, it sums that value and then continues to rank it based on the vector in column 5. Column 2 can have the persons name for ease of helping out, the other columns aren't used for this formula.

To summarize, 1 needs the formula, 2 has the persons name that I need to sum if person exists more than 1x and then have them ranked. The difficulty also is I need to have all persons ranked based on the totals from duplicates as well. If this isn't clear I can paste more. The value command is essential as I am pulling from multiple sheets and dumps.

Thanks

 

[Eric W]

Welcome to the forum.

You're probably better off adding a helper column to get the total for each name, like this:

	A	B	C	D	E
Name	Value	Total	Ranking
Joe
Cal
Cal
Al
Al
Al
Al
Dave

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]41[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(D2="","",RANK.AVG(D2,$D$2:$D$9))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(COUNTIF($A$2:$A2,A2)=1,SUM(IF($A$2:$A$9=A2,$B$2:$B$9+0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Put the formulas in D2:E2, adjust for your ranges, and copy down the column. In order to incorporate your statement that some numbers might be stored as text, I used a SUM(array+0) instead of a SUMIF. The +0 serves the same purpose as the VALUE function. It doesn't show in the example, but I formatted B4 as text.

I tried to create an array formula that wouldn't require the helper column, but it appears the RANK.AVG function doesn't work with arrays. I might play around a little more, but that might be as good as it gets.

 

[hartjonathand]

Eric,

Thank you a bunch for helping out on this. Part of the issue I have is I don't want to add an additional helper column if I can avoid it as the sheet I'm working in requires a particular format and is already pretty long column wise. Is it possible to do everything in one cell?

Thanks again for your help. Btw, I always ask other excel users. Do you have a favorite book for more advanced excel stuff?

 

[Eric W]

Well, you pretty much have to choose how you want to compromise. Here's a non-helper column version:

	A	B	C	D	E	F
Name	Value	Total	Ranking	Non-helper ranking
Joe
Cal
Cal
Al
Al
Al
Al
Dave

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]41[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(D2="","",RANK.AVG(D2,$D$2:$D$9))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(COUNTIF($A$2:$A2,A2)=1,SUM(IF($A$2:$A$9=A2,$B$2:$B$9+0)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=IF(COUNTIF($A$2:$A2,A2)>1,"",SUM(IF((SUMIF($A$2:$A$9,A2,$B$2:$B$9)<=SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9))*(MATCH($A$2:$A$9,$A$2:$A$9,0)=ROW($A$2:$A$9)-ROW($A$2)+1),1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Columns D and E are the same as before, column F has a non-helper version. However, it has 2 drawbacks. First, it does not handle numbers formatted as text. The trick I used in the first version doesn't work here. Second, it handles ties differently. Maybe someone cleverer could figure something out, but I'm not sure what else to try. The final arrow in my quiver would probably to build a UDF to do this in VBA. If that's of interest, let me know, and how you want to handle ties.

As far as books go, I don't have any particular recommendations. I've always been more of a "get my fingers dirty" kind of guy. Most of my Excel expertise comes from experimenting and digging though the help screens. I have learned a lot at this forum just looking at how others solve particular problems, which I then apply to similar problems. If I have a particular problem I can't figure out, I'll do an internet search. I like puzzles and I view solving problems like this as a puzzle. Other people on this forum have book recommendations that you can search for.

Let me know how this works for you.

 

[hartjonathand]

Eric,

Thank you for your help. I think this gets me far enough to do what I need it to.

 

[Eric W]

Glad to help. Come back if you have any other questions.