MegaSack DRAW - 6pm Christmas Eve - LIVE on our YouTube Channel
I have a log file with thousands of rows. The first column of each row is a timestamp i.e. 15:01:23. I want to count how many times each value repeats to get how many transactions happen in each second. I could do this in SQL with group by and count. But Excel? Pivot table? The UI for this seems not to be able to deal with my data.
I know I could probably use grep but reasons.
Conceptually a pivot table would work, although you’d have 84000 possible categories so might be painful or might break it.
I’d probably do it Python but if someone made me do it in excel I’d make a column with all possible time slots then the column beside it I’d use a countif - if that doesn’t work I’d suspect confusion between a time format and a text field as that’s always fun on excel.
Please tell me this isn't Elite related.
I’m thinking UNIQUE to get the list of unique stamps. Then COUNTIF for the number of times each appears.
Array formula if values are sorted in order? Assume you want a second by second breakdown rather than an average.
Pivot table is easiest unless you are just looking for the average of course. You might want to insert an extra column to make sure your times are really truncated to seconds and not 1000ths first though
I am thinking sort by time stamp - count in next column for each time stamp - flag in next column for if timestamp differs from next row - and filter on this flag.
Yeah, but the timestamp could be many characters long, so you might end up with effectively thousands of different ones. Is the log file a CSV or xls? If CSV I'd be tempted to script something that would cop the timestamp to something less granular, then either do the counting or put it into excel to do the counting.
I'm with reeksy, I would set up a sheet with a column of the unique values of full seconds and do countif matching of the same hour:minute:second.
Please tell me this isn’t Elite related.
Lol no, it's work 🙂
I did use the COUNTIF method in the end. The time period was only about 10 mins so it was relatively easy to do. Except for the fact that my timestamps were converted from rounded second values into decimal numbers, and that's what it was using to compare and there were rounding errors. I had to use TEXT() function to make them into formatted strings to the nearest second.
Stick the sheet into Power Query and group on time (assuming the timestamps are consistent).. Count rows.
you could setup some time stamps and do a countifs => one time and < the next time and = the value
= countifs($A:$A,">=CELL_TIMESTAMP1",$A:$A,"<CELL_TIMESTAMP2",$B:$B,CELLVALUE)
Or you could use the FREQUENCY function, which can do it all for you, and there are plenty of tutorials to do exactly what you're after
The UI for this seems not to be able to deal with my data.
Is the time stamp being recorded as time and not text data?
