Highest 3 consecutive points , ranked

M

Mantiyeah

New Member
Joined
Sep 19, 2018
Messages
8
I have a column with different location names, and another with their temperatures on different consecutive locations. Each city has a random amount of points/locations (around 40)
For example:
<code>Houston 10
Houston 11
Houston 14
coruna 20
coruna 21
coruna 25
coruna 28
vigo 30
vigo 15
vigo 23
vigo 24
</code>How can I find the hottest 3 consecutive points in a single city and get a link to know where are they positioned? For example ,something to show me that coruna 21, coruna 25, and coruna 28 is the hottest area. Or even coruna 25, coruna 28 and vigo 30, since they are consecutive rows.
We have managed to analyse the hottest cities with pivot tables, but not the worse areas for those cities, which seem like a hardest thing to do.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hello,

With your Pivot Table, you have probably used a Sorting feature ( descending ) ...

Have you tried to Sort in ascending order ?
 
Upvote 0
James006 said:
Hello,

With your Pivot Table, you have probably used a Sorting feature ( descending ) ...

Have you tried to Sort in ascending order ?
Click to expand...
Thanks for your answer. The pivot table would give me the total numbers divided by cities, rather than exact points inside the cities. Unless there is some other way to arrange the pivot tables whichs I don't know of.

For example, one city may look like this

houston 12
Houston 13
Houston 43
Houston 12
Houston 12
Houston 12
Houston 34
Houston 33
Houston 40

I would need the excel to point me at the coloured section
 
Upvote 0
Hello,

What would be the rule you want to apply ...???

Is it the highest sum of each group of 3 for the same city ...???
 
Upvote 0
The highest groups of 3 over all of the data, and each group should be part of the same city if possible.
A bigger example:

Houston 1
Houston 1
Houston 2
Houston 2
Houston 2
Houston 3
newyork 1
newyork 1
newyork 2
newyork 3
newyork 3
newyouk 1
paris 5
paris 6
paris 6
paris 5
paris 5
paris 4

So I need something that can do what the colors are doing now
 
Upvote 0
Thanks for your explanation ...

Are you data merged in cells within a single column ...???

In my opinion, a macro would be better than a formula ...

Do you have a preference ...?
 
Upvote 0
I think James is right, this is more suited to a macro. Nevertheless, here's a formula option:


ABCDEF
CityValueMax Location (row)Sum
Houston
3
Houston
Houston
Houston
Houston
newyork
newyork
newyork
newyork
newyork
newyouk
paris
paris
paris
paris
paris

<tbody>
[TD="align: center"]1[/TD]

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

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

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Houston[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUM(INDEX(B:B,D2):INDEX(B:B,D2+2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=MOD(MAX(IF(MMULT(COUNTIF($D$1:$D1,ROW($A$2:$A$18)+{-2,-1,0,1,2}),{1;1;1;1;1})=0,IF($A$2:$A$18=$A$4:$A$20,($B$2:$B$18+$B$3:$B$19+$B$4:$B$20)*1000+ROW($B$2:$B$18)))),1000)}[/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]



This works on the small sample you gave, but it has some potential flaws, like if the values in B are non-integer, or if the range isn't sorted.
 
Upvote 0
Eric W said:
I think James is right, this is more suited to a macro. Nevertheless, here's a formula option:


ABCDEF
CityValueMax Location (row)Sum
Houston
3
Houston
Houston
Houston
Houston
newyork
newyork
newyork
newyork
newyork
newyouk
paris
paris
paris
paris
paris

<tbody>
[TD="align: center"]1[/TD]

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

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

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

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Houston[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=SUM(INDEX(B:B,D2):INDEX(B:B,D2+2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=MOD(MAX(IF(MMULT(COUNTIF($D$1:$D1,ROW($A$2:$A$18)+{-2,-1,0,1,2}),{1;1;1;1;1})=0,IF($A$2:$A$18=$A$4:$A$20,($B$2:$B$18+$B$3:$B$19+$B$4:$B$20)*1000+ROW($B$2:$B$18)))),1000)}[/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]



This works on the small sample you gave, but it has some potential flaws, like if the values in B are non-integer, or if the range isn't sorted.
Click to expand...
Thank you very much, learning so much by reading the forum.This has been really helpful.
I had no priority of a formula over a macro though.
How would you do it with a macro?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Deepakchoudhary
  • Question Question
Database & Bar Chart preparation for comparison of 3 parameters, 5 items on Dynamic selection of OUTLETS
Replies
0
Views
107
Deepakchoudhary
Deepakchoudhary
S
  • Solved
How to work out general trend in a series of numbers (film rankings)
Replies
5
Views
468
stefanaalten
S
JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
117
JenniferMurphy
JenniferMurphy
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E
U
  • Solved
Vlookup to return the highest and lowest value
Replies
2
Views
85
Utradeshow
U

Forum statistics

Threads
1,224,825
Messages
6,181,189
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top