Count unique phrase

[darrenb]

Hello,

I have attached an abstract of a spreadsheet which has a series of data and although I can work out various things, I'm stuck on the following:

Typical phrase is:
2010-08-01 10:24:00 +0000 1709120029: Device found: 00:23:d7:0f:26:25 (RSSI -90)

1. The serial number 00:23:d7:0f:26:25 can repeat a few times, and want I'm looking to find out is the number of unique serial numbers within the document

2. Im also looking to find the number of times the phrase "device found" is found in each hour of each day

3. And lastly Im looking to know the number of times serial number with RETRY exists for each serial number, so I can say there were x number of 1 RETRY, y number of 2 RETRY, z numbers of RETRY etc etc

Hopefully some of the Excel experts on here can help me solve this as its driving me mad!

Cheers

[Marcol]

There appears to be a problem with your attachment, please check it out and post the corrected copy.

[darrenb]

it works fine on mine? Its excel 2008?

[Marcol]

Check by opening the attachment you have posted, it seems to be a workbook with no sheets.
The sheet count is one, but nothing else can be accessed, except that the author is N******* M******.

[darrenb]

hi, I have reattached in 2 formats, hope that helps??

[Marcol]

Both files are okay.

Please remove the attachment from post #1, it has somehow become compromised.

[estige]

Hi, I had no problem opening the atachment from the first post.
I have a possible solution using some helper columns and pivot tables.

[darrenb]

hi estige, thanks for your help, see the way you copied and pasted the serial numbers out, will your solution work by leaving them in?

Basically this is a way to produce stats from an online system ie. we copy and paste in all the information and it automatically produces the results. We need it to be very quick?

[estige]

Hi, I did't copy and paste at all. Serialnumbers are being extracted automatically if they have the format ??:??:??:??:??:??

[darrenb]

hi, this looks great, there could be 800,000 entries in the spreadsheet though, can the pivot tables be extended for this?

[estige]

Sure, but I have adjusted it a bit (see attachment).
I have removed column A from the data source for the tables to avoid possible complications when you updata data. I have also extended the formulaes (in column B through F) down to row 9999. If you want more, just copy formulaes as far down as you need.

[estige]

there could be 800,000 entries in the spreadsheet though, can the pivot tables be extended for this?

I asume you don't mean 800,000 unique serial numbers - that surely would call for another approach than listing them in a table. Because of that at least the method for counting retries would have to be different.

[darrenb]

there would be 800,000 entries, but could be 40,000 unique serials? I could send the whole spreadsheet, but wouldnt want to put on forum for everyone to see?

I really appreciate your help in this

[darrenb]

can anyone help me with this???

[Marcol]

Is your data imported on a daily basis?
i.e. can the data be for more than one date?

[darrenb]

its imported on a monthly period to run statisticaly information to give to the client.

The suggestion above did work, but not for 800,000 entries?

[Marcol]

Almost there with a somewhat slow, at the moment, VBa routine. Look back later tonight.

[darrenb]

hi Marcol

Did you work out a way? I see you posted something about a vba routine?

Thanks for any help you can give

[Marcol]

Should be there soon.

Have you tried the solution offered here?
http://www.ozgrid.com/forum/showthre...d=1#post523641

It is very good but I don't think it is quite there, the hour results are incomplete.

My code is much slower but it returns much more information, for each hour and day.

Please let me know if you are going Robs' way.

How are Retries defined, are they always ... RETRY/3
or is it this way RETRY/1, RETRY/2, RETRY/3, etc. this can affect the execution time considerably.

[darrenb]

hi, I dont know what to do with Robs code, I dont know how to insert it?

No, they are always RETRY/3, but want I want to count is the number of times, this happens for each serial, so I want to then produce a pie chart showing

1 retry- number of serials it happened for?
2 retries- number of serials it happened for?
3 retries- number of serials it happened for?

I can send you the full spreadsheet with nearly 600,000 entries if that would be easier

[Marcol]

Okay that makes life easier and the code faster.

I'll revisit my code and post back in a few hours, got to go for a while.

I'll test it against Robs' code and let you know the way to go. (Probably Robs' way with some mods)

Don't need another workbook now I'll just use the data I have.

[darrenb]

Thanks, got a few other queries

1. the no. of devices needs to be per hour per day, so I can then collate a graph showing the no. of devices found over each day

2. we are as previously mentioned, we need to find the number of times, RETRY exists for each unique serial number, can the code also be written to include the number of times FAIL exists for each unique serial and paste into a different column?

3. this one might be tricky, is there a way to analyse all data and list the following of the numbers of times in the following range below

extract example from data is (RSSI -90, 31535339s), (RSSI -89, 28s) etc etc


less than 60s
more than 61 but less than 120
more than 121 but less than 300
more than 301 but less than 600
more than 601 but less than 1200
more than 1201 but less than 1800
more than 1801 but less than 2700
more than 2701 but less than 3600
more than 3601 but less than 5400
more than 5401 but less than 7200
more than 7201 but less than 14400
more than 14401 but less than 28800
more than 28801

[Marcol]

Try this workbook to get the ball rolling

1/. Import your data into sheet "2nd" as you normally do. If there is not a header row, then insert a row and give it a header name in A1, say "Text"

2/. Run the macro "RemoveBankRows". This should clear and repopulate sheet "Data".
This step is not required for any real purpose other than a testing backup, and will be integrated into the final code.

3/. Run the macro "Main", this is still a bit slow but it can be improved, once we have an acceptable prototype.

This should handle your original brief, and it has some built in provision for your later requests.

Once you have reviewed this sample.
1/. Do you want "FAILED/4" to be entered in a similar fashion to "RETRY/3"?

2/. I'm not clear about your needs regarding "Device Blocked", do you need something similar to the layout I have for "Device Found"?

3/. Your sample data doesn't cover a wide enough data range to make it clear what you mean in your last post, point 3.
Can you put together a sheet that would clarify this. i.e. maybe a layout as you envisage the results.

I hope this is on the lines you are after.

Cheers.