Rank data and output data based on rank

what are the limits for rank 1, 2 & 3?

Originally Posted by tim201110

what are the limits for rank 1, 2 & 3?

Not sure what you mean.
There might be much more data like top 10 rank.

for example
Date City 1 City 2 City 3 City 4 City 5 City 6 City 7
15.01.2017 0,17% -0,67% 0,88% 0,77% 0,92% 0,35% 0,71%
here we have 7 cities, 7 different values and need to define just 3 ranks.
to calculate them we need limits for values

Okey.
In the original data there will be like over 100 city data to rank by top 10.
So there will be some limits.

Originally Posted by tim201110

for example
Date City 1 City 2 City 3 City 4 City 5 City 6 City 7
15.01.2017 0,17% -0,67% 0,88% 0,77% 0,92% 0,35% 0,71%
here we have 7 cities, 7 different values and need to define just 3 ranks.
to calculate them we need limits for values

I uploaded a new updated file in the first post where I used the code I got in another post:
https://www.excelforum.com/excel-for...not-value.html
and
https://www.excelforum.com/excel-for...f-located.html

Here is the file in attachment

Thanks oeldere.

I am however not sure if I can use this format this way. I will have to do some checking.

Is there a way to do this with formulas and make it more in the format output closer to the way I set it up in the example ?

Originally Posted by JeteMc

This proposed solution employs three helper columns, which may be hidden for aesthetic purposes, on the 'percent' sheet populated by the following array entered formula*:

Formula:

Please Login or Register  to view this content.

The blue table on the 'rank' sheet is then populated by the formula:

Formula:

Please Login or Register  to view this content.

There are ten more helper columns on the 'rank' sheet divided into two tables, yellow and green.
The formula that populates the green table is:

Formula:

Please Login or Register  to view this content.

The formula that populates the yellow table is:

Formula:

Please Login or Register  to view this content.

The formula that populates the 'output' sheet is:

Formula:

Please Login or Register  to view this content.

Note: the 'output' sheet formula only populates the rows headed 'Insert' and 'let og' as I don't understand the criteria for populating the rows headed 'keep' (please provide me with more information).
Zero values in the 'output' table are hidden using conditional formatting.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Thank you very much!

However there is a few errors, in the output, if they can be fixed i will be able to use this!

In the output for 15.01.2017 city 2 and city 6 is in the "let og" should be blank
In the output for 30.01.2017 city 2 is there in "let og" should be blank and city 4 should be insert

I am not able to correct this myeself in the code.

And I have another question,
If I would like to have top 10 instead of a top 3 rank insert and let go, how do i scale it? with the code?

Originally Posted by JeteMc

I need to know more about the criteria for 'let go' and 'insert'. I was under the impression that the top three cities by percentage, for that date, would be included in 'insert' and that any other cities that had a percentage for that date would be in 'let go', so that only cities without a percentage for that date would be left blank. Apparently that is incorrect.
In order to have a top 10 the number of helper columns would need to be increased, however I feel that is secondary to establishing the rational behind including cities into the 'insert' and 'let go' categories.
Let us know if you have any questions.

Sorry. Let me try again.

we rank top 3 out of 7 for any date.
The value for the top 3 for the given date is extracted from data and inserted in the output tab for the current dates.

Next date the data corresponding to the new top 3 will be inserted in the output. Some of the top 3 ranked cities might have been top 3 still, than the new value is inserted.
The cities that might no long be top 3 will be taken out, so the value from the data needs to be inserted.

In this example it will be like (values corresponding with the cities need to be put in output tab):
01.01.2017:
Insert: city: 7,1,3
15.01.2017:
let go: 7, 1
Keept: 3
Insert: 5,3,4

30.01.2017
let go: 3, 4
Keept : 5
Insert: 7, 1, 5

Thanks very much JetMc.
This seems to work.

I however meet another problem when trying to scale it up..
Error message:
"Excel ran out of resources while attempting to calculate one or more formulas. These formulas can therefore not be evaluated."

Its on a Excel for MAC. I will try on another windows machine later.