Capturing Highest Historical Rank in a Continuously Updated Worksheet

[Mz309Lord]

I have a list of sites that are being ranked based on various factors that feed into the "score" equation, and I also want to be able to capture and save the highest rank each site has ever achieved (and the date of that high ranking).

This is currently captured in an Excel 365 sheet on Sharepoint where various people are able to make updates to factors feeding into the score equation as well as adding new sites to the list. Here's a basic view of the worksheet:

Site	Score	Date	Rank	Highest Historical Rank	Date of Highest Historical Rank
a	90	1/5/24	1	??	??
b	80	1/17/24	2
c	70	1/4/24	3
d	60	2/1/24	4
e	50	1/29/24	5

The "score" is an equation (which updates depending on the whatever the inspector finds on the given date). I have limited VBA skills, so was able to cut and paste someone else's code to get a new field called "previous rank," however, since I want to capture the all time highest rank a site has ever achieved, I'm drawing a blank as to how to maintain that high score. (Here's the tutorial I used to store the previous rank: How to remember or save previous cell value of a changed cell in Excel?)

 

[Coyotex3]

Hmmmm, assuming your data starts on range A1, maybe you can use this formula in cell E2

=MAX(FILTER($B$2:$B$6,$A$2:$A$6=A2))

And on cell F2 you can try the following:

=FILTER(C2:C6,B2:B6=E2)

All based on the data on this screenshot:

You would have to update the formula to capture the full range on your sheet. Would it be possible to convert your data into an excel table since the sheet will be updated continuously?

 

[awoohaw]

This gives you top 5 and Ties.

Book1
	A	B	C	D	E	F
1	Site	Score	Date	Rank	Highest Historical Rank	Date of Highest Historical Rank
2	a	80	2001-05-24	1	90	2001-04-24
3	b	80	2024-01-17	2	82	2001-11-24
4	c	70	2001-04-24	3	80	2001-05-24
5	d	60	2002-01-24	4	80	2024-01-17
6	e	50	2024-01-29	5	78	2001-02-24
7	a	90	2001-04-24		78	2001-10-24
8	b	71	2023-12-17
9	c	67	2001-03-24
10	d	65	2001-12-24
11	e	66	2023-12-29
12	a	75	2001-03-24
13	b	72	2023-11-17
14	c	69	2001-02-24
15	d	82	2001-11-24
16	e	65	2023-11-29
17	a	78	2001-02-24
18	b	73	2023-10-17
19	c	66	2001-01-24
20	d	78	2001-10-24
21	e	74	2023-10-29
Sheet3

Cell Formulas
Range	Formula
E2:F7	E2	=SORT(FILTER($B$2:$C$21,B2:B21>=AGGREGATE(14,,$B$2:$B$21,5),""),1,-1)
Dynamic array formulas.

 

[Mz309Lord]

Hmmmm, assuming your data starts on range A1, maybe you can use this formula in cell E2

=MAX(FILTER($B$2:$B$6,$A$2:$A$6=A2))

And on cell F2 you can try the following:

=FILTER(C2:C6,B2:B6=E2)

All based on the data on this screenshot:

View attachment 106721

You would have to update the formula to capture the full range on your sheet. Would it be possible to convert your data into an excel table since the sheet will be updated continuously?

Hmmmm, assuming your data starts on range A1, maybe you can use this formula in cell E2

=MAX(FILTER($B$2:$B$6,$A$2:$A$6=A2))

And on cell F2 you can try the following:

=FILTER(C2:C6,B2:B6=E2)

All based on the data on this screenshot:

View attachment 106721

You would have to update the formula to capture the full range on your sheet. Would it be possible to convert your data into an excel table since the sheet will be updated continuously?

Hmmmm, assuming your data starts on range A1, maybe you can use this formula in cell E2

=MAX(FILTER($B$2:$B$6,$A$2:$A$6=A2))

And on cell F2 you can try the following:

=FILTER(C2:C6,B2:B6=E2)

All based on the data on this screenshot:

View attachment 106721

You would have to update the formula to capture the full range on your sheet. Would it be possible to convert your data into an excel table since the sheet will be updated continuously?

I tried plugging that formula into E1 (not as VBA code, just regular excel) and as soon as the score in B1 is overwritten, E1 shows the latest score. I see that you're suggesting I plug the formula in as VBA code, though, so I'm probably doing this incorrectly. How do I plug VBA code into specific cells as you suggest? (I'm only familiar with inserting code into whole tabs, as I'm a VBA newbie.)

 

[Mz309Lord]

This gives you top 5 and Ties.

Book1
	A	B	C	D	E	F
1	Site	Score	Date	Rank	Highest Historical Rank	Date of Highest Historical Rank
2	a	80	2001-05-24	1	90	2001-04-24
3	b	80	2024-01-17	2	82	2001-11-24
4	c	70	2001-04-24	3	80	2001-05-24
5	d	60	2002-01-24	4	80	2024-01-17
6	e	50	2024-01-29	5	78	2001-02-24
7	a	90	2001-04-24		78	2001-10-24
8	b	71	2023-12-17
9	c	67	2001-03-24
10	d	65	2001-12-24
11	e	66	2023-12-29
12	a	75	2001-03-24
13	b	72	2023-11-17
14	c	69	2001-02-24
15	d	82	2001-11-24
16	e	65	2023-11-29
17	a	78	2001-02-24
18	b	73	2023-10-17
19	c	66	2001-01-24
20	d	78	2001-10-24
21	e	74	2023-10-29
Sheet3

Cell Formulas
Range	Formula
E2:F7	E2	=SORT(FILTER($B$2:$C$21,B2:B21>=AGGREGATE(14,,$B$2:$B$21,5),""),1,-1)
Dynamic array formulas.

OK, thank you, however, I don't need to see top 5 or ties. Also, the list doesn't grow as you've shown (updated site info doesn't get concatenated to the end of the list--instead, the score and date of inspection are simply overwritten in columns B and C, and then the Rank formula i have in column D automatically updates itself to show the newest rank.)

 

[Fluff]

@Coyotex3 when posting a formula please use the formula tags (the xls icon), it avoids confusion over whether you are posting a formula or VBA.

 

[Coyotex3]

@Coyotex3 when posting a formula please use the formula tags (the xls icon), it avoids confusion over whether you are posting a formula or VBA.

Sure thing. Can't believe I didn't notice there was a different tag for Formulas until now! Will make the change moving forward.

 

[Coyotex3]

I tried plugging that formula into E1 (not as VBA code, just regular excel) and as soon as the score in B1 is overwritten, E1 shows the latest score. I see that you're suggesting I plug the formula in as VBA code, though, so I'm probably doing this incorrectly. How do I plug VBA code into specific cells as you suggest? (I'm only familiar with inserting code into whole tabs, as I'm a VBA newbie.)

Apologies, as Fluff pointed out, I used the VBA code Tags as opposed to the Formula code tags. This is a formula, no need to use VBA on this one.

What do you mean when the score is overwritten? Based on my example, if you write say 97 on cell B2(B1 contains the titles, Cell B2 contains the data), then E2 should show 97. Is that not happening on your end?

 

[Mz309Lord]

@Coyotex3 I did try inserting your formulas (and would be thrilled to get formulas to do this job rather than VBA!), however, when I overwrite B2 with any new number, then E2 just shows me that newest B2 entry. So for example, if at first Site A scores 90, then the next day they score 93, I can see that E2 goes up to 93 and I get the newest date in F2, but then on the third day if the score drops to 53, then E2 and F2 don't save that old high score of 93 with correlating date of yesterday...

 

[Mz309Lord]

Here is what I'm considering might work, but I'm not quite sure how to code it:

Apologies, as Fluff pointed out, I used the VBA code Tags as opposed to the Formula code tags. This is a formula, no need to use VBA on this one.

What do you mean when the score is overwritten? Based on my example, if you write say 97 on cell B2(B1 contains the titles, Cell B2 contains the data), then E2 should show 97. Is that not happening on your end?

Hi @Coyotex3 --I just realized why your code doesn't work: it's because you have added an additional row for Site A with a new date, so the code works if I keep adding new rows for the existing sites. However, that's not how this dataset gets updated. (We might add new sites H, I, J, K, etc. etc.), but when the sites we already inspect get a new score, their old score is over-written. So when site A started out with a score of 90, the next day that 90 appearing in cell B2 is overwritten with 93--which should now be captured as the new high score. When the site is inspected three days later and gets a score of 53, I still want the all-time-high score for site A to show 93. Does that make sense?