Selecting a column then automatically deselecting values

[diamondjoechubbs]

I am trying to recreate a macro to take a bunch of data and apply the same name to it where every line isn't automatically tagged. The first step is to have excel create a new column and import the name on the lines it is on and I have done that using the following formula =TRIM(IFERROR(RIGHT(B2,LEN(B2)-FIND(": ",B2)), "0")). Then I have a column that has the name followed by any number of blanks or spaces depending on how many fields of data that agent has. I have seen some way that you could select all the fields with a space and then tell it to start at the top and if it was blank to copy the field above it, but I am unable to recreate this step. I included a picture, but what I need it to do is automate filling in the rest of column S with the last value it sees above it.

 

[Peter_SSs]

What about just changing your existing formula to something like this?

21 09 03.xlsm
	S	T
1
2	a	Employee: a
3	a
4	a
5	a
6	b	Employee: b
7	b
8	b
9	b
10	b
11	b
12	c	Employee: c
13	c
14	c
15	c
16	c
17	c
Copy down

Cell Formulas
Range	Formula
S2:S17	S2	=IFERROR(TRIM(REPLACE(T2,1,FIND(":",T2),"")),S1)

 

[diamondjoechubbs]

What about just changing your existing formula to something like this?

21 09 03.xlsm
	S	T
1
2	a	Employee: a
3	a
4	a
5	a
6	b	Employee: b
7	b
8	b
9	b
10	b
11	b
12	c	Employee: c
13	c
14	c
15	c
16	c
17	c
Copy down

Cell Formulas
Range	Formula
S2:S17	S2	=IFERROR(TRIM(REPLACE(T2,1,FIND(":",T2),"")),S1)

Can you explain to me how this works?

 

[Peter_SSs]

Can you explain to me how this works?

Sure.

=IFERROR(TRIM(REPLACE(T2,1,FIND(":",T2),"")),S1)


TRIM(REPLACE(T2,1,FIND(":",T2),""))
This takes the value in column T and replaces all characters from character 1 up to and including the colon with nothing, then trims the results as per your formula.
This is pretty similar to this part of your formula: RIGHT(T2,LEN(T2)-FIND(": ",T2)), I just thought the 'replace' was a bit more direct & neater.

So in cell S2 this produces the "a" as required.

In S3, this part of the formula produces an error because FIND(":", T3) fails and the IFERROR in my formula says in that case take the value from the cell above.
This produces another "a"
These errors keep occurring down the column and keep producing "a" from the cell above until the FIND does not produce an error which occurs in row 6 of my sample, producing a "b" there.

We could also have used your original formula and just replaced the red IFERROR part with the blue, like this
=TRIM(IFERROR(RIGHT(T2,LEN(T2)-FIND(": ",T2))," "))
=TRIM(IFERROR(RIGHT(T2,LEN(T2)-FIND(": ",T2)),S1))

Here it is with my same sample data

21 09 03.xlsm
	S	T
1
2	a	Employee: a
3	a
4	a
5	a
6	b	Employee: b
7	b
8	b
9	b
10	b
11	b
12	c	Employee: c
13	c
14	c
15	c
16	c
17	c
Copy down (2)

Cell Formulas
Range	Formula
S2:S17	S2	=TRIM(IFERROR(RIGHT(T2,LEN(T2)-FIND(": ",T2)),S1))