VBA Using LARGE to find Nth Number and return .ROW

[Lres81715]

Hello all.
I have a project Dashboard that needs to refresh the Daily Numbers in order of Order Volume. Then, after I supply the number, I want to application.worksheetfunction.match/index the client associated with that Nth Large number. Only match/index isn't working for me as there are often duplicate results. I need to find the .row that the application.worksheetfunction.large finds.

Simplified Explanation: Daily Order Count is the column I'm doing LARGE on. Using VBA, I want to return both the LARGE (nTH) and the associate Client ID next to it.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Daily Order Count[/TD]
[TD]Fee[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD]74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1129[/TD]
[TD]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]716[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]906[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]389[/TD]
[TD]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]821[/TD]
[TD]165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1085[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1963[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1058[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1278[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I next use Application.WorksheetFunction.Match/Index on the LARGE and the Results are not as promising on duplicate LARGE entries

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Top Clients in order of Volume[/TD]
[TD]Daily Order Count[/TD]
[TD]Fee[/TD]
[/TR]
[TR]
[TD]821[/TD]
[TD]165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1129[/TD]
[TD]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]389[/TD]
[TD]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD]74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1963[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1278[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1085[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]716[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]716[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]716[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The duplicated LARGE(Nth) of 15 is causing the Client ID to return the same row the first 15 is found. Yadda Yadda its not good.

What I have so far that has worked up until this point is this:

    lrg = 1
    For iRow = 2 To 30
        DailyLarge = Application.WorksheetFunction.Large(CurYearSum.Range("F1:F900"), lrg)
        [U][COLOR=#ff0000]RowNo = Application.WorksheetFunction.Match(DailyLarge, CurYearSum.Range("F1:F900").Offset(0, cmn), 0)[/COLOR][/U]
    [Loop process goes here]
    ....
    lrg = lrg + 1
    Next iRow

The part in red is where the RowNo is returning the incorrect Row Number on results that are duplicated.
There is an Excell ARRAY Formula that can get me the results I want but its very clunky and doesn't translate into VBA very well.

=INDEX(Sheet2!$A$1:$A$900,SMALL(IF(Sheet2!$F$1:$F$900=$B2,ROW(Sheet2!$F$1:$F$900)),COUNTIF($B$2:B2,B2)),1)

Any help to steer me in the right direction of just finding out the unique RowNo for duplicate match results would be greatly appreciated.

 

[RickXL]

Hi,

Can you not just sort the columns by Daily Order Count then Client ID?


Regards,

 

[Lres81715]

I ended up doing just this for a project in the past, I was hoping there was a less cumbersome way as the data in question doesn't have a solid Selection.Autofilter method. I guess I'll improvise with a multi step process before hand.

AKA
The data I have is a lot like this

Blah Blah Blah
Yadda Yadda Yadda

Blah Blah Yadda
Yadda Yadda Blah Tada
gibberish gibberish etc

Blah Yadda Yadda
Etc Etc Etc
Etc Etc Etc

I'l figure it out. Thanks

 

[RickXL]

I have tried using the data you included (using columns A:C) and a straight SORT works:

Sub sort()
    ActiveSheet.UsedRange.sort key1:=Range("B1"), order1:=xlDescending, Header:=xlYes
End Sub

Presumably, you would need to change references from B to F but otherwise it should work.


Regards,

 

[Lres81715]

RickXL,
Thanks for the reply. The data sheet I'm getting all the information from is a little more complex than using the simple usedrange.sort. But it partially worked.
I ended up creating a Temp sheet, copying just the data needed from each section minus the repetitive headers, (used a cycle-thru macro of finding the blank space and counting down 3 and then end cell of each section. So instead of 30-60 client subsections from the report, it's one fluid section on the TEMP sheet.
Then using ActiveSheet.sort key1:=Range("B1"), order1:=xlDescending, Header:=xlYes Worked.
Ended up saving a few steps because the results could be easily transferred to an email template as well.

 

[RickXL]

I am glad you got it sorted.

For future reference, you might want to think how difficult it is to provide a working solution if you do not fully describe the problem. It is surprising how often a different take on the problem produces a solution rather than pursuing the original path.


Regards,

 

[Lres81715]

LOL I hear you. In an ironic twist, I submitted a detailed explanation of a problem I was having earlier last year and was told it was too complex. Even told me that I should simplify my explanation of said problem so they could help. Can't win apparently.

For the record, your advice steered me in the right direction and ended up using the sort method (through a round about way). So thanks.

 

[RickXL]

That is how it is, I'm afraid.

I think the best idea is to convey what you are trying to do in words so that someone is able to think of an "out of the box" solution but also to include any complicating factors you have added like skipping rows, merging cells, spreading the data over multiple worksheets and workbooks etc.

The problem for a programmer is that a relational database approach is usually the first assumption. If you have done something different, and Excel lets you make it very different, then those differences need to be highlighted.

In practice, most computer solutions tend to iterate between: "I want this", "Well, I can give you that", "OK, but can you change this" etc etc.

The other issue is that the "wrong" person may see your question by which I mean, if the question looks like a worksheet formula problem and it turns out to be a database connection issue, which you didn't mention, then you might be talking to the wrong expert.


Regards,