formula help on a sheet

[SQUIDD]

Hi All

I have data in a sheet in column A and column B.
I want to end up filtering column C dependant on if it has a "1"

Column A looks like this.

A2=07:20
A3=Grade: A9 Prize: £62 Distance: 470m Going: -170
A4=http://www.site/dog_home.sd?dog_id=1
A5=http://www.site/dog_home.sd?dog_id=6
A6=http://www.site/dog_home.sd?dog_id=4
A7=http://www.site/dog_home.sd?dog_id=12
A8=http://www.site/dog_home.sd?dog_id=100
A9=http://www.site/dog_home.sd?dog_id=29

Then it repeats as above with another batch of data similar. Generally the data is 8 lines long, but sometimes not, that is MY PROBLEM.I want to filter the data that had all 6 runners. So in the example above c2,c3,c3,c4,c5,c6,c7,c8,c9 would ="1".
But in the example below c2,c3,c3,c4,c5,c6,c7,c8 would =""

A2=07:20
A3=Grade: A9 Prize: £62 Distance: 470m Going: -170
A4=http://www.site/dog_home.sd?dog_id=1
A5=http://www.site/dog_home.sd?dog_id=6
A6=http://www.site/dog_home.sd?dog_id=4
A7=http://www.site/dog_home.sd?dog_id=12
A8=http://www.site/dog_home.sd?dog_id=100

If it helps column B looks like this.

[TABLE="width: 309"]
<tbody>[TR]
[TD]B2=07:20
B3=Grade: A9 Prize: £62
B4=Hawaiian Flash[/TD]
[/TR]
[TR]
[TD]B5=Smooth Lane[/TD]
[/TR]
[TR]
[TD]B6=Under The Limit[/TD]
[/TR]
[TR]
[TD]B7=Whitelaithelady[/TD]
[/TR]
[TR]
[TD]B8=Drumsna Lucy[/TD]
[/TR]
[TR]
[TD]B9=Birkdale Lass

The whole thing is to do with is there blocks of 8 and if they are part of the block of 8 I want to filter and keep and if not filter out before a copy and paste.

Thanks for looking.

Dave
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Rick Rothstein]

Okay, I see what you were asking for now. This modification to the code I posted earlier should work for you...

[table="width: 500"]
[tr]
	[td]Sub SixRunners()
  Dim NonTimeData As Range
  Columns("C").ClearContents
  For Each NonTimeData In Columns("A").SpecialCells(xlConstants, xlTextValues).Areas
    If NonTimeData.Rows.Count = 7 [B][COLOR="#0000FF"]And Left(NonTimeData(1).Value, 8) = "Grade: A"[/COLOR][/B] Then NonTimeData.Offset(-1, 2).Resize(NonTimeData.Count + 1).Value = 1
  Next
End Sub[/td]
[/tr]
[/table]

 

[SQUIDD]

Nice one rick.

Spot one,
I love it.

Works like a Charm

Thank you very much

 

[Oeldere]

Book1
	A	B	C	D	E
2	07:20
3	Grade: A9 Prize: £62 Distance: 470m Going: -170			ok	$B$4:$B$10
4	http://www.site/dog_home.sd?dog_id	1		ok	$B$4:$B$10
5	http://www.site/dog_home.sd?dog_id	6		ok	$B$4:$B$10
6	http://www.site/dog_home.sd?dog_id	4		ok	$B$4:$B$10
7	http://www.site/dog_home.sd?dog_id	12		ok	$B$4:$B$10
8	http://www.site/dog_home.sd?dog_id	100		ok	$B$4:$B$10
9	http://www.site/dog_home.sd?dog_id	29		ok	$B$4:$B$10
10	07:20			failure
11	Grade: A9 Prize: £62 Distance: 470m Going: -170				$B$12:$B$18
12	http://www.site/dog_home.sd?dog_id	1			$B$12:$B$18
13	http://www.site/dog_home.sd?dog_id	6			$B$12:$B$18
14	http://www.site/dog_home.sd?dog_id	4			$B$12:$B$18
15	http://www.site/dog_home.sd?dog_id	12			$B$12:$B$18
16	http://www.site/dog_home.sd?dog_id	100			$B$12:$B$18
17	07:20			failure
18	Grade: A9 Prize: £62 Distance: 470m Going: -170			ok	$B$19:$B$25
19	http://www.site/dog_home.sd?dog_id	1		ok	$B$19:$B$25
20	http://www.site/dog_home.sd?dog_id	6		ok	$B$19:$B$25
21	http://www.site/dog_home.sd?dog_id	4		ok	$B$19:$B$25
22	http://www.site/dog_home.sd?dog_id	12		ok	$B$19:$B$25
23	http://www.site/dog_home.sd?dog_id	100		ok	$B$19:$B$25
24	http://www.site/dog_home.sd?dog_id	29		ok	$B$19:$B$25
25	07:20			failure
26	Grade: A9 Prize: £62 Distance: 470m Going: -170				$B$27:$B$33
27	http://www.site/dog_home.sd?dog_id	1			$B$27:$B$33
28	http://www.site/dog_home.sd?dog_id	6			$B$27:$B$33
29	http://www.site/dog_home.sd?dog_id	4			$B$27:$B$33
30	http://www.site/dog_home.sd?dog_id	12			$B$27:$B$33
31	http://www.site/dog_home.sd?dog_id	100			$B$27:$B$33
32
Blad22

Cell Formulas
Range	Formula
D3	=IFERROR(IF(AND(LEFT($A3,5)="Grade",COUNTIF(INDIRECT($E3),">0")=6),"ok",IF(AND($D2="ok",LEFT(A3,4)="http"),"ok","")),"failure")
E3	=IF(LEFT(A3,4)="http",E2,IF(LEFT(A3,5)="grade",ADDRESS(ROW()+1,2)&":"&ADDRESS(ROW()+7,2),""))