**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hello! I am working with two columns in excel. I'd like to search Column A to find when the value changes. Then I'd like for the value of Column B to autofill down only when all the Column A values are the same.

Solved by S. D. in 26 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
25/01/2018 - 04:22

Hello!

Excelchat Expert
25/01/2018 - 04:23

Hello, I understand that you want to check column A for a value change and then auto fill column B up to that point, right?

User
25/01/2018 - 04:23

yep!

Excelchat Expert
25/01/2018 - 04:23

Can you show me an example please?

User
25/01/2018 - 04:23

can you see the document preview?

Excelchat Expert
25/01/2018 - 04:23

I do.

User
25/01/2018 - 04:23

so like in column C

User
25/01/2018 - 04:24

I want it to drop down with the first value

User
25/01/2018 - 04:24

and then like the rest

Excelchat Expert
25/01/2018 - 04:24

Okay, will the value of C1 be manually filled?

User
25/01/2018 - 04:24

yes

Excelchat Expert
25/01/2018 - 04:24

Alright, let me work on this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.

User
25/01/2018 - 04:24

okay no problem!

User
25/01/2018 - 04:24

perfect

Excelchat Expert
25/01/2018 - 04:25

May I know where will the second value of column C come from?

User
25/01/2018 - 04:25

originally?

Excelchat Expert
25/01/2018 - 04:25

How will it know what the value of the second instance in column C?

User
25/01/2018 - 04:25

in the end goal, C2 should be the same as C1

User
25/01/2018 - 04:26

so for all values in column A that are the same, Column C should have the same values

Excelchat Expert
25/01/2018 - 04:26

I meant, the formula will know what the value of C2 is based on the value of C1.

User
25/01/2018 - 04:26

yes

Excelchat Expert
25/01/2018 - 04:26

But, where does "D" come from?

Excelchat Expert
25/01/2018 - 04:27

How will Excel know what value to place if there's a change in column A value.

User
25/01/2018 - 04:27

let me show you an original sample data in column D

User
25/01/2018 - 04:28

Since we only want one value in the D column associated with the A column, and then autofill down

User
25/01/2018 - 04:28

so column C is what I'm trying to get

Excelchat Expert
25/01/2018 - 04:28

Using the sample in the Doc Preview, how will column C look like?

User
25/01/2018 - 04:29

actually the same right now

Excelchat Expert
25/01/2018 - 04:29

I'm not sure that makes sense.

Excelchat Expert
25/01/2018 - 04:29

I've updated the column A numbers so there are plenty of value changes, shouldn't column C be updated as well?

User
25/01/2018 - 04:30

by just pure coincidence, no

User
25/01/2018 - 04:30

change the numbers again

User
25/01/2018 - 04:30

and I'll show you the change

Excelchat Expert
25/01/2018 - 04:30

I just did.

User
25/01/2018 - 04:30

oh i'm sorry,

User
25/01/2018 - 04:31

dumb mistake on my park

Excelchat Expert
25/01/2018 - 04:32

What happened to "D"?

User
25/01/2018 - 04:32

the value in A3 changed, so I took the value of D3

Excelchat Expert
25/01/2018 - 04:32

Ah okay, that makes sense now.

Excelchat Expert
25/01/2018 - 04:32

Thank you. Please give me a few minutes.

User
25/01/2018 - 04:33

when there's a change in the A value, then it's supposed to take the value from D and drop it down in that column until the value of A changes again

User
25/01/2018 - 04:33

and okay!

Excelchat Expert
25/01/2018 - 04:34

The formula I wrote returns the right value other than E10 which I highlighted.

Excelchat Expert
25/01/2018 - 04:35

Is it possible that you wrote the wrong answer?

Excelchat Expert
25/01/2018 - 04:35

The value of column A did not change there so it should still be "G" instead of "A", right?

User
25/01/2018 - 04:35

yees

User
25/01/2018 - 04:35

but E was the original data

User
25/01/2018 - 04:35

you moved it over

User
25/01/2018 - 04:36

sorry thats kinda confusing

Excelchat Expert
25/01/2018 - 04:36

I did but that should not affect the logic of request.

User
25/01/2018 - 04:36

so whats in C and D is the correct answer

User
25/01/2018 - 04:36

column C* and D*

Excelchat Expert
25/01/2018 - 04:37

Oh okay.

Excelchat Expert
25/01/2018 - 04:37

So here's how it'll work.

Excelchat Expert
25/01/2018 - 04:38

You will need the reference column in column C so that the formula in column B will know what the next value is.

Excelchat Expert
25/01/2018 - 04:38

Then you can simply have this formula in column B.

User
25/01/2018 - 04:39

just to be clear, you deleted the column with sample data

User
25/01/2018 - 04:40

okay good

Excelchat Expert
25/01/2018 - 04:40

One moment.

Excelchat Expert
25/01/2018 - 04:41

Now that's better.

Excelchat Expert
25/01/2018 - 04:41

Formula for B1 is going to be different from the rest.

Excelchat Expert
25/01/2018 - 04:42

=C1

Excelchat Expert
25/01/2018 - 04:42

B2 will use this: =if(A2=A1,B1,C2)

Excelchat Expert
25/01/2018 - 04:42

Then you can just drag it downwards.

Excelchat Expert
25/01/2018 - 04:42

A gentle reminder that since you've extended the session: Please ensure that you end the session properly later or else I'll be stuck here waiting for the timer to expire and I'll be unable to help others.

User
25/01/2018 - 04:42

OKay let me just check it real quick

User
25/01/2018 - 04:45

just a quick question: how does... for example... in B11, that formula just not reference another formula from cell B10?

User
25/01/2018 - 04:45

this will work in excel just as well as Google drive righ?

Excelchat Expert
25/01/2018 - 04:45

I'm not sure what you meant by your first question but yes, it will work with Excel. You can download the file as an Excel file as well.

User
25/01/2018 - 04:46

oh fantastic

User
25/01/2018 - 04:46

I'll definitely do that

Excelchat Expert
25/01/2018 - 04:46

Would there be anything else that I can help you with regards to the original question?

User
25/01/2018 - 04:46

No

Excelchat Expert
25/01/2018 - 04:46

Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)

User
25/01/2018 - 04:47

but now I can't even see the spreadsheet

Excelchat Expert
25/01/2018 - 04:47

I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of. :)

Excelchat Expert
25/01/2018 - 04:47

Give me a few minutes I

Excelchat Expert
25/01/2018 - 04:47

'll send it to you.

User
25/01/2018 - 04:47

terrific! and of course!

Excelchat Expert
25/01/2018 - 04:47

[Uploaded an Excel file]

Excelchat Expert
25/01/2018 - 04:47

Please download that file. It'll be in Excel format as well.

User
25/01/2018 - 04:48

thank you so much

Excelchat Expert
25/01/2018 - 04:48

Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

Excelchat Expert
25/01/2018 - 04:48

Thank you for contacting Got It Pro. Have an awesome day!

User
25/01/2018 - 04:48

goodbye

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*