Highest 3 consecutive points , ranked

[Mantiyeah]

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.

 

[James006]

Hello,

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

Have you tried to Sort in ascending order ?

 

[Mantiyeah]

Hello,

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

Have you tried to Sort in ascending order ?

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

 

[James006]

Hello,

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

Is it the highest sum of each group of 3 for the same city ...???

 

[Mantiyeah]

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

 

[James006]

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 ...?

 

[Eric W]

I think James is right, this is more suited to a macro. Nevertheless, here's a formula option:

	A	B	C	D	E	F
1	City	Value		Max Location (row)	Sum
2	Houston	1		15	17
3	Houston	1		10	8
4	Houston	2		5	7
5	Houston	2
6	Houston	2
7	Houston	3
8	newyork	1
9	newyork	1
10	newyork	2
11	newyork	3
12	newyork	3
13	newyouk	1
14	paris	5
15	paris	6
16	paris	6
17	paris	5
18	paris	4

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =SUM(INDEX(B:B,D2):INDEX(B:B,D2+2)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

 

[James006]

@ Eric

Love your Row Location formula ...!!!

 

[Eric W]

Thanks, that was fun to figure out!

 

[Mantiyeah]

I think James is right, this is more suited to a macro. Nevertheless, here's a formula option:

	A	B	C	D	E	F
1	City	Value		Max Location (row)	Sum
2	Houston	1		15	17
3	Houston	1		10	8
4	Houston	2		5	7
5	Houston	2
6	Houston	2
7	Houston	3
8	newyork	1
9	newyork	1
10	newyork	2
11	newyork	3
12	newyork	3
13	newyouk	1
14	paris	5
15	paris	6
16	paris	6
17	paris	5
18	paris	4

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =SUM(INDEX(B:B,D2):INDEX(B:B,D2+2)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

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?