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.
