Formula to autofill values in B with condition from A

[bettlejus]

Hi,

I cannot figure the formula logic for the following operation:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]CRT[/TD]
[TD]STATUS[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]P1[/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]P2[/TD]
[TD]P2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]P2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]P2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]P3[/TD]
[TD]P3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]P3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]P3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]P3[/TD]
[/TR]
</tbody>[/TABLE]


I have a count in column A, that is the same untill a value changes in B, like 1..1..1..for P1 and I want a formula that will fill column B to look just like column C, fill the first value untill number in column A changes.

Can you guys help me out?

Thank yoU!

 

[Marcelo Branco]

Try

C2 copied down
=INDEX(B:B,MATCH(A2,A:A,0))

M.

 

[Joe4]

This is pretty easy to do.
See here for an explanation and details of the steps: https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

 

[sheetspread]

If you have Excel 2013 or later, there's a flash fill. Some more methods:

https://www.mrexcel.com/forum/excel-questions/945252-formula-populate-text-column.html

 

[Marcelo Branco]

Try

C2 copied down
=INDEX(B:B,MATCH(A2,A:A,0))

M.

Disregard, I replied too fast.
Joe4 has provided a solution.

M.

 

[bettlejus]

Try

C2 copied down
=INDEX(B:B,MATCH(A2,A:A,0))

M.

Thank you Marcelo! Works great!

 

[Peter_SSs]

Joe4 has provided a solution.

So has sheetspread.
I wasn't aware that Flash Fill would work on disjoint ranges like that. Good to know, thanks for the tip.
The steps,
- Select B2:B11 for the example given
- Data ribbon tab -> Flash Fill

 

[Marcelo Branco]

Thank you Marcelo! Works great!

Well...i thought i was wrong because reviewing your original post it seemed you wanted to fill column B, not column C.
Anyway, you are welcome and thank you for the feedback.

M.

 

[bettlejus]

Column C is just to apply the formula, in the end I will replace it with Column B.

Also Flash fill doesn't works, it only works when it has a pattern, like column C in my example, but actually my excel is composed of only column A and column B.

C was just an example so you understand where I am trying to get with the result.

 

[sheetspread]

Also Flash fill doesn't works, it only works when it has a pattern

Are you sure? Peter and AlKey tested it on interspersed blanks