Import Log and find highest for each hour

[shcsbaker]

I've got a text file formatted as shown below. I'm looking for a way to highlight the highest value after I import the file into excel.
0:00_1
0:01_1
0:02_2
0:03_2
0:04_1

* this continues for a 24hr period

Thanks,
Kevin

[HangMan]

Hi Kevin,

What is considered to be the highest value, e.g. would 0:04_1 be higher than 0:03_2? Can you upload a sample and indicate the criteria which determines the highest value, is it the 0:00 part or the _1 suffix or some combination of the two?

[newdoverman]

If the value after the _ is to be evaluated for being the max in the list this will separate out the value after the _

Please Login or Register  to view this content.

The MAX function can then determine the max value in the column with the separated values.

Please Login or Register  to view this content.

[shcsbaker]

HangMan-

Sorry about that! After re-reading my post, I can see how I wasn't very clear. I have a batch file that runs every minute and grabs two bits of info (the time and the number of logged on users)so the 0:00 is the time and the last number is the number of logged on users. the _ (underscore) is just my delimiting character. The batch file write this info to a text file. I'm trying to figure out a way to grab the highest value per hour. So I need to get the highest number from 0:00 through 0:59, 1:00 through 1:59, etc. The results would be similar to this:
0 = 0
1 = 0
2 = 0
3 = 0
4 = 0
5 = 1
6 = 1
7 = 0
8 = 2
etc
below is a the file I'd import
10_14_2015.txt

[newdoverman]

With the imported values in column A2 down, enter this in B2 and fill down to extract the hour.

Please Login or Register  to view this content.

In column D2 enter the values 0 and fill down to 24
In column E enter this formula and fill down to give the count per hour

Please Login or Register  to view this content.

[newdoverman]

Without helper columns, enter this in E2 and fill down (data starts in A2) and values from 0 to 24 in column D starting in D2

Please Login or Register  to view this content.

[shcsbaker]

newdoverman - Thanks for your help, but you've got me lost. I'm not understanding what I need to do.

[newdoverman]

Ok, I was working with your first example. Here is something that will work with your second example
With the data in column A starting in A2:
To get the hour enter in B2 and fill down:

Please Login or Register  to view this content.

To get the number of Users per time enter in C2 and fill down:

Please Login or Register  to view this content.

In column E starting in E2 enter 0 to 23
In column F to get the number of Users per hour enter in F2 and fill down:

Please Login or Register  to view this content.

In column G to get the max number of Users per time enter this in G2 and fill down (ARRAY FORMULA ENTER WITH CTRL+ SHIFT+ENTER)

Please Login or Register  to view this content.

[shcsbaker]

newdoverman -

WOW! Thanks... lots to disgust here, but it works!

Thanks,
Kevin

[newdoverman]

Thank you for the feedback. Sorry for taking so long. I was working with the first example and a misconception at the same time.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.