Unconsolidate Rows in Excel 2016

K

kevinmcgarry90

New Member
Joined
Jan 2, 2018
Messages
3
Hello Gurus,

I am currently trying to break oneline summaries into many rows. Basically, on one tab, I have one line for each subject. This line says how many events that subject had. I want to have another tab that creates rows for each subject and event. The desired look is below, I just didn't extrapolate the values all the way down to save space. I'd like it to automatically move from one subject to the next when the previous subject is out of lines to make. So basically, performing the exact opposite function is easy yet I can't figure out how to get it to unconsolidated.

Original Table
[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]WELL #[/TD]
[TD="width: 64, bgcolor: transparent"]# STAGES[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
</tbody>[/TABLE]

Desired Output on Different Tab
[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]WELL[/TD]
[TD="width: 64, bgcolor: transparent"]STAGE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for the help!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hi & welcome to the board.
Is this what you're after
Code: 
Sub UnconsolidateData()

   Dim Cl As Range
   Dim UsdRws As Long
   
   
   With Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
      .Range("A1:B1").Value = Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A1:B1").Value
      For Each Cl In Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A2", Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp))
         UsdRws = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
         .Range("A" & UsdRws).Resize(1 * Cl.Offset(, 1)).Value = Cl.Value
         .Range("B" & UsdRws).Value = 1
         .Range("B" & UsdRws).AutoFill Range("B" & UsdRws).Resize(Cl.Offset(, 1)), xlFillSeries
      Next Cl
   End With

End Sub
Change sheet names to suit
 
Upvote 0
Fluff said:
Hi & welcome to the board.
Is this what you're after
Code: 
Sub UnconsolidateData()

   Dim Cl As Range
   Dim UsdRws As Long
   
   
   With Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
      .Range("A1:B1").Value = Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A1:B1").Value
      For Each Cl In Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A2", Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("A" & Rows.Count).End(xlUp))
         UsdRws = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
         .Range("A" & UsdRws).Resize(1 * Cl.Offset(, 1)).Value = Cl.Value
         .Range("B" & UsdRws).Value = 1
         .Range("B" & UsdRws).AutoFill Range("B" & UsdRws).Resize(Cl.Offset(, 1)), xlFillSeries
      Next Cl
   End With

End Sub
Change sheet names to suit
Click to expand...


Thank you! This is very close! It does exactly what I want it to do if my data was in those ranges. As such, my sheet1 data begins on C3:D3 and my sheet2 area begins on C14:D14. When I modify the code to accommodate these changes, it doesn't work for the first well value. It just returns one line for the first subject and then begins the count for the rest of the subjects. Do you know how I could modify to prevent this?
[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]well[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]stage[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this
Code: 
Sub UnconsolidateData()

   Dim Cl As Range
   Dim UsdRws As Long
   
   With Sheets("Sheet2")
      .Range("C14:D14").Value = Sheets("Sheet1").Range("C3:D3").Value
      For Each Cl In Sheets("Sheet1").Range("C4", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp))
         UsdRws = .Range("C" & Rows.Count).End(xlUp).Offset(1).Row
         .Range("C" & UsdRws).Resize(1 * Cl.Offset(, 1)).Value = Cl.Value
         .Range("D" & UsdRws).Value = 1
         If Cl.Offset(, 1) > 1 Then
            .Range("D" & UsdRws).AutoFill .Range("D" & UsdRws).Resize(Cl.Offset(, 1)), xlFillSeries
         End If
      Next Cl
   End With

End Sub
 
Upvote 0
OK, it wasn't working because I didn't think to exclude the title blocks from the row counting. After playing with it, this is the final code that does exactly what I was hoping for. Thank you so much!!

Sub UnconsolidateData()
Dim Cl As Range
Dim UsdRws As Long

With Sheets("Sheet2")
.Range("C13:D13").Value = Sheets("Sheet1").Range("C2:D2").Value
For Each Cl In Sheets("Sheet1").Range("C3", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp))
UsdRws = .Range("C" & Rows.Count).End(xlUp).Offset(1).Row
.Range("C" & UsdRws).Resize(1 * Cl.Offset(, 1)).Value = Cl.Value
.Range("D" & UsdRws).Value = 1
If Cl.Offset(, 1) > 1 Then
.Range("D" & UsdRws).AutoFill .Range("D" & UsdRws).Resize(Cl.Offset(, 1)), xlFillSeries
End If
Next Cl
End With
End Sub
 
Upvote 0
Glad you got it working & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
666
ItalianPlatinum
I
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
72
Peter_SSs
Peter_SSs
P
  • Solved
Split rows separated by blank row and create multiple files
Replies
8
Views
175
Kaper
Kaper
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
426
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

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