excel help0r part VII

[Testin da Cable]

help me please:

I require a macro or otherwise for excel that does the following:

• select all rows that do *NOT* begin with a cell that contains any of the following text: :00:, :15:, :30: or :45:
• delete selected rows

you guessed it, it's a zillion row sheet, and I seriously can't go sifting through it by hand help0r me, and you earn a pint of your choice at the BarryBeer!!

 

[Jupitus]

Add a colum to the left of the data to act as an index, and autofill down sequential numbers 1,2,3 etc .... you can sort on this colum afterwards to return the data to its original sequence.

Next insert a column next to the column containing the ":00:" or whatever. In this column use this formula:

=IF(AND(ISERROR(FIND(":00:",A1)),ISERROR(FIND(":15:",A1)),ISERROR(FIND(":30:",A1)),ISERROR(FIND(":45:",A1))),1,0)

but replace the 4 cell references 'A1' with the cell containing the ":00:". You can now copy and paste this formula down the column, and it will give you a 1 if the 00 15 30 and 45 are NOT found, and a 0 if they are. When you have filled the column you can highlight the whole column and select 'edit, copy' and then 'edit, paste special, values'. You now have a column you can sort by (dont forget to select all of the data when you sort) and get all of the rows together that you need. Delete unwanted rows and then sort again using the first column of sequential numbers.

Genius

EDIT: Back it all up before starting!!!!!!!!!!!!!!!!!!!!!!!

 

[Jupitus]

Oh.... and if it doesn't work it is most likely that the cell we are testing is not formatted as text but is a time value... if you get an error let me know, it can be sorted out np.

 

[Testin da Cable]

woot, you rock!! and it's indeed a time formatted value, how do I fix that? if I format it as text it goes all to heck

 

[Jupitus]

ok hold on a sec.....

 

[Jupitus]

new formula:

=IF(AND(ISERROR(FIND(":00:",TEXT(F1,"hh:mm:ss"))),ISERROR(FIND(":15:",TEXT(F1,"hh:mm:ss"))),ISERROR(FIND(":30:",TEXT(F1,"hh:mm:ss"))),ISERROR(FIND(":45:",TEXT(F1,"hh:mm:ss")))),1,0)


again, my cell reference F1 needs replacing with the actual cell on your sheet

 

[Testin da Cable]

hmm, excel says there's an error, then helpfully highlights the formula like so:

=IF(AND(ISERROR(FIND(":00:",TEXT(C3,"hh:mm:ss"))),ISERROR(FIND(":15:",TEXT(C3,"hh:mm:ss"))),ISERROR(FIND(":30:",TEXT(C3,"hh:mm:ss"))),ISERROR(FIND(":45:",TEXT(C3,"hh:mm:ss")))),1,0)

anything you can see? perhaps a typo that I made?

 

[Jupitus]

Can you type in here what the time data we are testing looks like?

Is it time AND date or just time?

Thanks

Edit: you have NOT made a typo, btw

 

[Testin da Cable]

sure, here's an example:

6		1:00:00	13	4	0	83
7		1:15:01	0	1	0	98
8		1:16:00	1	3	1	96
9		1:17:00	2	5	0	92
10		1:18:00	1	2	1	97
11		1:19:00	0	1	1	98
12		1:20:00	0	1	0	98
13		1:21:00	0	2	0	98
14		1:22:00	2	6	0	92
15		1:23:00	1	3	1	96
16		1:24:01	1	2	0	97
17		1:25:00	0	1	0	98
18		1:26:00	1	3	0	96
19		1:27:00	2	5	0	93
20		1:28:01	1	2	0	97
21		1:29:00	33	8	0	59
22		1:30:01	1	1	0	98

there's a sample being taken every minute, but that's far too much data. I want to show values every 15 minutes. I need to unleash something on the time colum that deletes all unneeded values. make the above look like the below:

6		1:00:00	13	4	0	83
7		1:15:01	0	1	0	98
22		1:30:01	1	1	0	98

thanks for taking the time to help me out, I really appreciate it

edit: the thing is over 20k rows long, that is why it isn't going to be a, ahem, hand-job

 

[Jupitus]

check your pm's tdc

edit: and hurry I'm out to lunch soon

 

[Testin da Cable]

sent. eat hearty mate, I'm off for a bite too

 

[Jupitus]

well I used the same formula in a cell to the right and it works fine - I expect you had a funny format in the 2nd column... perhaps try formatting column B as general and then pasting in the formula. Anyway - working version sent, have a nice day

*goes for lunch*

 

[MYstIC G]

Data: Filter: AutoFilter

tbh

 

[Testin da Cable]

yay!

Jupitus - you rock, thanks so much for your trouble (it was indeed dodgy formatting I guess -all is fine now). if you're at the BarryBeer you'll have your pint

teh G - you also rock, but differently than Jupitus. I already knew about the autofilter, and was planning to unleash it's power apon a nice clean colum of 0's and 1's You too shall have a pint, unless you still drink that beastly stuff that smells like chewinggum

 

[xane]

IF ( AND ( FIND ( "excel", TITLE ), NOT ( FIND ( "Software", FORUMTITLE ) ) ), ANSWER = "Wrong Forum, Idiot" )

 

[Testin da Cable]

begone beardy one!!! lest I strike you down with my leet new macro making skillz!!!

besides, excel brings me down in general. hur hur

 

[Jonaldo]

fdisk

 

[old.D0LLySh33p]

This thread is so much like real work it makes babeh jebus cry

 

[Testin da Cable]

I guess my saying that I never work is no longer as accurate as it used to be

*pine for good old days*

 

[Jupitus]

mmmm Beer is good!

Originally posted by Testin da Cable
yay!
Jupitus - you rock, thanks so much for your trouble (it was indeed dodgy formatting I guess -all is fine now). if you're at the BarryBeer you'll have your pint

Pint eh? I might have to think about coming along then

 

[Testin da Cable]

just as long as it's not a pint of ritz reserve or other mouthwash that costs loads per picaliter mind

edit: got one sheet done! five more to go though