Not sure how to title this, please take a peek.

[Hawkmek]

Had such good luck this morning I thought I would try again before Happy Hour.

Data will be sorted by column A so all reds, blues, greens, etc will be together. Formulas will be in Column C
Scan Column B for 'target'. When target found populate Column C with target until value in A changes.
Column C is showing desired final product.
I tried figuring this out with Nested IFs and got close but there was always an issue.


--A-------B--------C
red-----stuff---don't care
red-----stuff---don't care
blue----target--target
blue----target--target
blue----target--target
blue----stuff---target
blue----stuff---target
blue----stuff---target
blue----stuff---target
green---stuff---don't care
green---stuff---don't care

 

[Eric W]

Maybe:

	A	B	C
1	Col A	Col B	Col C
2	red	stuff
3	red	stuff
4	blue	target	target
5	blue	target	target
6	blue	target	target
7	blue	stuff	target
8	blue	stuff	target
9	blue	stuff	target
10	blue	stuff	target
11	green	stuff
12	green	stuff

<tbody>
</tbody>

Manual​

Worksheet Formulas Cell Formula C2 =IF(A2<>A1,IF(B2="target","target",""),IF(OR(B2="target",C1="target"),"target","")) <tbody> </tbody>

<tbody>
</tbody>

seems jtakw came up with a shorter version below.

 

[jtakw]

Hi,

Another way, need an empty or header row on top of your data:

Book1
	A	B	C
1
2	red	stuff	don't care
3	red	stuff	don't care
4	blue	target	target
5	blue	target	target
6	blue	target	target
7	blue	stuff	target
8	blue	stuff	target
9	blue	stuff	target
10	blue	stuff	target
11	green	stuff	don't care
12	green	stuff	don't care
13	purple	stuff	don't care
14	purple	target	target
15	purple	stuff	target
16	purple	stuff	target
Sheet52

Cell Formulas
Range	Formula
C2	=IF(B2="target","target",IF(A2=A1,C1,"don't care"))

Formula copied down.

 

[Hawkmek]

Thank you both. I went with the shorter solution. Easier to get my head around.

 

[jtakw]

You're welcome.