?
Solved

Excel merge blank row data into top row data

Posted on 2012-09-12
11
Medium Priority
?
419 Views
Last Modified: 2012-09-17
I have an excel sheet that auto generates and the item numbers in the first cell may have blanks underneath it since the data in the following cells pertain to that same item. I need to have automatically the blank cells move the data in its row up to the cell that has an actual item number. Here is what the report looks like
initial report
I want it to do the following
what i need the report to look like
0
Comment
Question by:natevelli2
  • 5
  • 4
  • 2
11 Comments
 
LVL 11

Expert Comment

by:acseven
ID: 38392945
This is doable, but are you sure you want it like that? I ask this because you will lose the ability of filtering data properly.
If you need to just improve the way data is presented, I usually create a pivot table that organizes data a lot better. Do you want an example?
0
 

Author Comment

by:natevelli2
ID: 38393121
I will not need to filter those rows for they will be joing another worksheet where the filtering is done. Please provide me with whatever help you can in doing the first step.

Thank you
0
 
LVL 11

Accepted Solution

by:
acseven earned 2000 total points
ID: 38393244
I was doing this with a formula, but it's not finished and I got to get some sleep.
You can do it with this VBA solution, though a bit messy:
http://stackoverflow.com/questions/10934616/excel-macro-to-concatenate-multiple-rows-from-column-b-per-id-rows-in-column-a-w

ps: hopefully someone else can get there faster, sorry for the shortcut
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:xtermie
ID: 38394364
This will merge data in column A
Sub mergeA()
'Merge Column A
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet
'limit the number of iterations to the rows used to speed up process
x = ws.UsedRange.Rows.Count
For i = 2 To Cells(x + 1, 1).End(xlUp).Row
    If IsEmpty(Cells(i, 1)) Then Range(Cells(i - 1, 1), Cells(i, 1)).Merge
Next

Open in new window

0
 
LVL 18

Expert Comment

by:xtermie
ID: 38394366
I will try to post code to do the same for your other data, ie columns B, C, D and E shortly
0
 
LVL 18

Expert Comment

by:xtermie
ID: 38394734
Here is code that will merge data in column B and left as long as column A is blank  and then will merge column A too (no blanks)
Sub mergeA()
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet
'Turn off warning messages
Application.DisplayAlerts = False
'limit the number of iterations to the rows used to speed up process
x = ws.UsedRange.Rows.Count
'merge columns
f = 0
colb = ""
For k = 2 To ws.UsedRange.Columns.Count
For j = 1 To ws.UsedRange.Rows.Count
    If IsEmpty(Cells(j, 1)) Then f = 1 Else f = 0
    If f = 0 Then
        colb = ""
        colb = Cells(j, k).Value
    Else
        colb = colb & Chr(10) & Cells(j, k).Value
    End If
    If j > 1 Then
        If f = 1 Then
            Cells(j - 1, k).Value = colb
            Range(Cells(j - 1, k), Cells(j, k)).Select
            Range(Cells(j - 1, k), Cells(j, k)).Merge
        End If
        If f = 0 Then colb = Cells(j, k).Value
    End If
Next
Next
'Merge Column A
For i = 2 To Cells(x + 1, 1).End(xlUp).Row
    If IsEmpty(Cells(i, 1)) Then Range(Cells(i - 1, 1), Cells(i, 1)).Merge
Next
'Turn on warning messages
Application.DisplayAlerts = False
End Sub

Open in new window

0
 

Author Comment

by:natevelli2
ID: 38395721
It appears to merge but they still show up as different row numbers. I need to have them all in the same row number
0
 

Author Comment

by:natevelli2
ID: 38395872
I think I have something that will work but my next question is the sheet that has this script the data is pulled from the a html import so how can I make it so the macro auto-runs after the data refresh?
0
 

Author Comment

by:natevelli2
ID: 38396801
Here is what I have but the auto is not working when I open the excel workbook.
if I select each script and click run it does work however. I need this to first refresh data for the particular sheet, then clear column c, and then run the data merge


Private Sub Workbook_Open()
     'change C6 to your desired cell
     
    Range("C1:C99999").Select
    Selection.ClearContents
     
End Sub
Private Sub Make_Severely_Denormalized()
  Const HEADER_ROWS As Long = 1
  Const OUTPUT_TO_COLUMN As Long = 3
  Const DELIMITER As String = vbNewLine
  Dim A_Range As Range
  Dim B_Range As Range
  Dim A_temp As Range
  Dim B_temp As Range
  Dim B_Cell As Range
  Dim Concat As String

On Error GoTo Whoops
  Set A_Range = Range("A1").Offset(HEADER_ROWS)
  Do While Not A_Range Is Nothing
    Set B_Range = A_Range.Offset(0, 1)

    ' some helper ranges
    If A_Range.Offset(1, 0).Value = "" Then
      Set A_temp = Range(A_Range, A_Range.End(xlDown).Offset(-1, 0))
    Else
      Set A_temp = A_Range.Offset(1, 0)
    End If
    Set B_temp = Range(B_Range, B_Range.End(xlDown)).Offset(0, -1)

    ' determine how high "B" is WRT no change in "A"
    Set B_Range = Range(B_Range, B_Range.Resize( _
      Application.Intersect(A_temp, B_temp, ActiveSheet.UsedRange).Count))

    ' loop through "B" and build up the string
    Concat = ""
    For Each B_Cell In B_Range
      Concat = Concat & B_Cell.Value & DELIMITER
    Next
    Concat = Left(Concat, Len(Concat) - Len(DELIMITER))

    ' do the needful
    A_Range.Offset(0, OUTPUT_TO_COLUMN - 1).Value = Concat

    ' find the next change in "A"
    If A_Range.Offset(1, 0).Value = "" Then
      Set A_Range = Application.Intersect(A_Range.End(xlDown), ActiveSheet.UsedRange)
    Else
      Set A_Range = A_Range.Offset(1, 0)
    End If
  Loop
  Exit Sub
Whoops:
  MsgBox (Err & " " & Error)
  Stop
  Resume Next
End Sub
0
 
LVL 18

Expert Comment

by:xtermie
ID: 38405242
Hey there...the code I wrote you shows them in a single row, in different lines so they are legible.

Now the next thing you want is to for the macro to run automatically after an update in the data (which is external) or more specifically after the data is refreshed in SHEET BEFORE?
0
 

Author Comment

by:natevelli2
ID: 38406055
The code works like a champ I am just trying to figure out how to have it run for a second sheet in the workbook. It gives me an error of duplicate command.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question