Returning top results based on critera

[ProfitMajin]

Hi All,

Firstly, apologies if this is posted elsewhere, I tried searching for a similar issue but couldn't find any similar threads.

The Situation/Project

I've been working on a project that allows a member of staff to lookup a information on a client based on our unique ID for them, this allows them to lookup information such as the client's area which I've used Index Match and other similar formula for, which is all working correctly. In addition to that I'm also using a formula to draw the top results based on the area which is also working correctly (as far as I can tell), but as it needs to be an array and look through roughly 1,300 records (and there are 9 of these array formulas that update at the same time), it takes a while to update, generally 10 seconds or so.

It's not a massive delay, but it is causing a disconnect where this particular worksheet needs to be viewed separately so as to not cause the delay anywhere else when looking up the unique ID, in an ideal situation I'd have the various worksheets all update based on the client ID at the same time. The Spreadsheet will be used by staff away from the office and so I have protected the workbook and avoided using VBA to minimise any problems and/or a user not enabling macros and complaining that it doesn't work...

The Formula:

I found this online, I understand some parts of it, but not all of it, and as such could well be causing the delay by using a bloated formula:

=IFERROR(INDEX(PInfo!$A:$A,MATCH(1,INDEX((PInfo!$Z:$Z=LARGE(IF(ISNUMBER(SEARCH("*"&$B$11&"*",PInfo!$R:$R)),PInfo!$Z:$Z),ROWS(Q$27:Q27)))*(COUNTIF(Q$27:Q27,$B$11)=0),),0)),"")

The parts I understand - We are getting back the first column (A) from PInfo, after it has found the top result from the Z column, which it determines by using the LARGE formula with a nested IF function to see if that particular row's R column contains the area information stored in cell B11.

The parts I don't understand - The formula seems to lookup the information in the opposite order, in that it finds cells with the area information, and then compares the value in column Z to determine which is highest, and this is why the array is required. There are also the 'ROWS(Q$27:Q27)' and the '(COUNTIF(Q$27:Q27,$B$11)' parts which use cell Q27 even though it is an empty cell, and I can't understand why these parts are required.

Any help anyone can offer would be greatly appreciated, I've not used the LARGE formula before and could well be misusing it here as I found it online. The aim is to find the top 3 results (using PInfo column Z for this criteria) based on the area (PInfo column R), so if a row would have been number 1 but it isn't in the given area, it should be ignored. I do have the option of formatting/preparing the PInfo sheet if additional criteria/columns are required.

Thank you,

M

 

[baitmaster]

Easy way to test if its that formula that's killing your workbook, simply delete the formula - still got the problem?

What's the criteria for "top 3"? VBA will perform the quick analysis for you, and won't add complex formula overhead to your file - 10 seconds is unacceptable and suggests better options are available

It's easy to force VBA to be active. Simply have a single worksheet that says in BIG letters "You need to enable macros", and when saving have VBA set this sheet to visible and all others to hidden. When the file is opened, it sets this to hidden and all others to visible. No macros = worksheets don't change and warning shows

I'd also consider storing data in a database which will get around version control etc. with multiple users at multiple sites. Its more complex, but allows you to hold all data centrally in an Access file, and have remote users view / amend the data via Excel. I fully understand why you don't want to do this though... don't if you're at all unsure, but be aware it has much potential if you want to get some advanced functionality going

 

[ProfitMajin]

Easy way to test if its that formula that's killing your workbook, simply delete the formula - still got the problem?

What's the criteria for "top 3"? VBA will perform the quick analysis for you, and won't add complex formula overhead to your file - 10 seconds is unacceptable and suggests better options are available

It's easy to force VBA to be active. Simply have a single worksheet that says in BIG letters "You need to enable macros", and when saving have VBA set this sheet to visible and all others to hidden. When the file is opened, it sets this to hidden and all others to visible. No macros = worksheets don't change and warning shows

I'd also consider storing data in a database which will get around version control etc. with multiple users at multiple sites. Its more complex, but allows you to hold all data centrally in an Access file, and have remote users view / amend the data via Excel. I fully understand why you don't want to do this though... don't if you're at all unsure, but be aware it has much potential if you want to get some advanced functionality going

Hi Baitmaster,

Thank you for replying! The criteria for the Top 3 is simply a value that I've already calculated in PInfo's Z column, so whichever 3 rows have the highest value in that column, but where that row also has a particular area in the R column, Manchester for example.

Quick Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column R[/TD]
[TD]Column Z[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Manchester[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Manchester[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Brighton[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Client 4[/TD]
[TD]Manchester[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

The aim would be to have Client 1, 2, and 4 returned from the above as they are in Manchester, but to ignore Client 3 as it's in Brighton. I would also want to return them in the following order:

Client 2 150
Client 1 100
Client 4 50

Hope this helps, also, the delay is definitely caused only from the array formula as it only started after adding them, and as you've suggested does indeed stop if they are removed.

There is an input field where the user enters the unique ID for a client, and the formulas all work based on that field, and 3 sections which detail the client's area. Each section has a top 3 lookup which totals 9 array formulas, which have to look through 1,300 records (each). This of course delays the loading of the worksheet every time the input field is modified, which also means I can't link the input fields together as the user would get the delay when using any of the worksheets.

I like the suggestion of being able to hide away the VBA enabler, the main concern is that the end users are sales staff, and any delays/broken components will immediately end any credibility for the project, so I've kept everything to formulas which has worked for most elements except this top 3 lookup. I fully appreciate that a database would be a far superior solution and would like to move to a MySQL Web interface later down the line, but I'm putting this out for MVP in the first instance so that if it goes down well I'll have some clout to change the format for some of the data sources to make them more compatible for a setup like that.

Thank you for your help so far!

M

 

[baitmaster]

apologies for the delay, been a little busy recently

the following UDF will return the xth highest value for a specific location and works comfortably for multiple formulas looking at 1300+ data items

Option Explicit

Function largest(rngPlace As Range, rngClients As Range, rngLocations As Range, rngValues As Range, intX As Integer) As String

Dim arrValues(), arrClients(), x As Integer, i As Integer
Dim cl As Range

For i = 1 To rngLocations.Cells.Count
    If rngLocations.Cells(i, 1) = rngPlace Then
        x = x + 1
        
        ReDim Preserve arrValues(1 To x): arrValues(x) = rngValues.Cells(i, 1)
        'ReDim Preserve arrClients(1 To x): arrClients(x) = rngClients.Cells(i, 1)
        
    End If
Next i

largest = WorksheetFunction.Large(arrValues, intX)
End Function

the function is incomplete as I'm unclear on how you want results presented - do you need to identify the client? Can this be presented within the same cell as the value? If in the same cell we can add a bit to this code, if not then you need a separate formula to pull the client and we can remove the reference to client in this code

As its a UDF you can use it as a normal Excel formula, in the format "=largest( [single location cell], [client list (not yet used)], [location list], [values list], [rank cell or value e.g. 1])"

 

[ProfitMajin]

Hi Baitmaster,

Thank you for getting back to me! I'm looking to have the results in separate cells, similar to the following:

Top 3:
Result 1
Result 2
Result 3

I was hoping to avoid VBA as this opens up the possibility of it being disabled on certain end points, would a UDF have the same trouble? And would it also have a delay as with the array formula?

I don't fully understand the lines in the snippet above, but I can kind of follow the logic based on your tags where it finds entries that have the same location then compares the values to see which is highest. Would this be possible with a formula too or would it only be possible using an array formula as before?

Thank you again for you help!

M

 

[baitmaster]

VBA allows you to create SUBS which are pieces of code that run on demand and perform a fixed sequence of actions, or FUNCTIONS which work in a similar way but can return a result to wherever the code was called from. Functions can be called from Subs or other Functions, but can also be called from cells just like a standard Excel formula. Such functions are called User-Defined Functions or UDFs

In terms of whether they can be disabled etc., Subs and Functions are simply slightly different variants of the same thing, VBA scripts, and hence they do rely on macros being enabled in order to work. There are things you can do to your file to force the User to enable macros

I mentioned that this particular UDF works comfortably for multiple formulas looking at 1300+ data items. I've tested it in a file containing 42 of these formulas, looking at 1335 data items, and believe that on this occasion it works more efficiently than your array formula. You're right to question it though, UDFs can quickly become inefficient just like array formulas can.

OK I've broken it down to a more simple array formula, but this still doesn't identify the client. With client in column A, Location in B and values in C, rows 1 to 1335; then with results table starting in E1, locations listed across the top and rank down the side, use in F2: =LARGE(IF(--($B$1:$B$1335=F$1),$C$1:$C$1335,0),$E2) entered as array formula shift + ctrl + enter, and drag across / down

To get the client you'd need to identify the row in the table that has these values for this location. Issue there is that if 2 or more clients have the same value you will only easily identify the first client, which is why I was looking to build a VBA solution as I suspect it will be easier (but still not easy!) to prevent duplicated / erroneous results. If you can live with the risk of duplication (e.g. because it's low / plus you can easily test for it) then this second part isn't too hard. Array formula =INDEX($A$1:$A$1335,MATCH(F$1&F2,$B$1:$B$1335&$C$1:$C$1335,0)) would do it. A simple test that no results are duplicated would be =OR(F2=F3,F3=F4)

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

R​

[/td][td]

Z​

[/td][td]

AA​

[/td][td]

AB​

[/td][td]

AC​

[/td][/tr]
[tr][td]

1​

[/td][td] [/td][td] [/td][td] [/td][td][/td][td] 3[/td][td][/td][/tr]


[tr][td]

2​

[/td][td] Client 1[/td][td] Manchester[/td][td] 100[/td][td][/td][td] manchester[/td][td][/td][/tr]


[tr][td]

3​

[/td][td] Client 2[/td][td] Manchester[/td][td] 150[/td][td][/td][td] 4[/td][td][/td][/tr]


[tr][td]

4​

[/td][td] Client 3[/td][td] Brighton[/td][td] 200[/td][td][/td][td] Top Clients[/td][td] Top Scores[/td][/tr]


[tr][td]

5​

[/td][td] Client 5[/td][td] London[/td][td] 150[/td][td][/td][td] Client 2[/td][td] 150[/td][/tr]


[tr][td]

6​

[/td][td] Client 6[/td][td] Manchester[/td][td] 50[/td][td][/td][td] Client 1[/td][td] 100[/td][/tr]


[tr][td]

7​

[/td][td] Client 4[/td][td] Manchester[/td][td] 50[/td][td][/td][td] Client 6[/td][td] 50[/td][/tr]


[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td] Client 4[/td][td] 50[/td][/tr]


[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In AB3 control+shift+enter, not just enter, and copy down:

=SUM(IF(R2:R7=AB2,IF(Z2:Z7>=LARGE(IF(R2:R7=AB2,Z2:Z7),
    MIN(AB1,SUM(IF(R2:R7=AB2,IF(ISNUMBER(Z2:Z7),1))))),1)))

In AB5 control+shift+enter and copy down:

=IF($AC5="","",INDEX($A$2:$A$7,SMALL(IF($R$2:$R$7=$AB$2,IF($Z$2:$Z$7=$AC5,
    ROW($A$2:$A$7)-ROW($A$2)+1)),COUNTIFS($AC$5:$AC5,$AC5))))

In AC5 control+shift+enter and copy down:

=IF(ROWS($AC$5:AC5)<=$AB$3,LARGE(IF($R$2:$R$7=$AB$2,$Z$2:$Z$7),ROWS($AC$5:AC5)),"")