Cooy data

[glynn1969]

Hello probably a very simple solution to my issue.

On sheet 1 column b I have a list of children going down column b then at the end of the first group I have the class they are in, then the list contues with the next group of children and then their class.

However I woukd like a vba code that would look down the list, ignore all the children names and then when it finds a text containing the word class, then apply that class in column A to all the children above, then repeat for the fill list in column b.

 

[dreid1011]

If a non vba solution will work, you can give this a try:

Book1
	A	B
1	Class	Roster
2	Class 1	Adam
3	Class 1	Bobby
4	Class 1	Carly
5	Class 1	Daniel
6	Class 1	Esme
7	Class 1	Franky
8	Class 1	George
9	Class 1	Heather
10	Class 1	Isabella
11	Class 1	Jackson
12		Class 1
13	Class 2	Adam
14	Class 2	Bobby
15	Class 2	Carly
16	Class 2	Daniel
17	Class 2	Esme
18	Class 2	Franky
19	Class 2	George
20	Class 2	Heather
21	Class 2	Isabella
22	Class 2	Jackson
23		Class 2
24	Class 3	Adam
25	Class 3	Bobby
26	Class 3	Carly
27	Class 3	Daniel
28	Class 3	Esme
29	Class 3	Franky
30	Class 3	George
31	Class 3	Heather
32	Class 3	Isabella
33	Class 3	Jackson
34		Class 3
Sheet4

Cell Formulas
Range	Formula
A2:A34	A2	=LET(i,INDEX(FILTER($B$2:$B$34,ISNUMBER(SEARCH("Class",$B$2:$B$34))),COUNTIF($B$2:B2,"*Class*")+1),IF(ISNUMBER(SEARCH("Class",B2)),"",i))