Convert hierarchy in Excel and fill down to create repeated rows

A

aston_007

New Member
Joined
Apr 7, 2023
Messages
6
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hi Experts.

Hoping you'll be able to help please.

I have a requirement to 'fill down' the contents of the columns to the next filled cell.
I would ordinarily do this by hand, but I have thousand of rows and multiple columns in my original worksheet.

Below is an image showing an example of the original file with a second Fill Down sheet showing what I'd ideally like please so that the receiving system can recognise the full records rather than 'empty cells'
The best of luck guys and thanks very much!

Mark
 

Attachments

  • Levels_Fill_Down.gif
    Levels_Fill_Down.gif
    110.6 KB · Views: 8

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
This is what I did. I added the formula in Column A. Then, I selected B2:I29, hit F5, then selected blanks. After the blanks were selected, I entered this formula...

Excel Formula: 
=IF(A2<>A1,"",B1)

After typing in the formula, hold CTRL and hit ENTER.

aston
ABCDEFGHI
1Parent8Parent7Parent6Parent5Parent4Parent3Parent2Parent1Child
2TOTAL        
3TOTALGNDTOTAL       
4TOTALGNDTOTALSTNETEXP      
5TOTALGNDTOTALSTNETEXP40000000     
6TOTALGNDTOTALSTNETEXP40000000OPSICOM    
7TOTALGNDTOTALSTNETEXP40000000OPSICOM44800000   
8TOTALGNDTOTALSTNETEXP40000000OPSICOM4480000044810000  
9TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000 
10TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110002
11TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110003
12TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110004
13TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110005
14TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110006
15TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110007
16TOTALGNDTOTALSTNETEXP40000000OPSICOM44700000   
17TOTALGNDTOTALSTNETEXP40000000OPSICOM4470000044710000  
18TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000 
19TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140001
20TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140002
21TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140003
22TOTALADDLINFO       
23TOTALADDLINFO90000000      
24TOTALADDLINFO90000000BUDADINF     
25TOTALADDLINFO90000000BUDADINF91000000    
26TOTALADDLINFO90000000BUDADINF9100000091800000   
27TOTALADDLINFO90000000BUDADINF910000009180000091810000  
28TOTALADDLINFO90000000BUDADINF9100000091800000918100009181000 
29TOTALADDLINFO90000000BUDADINF9100000091800000918100009181000918110001
Data
Cell Formulas
RangeFormula
B2,B29:H29,I22:I28,B28:G28,H19:H27,B27:F27,G18:G26,B26:E26,F17:F25,B25:D25,E7:E24,B24:C24,D6:D23,B23,C5:C22,B4:B21,I16:I18,H10:H17,G9:G16,F8:F15,I2:I9,H2:H8,G2:G7,F2:F6,E2:E5,D2:D4,C2:C3B2=IF(A2<>A1,"",B1)
A3:A29A3=A2
 
Upvote 0
lrobbo314 said:
This is what I did. I added the formula in Column A. Then, I selected B2:I29, hit F5, then selected blanks. After the blanks were selected, I entered this formula...

Excel Formula: 
=IF(A2<>A1,"",B1)

After typing in the formula, hold CTRL and hit ENTER.

aston
ABCDEFGHI
1Parent8Parent7Parent6Parent5Parent4Parent3Parent2Parent1Child
2TOTAL        
3TOTALGNDTOTAL       
4TOTALGNDTOTALSTNETEXP      
5TOTALGNDTOTALSTNETEXP40000000     
6TOTALGNDTOTALSTNETEXP40000000OPSICOM    
7TOTALGNDTOTALSTNETEXP40000000OPSICOM44800000   
8TOTALGNDTOTALSTNETEXP40000000OPSICOM4480000044810000  
9TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000 
10TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110002
11TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110003
12TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110004
13TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110005
14TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110006
15TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110007
16TOTALGNDTOTALSTNETEXP40000000OPSICOM44700000   
17TOTALGNDTOTALSTNETEXP40000000OPSICOM4470000044710000  
18TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000 
19TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140001
20TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140002
21TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140003
22TOTALADDLINFO       
23TOTALADDLINFO90000000      
24TOTALADDLINFO90000000BUDADINF     
25TOTALADDLINFO90000000BUDADINF91000000    
26TOTALADDLINFO90000000BUDADINF9100000091800000   
27TOTALADDLINFO90000000BUDADINF910000009180000091810000  
28TOTALADDLINFO90000000BUDADINF9100000091800000918100009181000 
29TOTALADDLINFO90000000BUDADINF9100000091800000918100009181000918110001
Data
Cell Formulas
RangeFormula
B2,B29:H29,I22:I28,B28:G28,H19:H27,B27:F27,G18:G26,B26:E26,F17:F25,B25:D25,E7:E24,B24:C24,D6:D23,B23,C5:C22,B4:B21,I16:I18,H10:H17,G9:G16,F8:F15,I2:I9,H2:H8,G2:G7,F2:F6,E2:E5,D2:D4,C2:C3B2=IF(A2<>A1,"",B1)
A3:A29A3=A2
Click to expand...
Hi. Thank you so much for replying. Much appreciated. I'll give this a go.
I'm left wondering if there is a VBA solution - only because I need to hand this over to a team who needs little instruction without formulae.

Thanks again!
Mark
 
Upvote 0
This does the same in VBA. I'm setting the LR variable by using column I since it is the column with the value at the bottom of the dataset. If that column isn't always the one with the value at the bottom of the dataset it won't work properly.

I'm also resizing the range to expand 7 columns to the right. Again, if this sample isn't entirely indicative of the structure of the data things can fall apart.

Other than that, this produces the same results.

VBA Code: 
Sub aston()
Dim LR As Long:     LR = Range("I" & Rows.Count).End(xlUp).Row
Dim r As Range:     Set r = Range("A1:A" & LR)

r.SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=R[-1]C"
Set r = r.Offset(, 1).Resize(, 7)
r.SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=IF(RC[-1]<>R[-1]C[-1],"""",R[-1]C)"

End Sub
 
Upvote 0
lrobbo314 said:
This is what I did. I added the formula in Column A. Then, I selected B2:I29, hit F5, then selected blanks. After the blanks were selected, I entered this formula...

Excel Formula: 
=IF(A2<>A1,"",B1)

After typing in the formula, hold CTRL and hit ENTER.

aston
ABCDEFGHI
1Parent8Parent7Parent6Parent5Parent4Parent3Parent2Parent1Child
2TOTAL        
3TOTALGNDTOTAL       
4TOTALGNDTOTALSTNETEXP      
5TOTALGNDTOTALSTNETEXP40000000     
6TOTALGNDTOTALSTNETEXP40000000OPSICOM    
7TOTALGNDTOTALSTNETEXP40000000OPSICOM44800000   
8TOTALGNDTOTALSTNETEXP40000000OPSICOM4480000044810000  
9TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000 
10TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110002
11TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110003
12TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110004
13TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110005
14TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110006
15TOTALGNDTOTALSTNETEXP40000000OPSICOM448000004481000044811000448110007
16TOTALGNDTOTALSTNETEXP40000000OPSICOM44700000   
17TOTALGNDTOTALSTNETEXP40000000OPSICOM4470000044710000  
18TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000 
19TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140001
20TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140002
21TOTALGNDTOTALSTNETEXP40000000OPSICOM447000004471000044714000447140003
22TOTALADDLINFO       
23TOTALADDLINFO90000000      
24TOTALADDLINFO90000000BUDADINF     
25TOTALADDLINFO90000000BUDADINF91000000    
26TOTALADDLINFO90000000BUDADINF9100000091800000   
27TOTALADDLINFO90000000BUDADINF910000009180000091810000  
28TOTALADDLINFO90000000BUDADINF9100000091800000918100009181000 
29TOTALADDLINFO90000000BUDADINF9100000091800000918100009181000918110001
Data
Cell Formulas
RangeFormula
B2,B29:H29,I22:I28,B28:G28,H19:H27,B27:F27,G18:G26,B26:E26,F17:F25,B25:D25,E7:E24,B24:C24,D6:D23,B23,C5:C22,B4:B21,I16:I18,H10:H17,G9:G16,F8:F15,I2:I9,H2:H8,G2:G7,F2:F6,E2:E5,D2:D4,C2:C3B2=IF(A2<>A1,"",B1)
A3:A29A3=A2
Click to expand...
Hi. Thank you so much for replying. Much appreciated. I'll give this a go.
I'm left wondering if there is a VBA solution - only because I need to hand this over to a team who needs little instruction without formulae.

Thanks again!
Mark
lrobbo314 said:
Sub aston() Dim LR As Long: LR = Range("I" & Rows.Count).End(xlUp).Row Dim r As Range: Set r = Range("A1:A" & LR) r.SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=R[-1]C" Set r = r.Offset(, 1).Resize(, 7) r.SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=IF(RC[-1]<>R[-1]C[-1],"""",R[-1]C)" End Sub
Click to expand...
That's absolutely amazing! How did you do that so quickly?
That's worked so well. Thank you so much. That'll save me countless hours.

Kind regards,
Mark
 
Upvote 0
Thanks! I've been doing this a long time.

Also, I had a thought.

If you want to get around some of those limitations I mentioned in the last post, this version prompts the user to select the range, then it will fill everything in based on that.

VBA Code: 
Sub aston2()
Dim r As Range:     Set r = Application.InputBox(Prompt:="Select Range", Type:=8)

r.Columns(1).SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=R[-1]C"
r.Offset(, 1).Resize(, r.Columns.Count - 1).SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=IF(RC[-1]<>R[-1]C[-1],"""",R[-1]C)"
End Sub
 
Upvote 0
lrobbo314 said:
Thanks! I've been doing this a long time.

Also, I had a thought.

If you want to get around some of those limitations I mentioned in the last post, this version prompts the user to select the range, then it will fill everything in based on that.

VBA Code: 
Sub aston2()
Dim r As Range:     Set r = Application.InputBox(Prompt:="Select Range", Type:=8)

r.Columns(1).SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=R[-1]C"
r.Offset(, 1).Resize(, r.Columns.Count - 1).SpecialCells(xlCellTypeBlanks).Formula2R1C1 = "=IF(RC[-1]<>R[-1]C[-1],"""",R[-1]C)"
End Sub
Click to expand...
Amazing! I could have lived with the limitations, but this is even better.
Thanks again for your time on this.
Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Creating a hierarchy in excel
Replies
4
Views
418
safniki
S
M
  • Solved
How to sum a row of data with some cells containing an extra character
2
Replies
10
Views
704
Fluff
Fluff
A
  • Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
531
AlfredDB
A
F
  • Question
Excel Macro Assistance Needed for Staff Roster and Training Allocation
Replies
0
Views
309
FajnaAli
F
E
  • Question
Data Filtering – Help needed with some nervous Excel users!
Replies
9
Views
752
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

Share this page

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