Update cell based on a parent value

rf7clan

New Member
Joined
Jun 25, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
Hello All! Let's say I have a spreadsheet that has 10 rows for a company (column A) and contains rows for 100 companies. In column B, there may be a fiscal year value but only in one row for a company. I want to update column B with that fiscal year value for all the rows for that company.

Simple example (all ABC Co. col B will be set to 2020 and all GHI Co. col B will be set to 2021):
A B
Row 1 ABC Co.
Row 2 ABC Co. 2020
Row 3 DEF Co.
Row 4 DEF Co.
Row 5 GHI Co. 2021
Row 6 GHI Co.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
So, it sounds like you just want to fill the blank cells in column B with the value from the cell above.
That can be done quickly and easily using the trick shown here: Fill Blanks With Value Above
 
Upvote 0
So, it sounds like you just want to fill the blank cells in column B with the value from the cell above.
That can be done quickly and easily using the trick shown here: Fill Blanks With Value Above
No as that was just a very small example. ABC Co. could have 100 rows in the spreadsheet with only one of those rows populated with 2020 where requirement is then to populate the other 99 rows with 2020.
 
Upvote 0
No as that was just a very small example. ABC Co. could have 100 rows in the spreadsheet with only one of those rows populated with 2020 where requirement is then to populate the other 99 rows with 2020.
Is there any consistency reagrding WHICH record may have a value on it?
Is it always the first record for that company?
If so It doesn't matter how many rows there may be per company, whether it is 2 or 100. As long as the FIRST one for a company is populated, it will populate ALL the blank ones under it with that value.

If it is not always the first record that is populated for a company, can you sort the data first?
Then this method would work.
 
Upvote 0
Is there any consistency reagrding WHICH record may have a value on it?
Is it always the first record for that company?
If so It doesn't matter how many rows there may be per company, whether it is 2 or 100. As long as the FIRST one for a company is populated, it will populate ALL the blank ones under it with that value.

If it is not always the first record that is populated for a company, can you sort the data first?
Then this method would work.
Is there any consistency reagrding WHICH record may have a value on it? No
Is it always the first record for that company? No

If it is not always the first record that is populated for a company, can you sort the data first? Possible option. On sample, I did filter, copy to another sheet filter values, then did VLOOKUP to populate cells. Just feel macro would be best method but not sure how to write the section which would do the break on column A.
 
Upvote 0
You can do that it VBA.
The steps I pointed out can all be automated, and a lot of the needed code can be obtained simply by using the Macro Recorder.
You would just do the following steps:
1. Turn on the Macro Recorder
2. Sort your data
3. Peform the steps outlined in the article to fill in the blank cells
4. Stop the Macro Recorder

Then, usually a little clean-up of the code is required to make it more dynamic.
You can try this yourself, and then post the code here, and we can help you clean it up.

Or you can answer these questions for us:
1. Is there a header/title row?
2. I know you are only concerned about columns A and B, but how many total column of data are there (so we can make that all are included in the sorting)?
 
Upvote 0
You can do that it VBA.
The steps I pointed out can all be automated, and a lot of the needed code can be obtained simply by using the Macro Recorder.
You would just do the following steps:
1. Turn on the Macro Recorder
2. Sort your data
3. Peform the steps outlined in the article to fill in the blank cells
4. Stop the Macro Recorder

Then, usually a little clean-up of the code is required to make it more dynamic.
You can try this yourself, and then post the code here, and we can help you clean it up.

Or you can answer these questions for us:
1. Is there a header/title row?
2. I know you are only concerned about columns A and B, but how many total column of data are there (so we can make that all are included in the sorting)?
Is there a header/title row? Yes
Spreadsheet could have thousands of company names (col A).
 
Upvote 0
Spreadsheet could have thousands of company names (col A).
That is not what I asked.
2. I know you are only concerned about columns A and B, but how many total column of data are there (so we can make that all are included in the sorting)?
I want to know how many COLUMNS, not ROWS of data there are.
 
Upvote 0
Here is code that will do what you need, though it may need editing:
VBA Code:
Sub MyFillMacro()

    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Sort data by columns A and B
    Range("A1:B" & lr).Sort _
        key1:=Range("A1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, Header:=xlYes

'   Find blanks in column B and populate with formula
    Range("B1:B" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    
    Application.ScreenUpdating = True
    
End Sub

You may need to change the letter "B" here:
Rich (BB code):
    Range("A1:B" & lr).Sort _
to whatever column is the last column with company data (otherwise, only columns A and B will be sorted and the others won't, messing up your data).
 
Upvote 0
Tried your previous recommendation and changed the 2nd company values that had no date to the value of the first company fiscal year. Will give macro a shot. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top