Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 25/01/2018 - 04:47
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.