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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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

rf7clan

New Member
Joined
Jun 25, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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

rf7clan

New Member
Joined
Jun 25, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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

rf7clan

New Member
Joined
Jun 25, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
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

rf7clan

New Member
Joined
Jun 25, 2020
Messages
13
Office Version
  1. 2007
Platform
  1. Windows
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,186,745
Messages
5,959,515
Members
438,429
Latest member
Ekana99

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
Top