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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
71
Peter_SSs
Peter_SSs
P
  • Solved
Split rows separated by blank row and create multiple files
Replies
8
Views
161
Kaper
Kaper
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
81
Peter_SSs
Peter_SSs
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
300
mumps
mumps
A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
220
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,886
Messages
6,175,195
Members
452,616
Latest member
intern444

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