Find The Top Ten #s in a Data Sheet

[Syndrome78]

I have a worksheet with 20 years worth of data and I was tasked to find the top ten load data values for the entire worksheet. Here is an example of the spreasheet. The H# is the hour and it goes up to H24.

CompID Dt H1 H2 H3 H4 H5
1 1/1/1989 142 124 117 112 112
1 1/2/1989 134 120 115 113 114
1 1/3/1989 125 116 113 113 116
1 1/4/1989 147 143 142 144 152
1 1/5/1989 169 169 171 174 187
1 1/6/1989 161 155 155 157 164
1 1/7/1989 135 128 125 122 128
1 1/8/1989 129 119 113 113 115
1 1/9/1989 124 117 111 114 117
1 1/10/1989 131 121 120 118 122

 

[mintjulep]

And "top ten" means what?

The 10 highest numbers?

The 10 most frequently occurring numbers?

Something else?

 

[Syndrome78]

The top ten highest.

 

[SkipVought]

Hi,

Do you ONLY want to know the TOP 10 VALUES, or do you also need to know WHERE they occurred?



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, check out the LARGE spreadsheet function. You could use it without any VBA code at all.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, using Conditional Formatting, along with a list of the top 10 I found using the LARGE function, you can see that the values in 1/5/1989 and 1/6/1989 are in the top 10.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Syndrome78]

The large function works, but what conditional format can I use to find the date and hour associated with it.

 

[SkipVought]

You really did not state your requirement completely. I assumed that you might want to know the date and time.

Suppose you fully explain exactly how you want this to work. Then it will take all the guesswork out of it.

How do you want the data presented?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Syndrome78]

I need the date from the left hand column B and I will need to know the hour H# in row 1. The request is for the top ten highest values and they want to know what day and what hour they occurred.

I need to know how I can get the top ten highest, what day they occured and what hour they occured, but I don't know how to get the last 2.

 

[SkipVought]

"...I don't know how to get the last 2..."

Then how did you get the first eight?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

How do you want multiples handled?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW,

If your data were Normalized, in an Access table, for instance, the solution would be quite easy.

That your data is not Normalized, will inflict upon you some blood, sweat and tears.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

After I normaized the data you sent, (took about 30 seconds) I got this (took about 10 seconds)
[tt]
CompID Dt Hr Value
1 1/5/1989 H1 169
1 1/5/1989 H2 169
1 1/5/1989 H3 171
1 1/5/1989 H4 174
1 1/5/1989 H5 187
1 1/6/1989 H1 161
1 1/6/1989 H2 155
1 1/6/1989 H3 155
1 1/6/1989 H4 157
1 1/6/1989 H5 164
[/tt]
To normalize in Access will take a few more minutes.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}