Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Finding rows with a blank first cell and concatenating other cells in the row with the cell above

    Excel semi-novice here, but lots of VBA for Word experience.

    I have a situation where an Excel data query from a data source results in some lines that start with a blank cell but contain information in some cells to the right that should be concatenated with the data in the cell in the row above. There are often more than one of these start-blank rows in sequence. They're the result of extracting rich text full of HTML codes from the data source. We can't just forbid the users from using rich text.

    It would look like this. The need is to make colC in the second row contain the data for rows 1-3 concatenated, and to then delete rows 2 to 3. I could also copy the fixed-up rows to another location.

    ColA ColB ColC ColD
    Data data data data
    (blnk) data
    (blnk) data
    Data data data data

    I can imaging slogging through from the bottom of the range, checking each row for an initial blank cell, then finding the cell with data and concatenating it with the data in the row above, and then deleting the bad row. Or deleting in a second pass.

    Is there a formula (an array formula perhaps?) that would help with this so I don't have to write a macro, or is there a more elegant approach I'm not thinking of?

    Many thanks,

    Jessica Weissman
    Last edited by jweissmn1; 2015-11-17 at 13:09.

  2. #2
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    I'll take it that there's no better solution than manually iterating upward. Or is the question insufficiently clear?

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    Short of writing a macro I can't see a way to do it. Even parsing the data before entering it in cells requires a macro, and complicates the import process.

    cheers, Paul

  4. The Following User Says Thank You to Paul T For This Useful Post:

    jweissmn1 (2015-11-25)

  5. #4
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Thanks for the confirmation. I was hoping for some kind of copy/filter magic. We have no control over how the stuff gets imported, alas, because it is coming from a third-party tool.

  6. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    I'm sure someone here will scratch something up for you (I don't have Excel to test).

    cheers, Paul

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jessica,

    Can you post a test file?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    I'll have to sanitize it a bit, so will have it for you on Friday.

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    jweissmn1,

    This will do what I think you are trying to achieve. Rows that start with a blank cell will concatenate with the full row above it. In this sample I have provided various configurations with the number of lines and the amount of values on each line. I am assuming that when you say concatenate you mean to move the cells in a row; not to concatenate all the values in one cell.

    Prior to code run:
    concat1.png

    After code run:
    concat2.png

    Below is the code well documented.

    In a Standard module:
    Code:
    Public Sub ConcatRows()
    '-------------------------------------
    'DECLARE AND SET VARIABLES
    Dim s(), I As Long, LastRow As Long, LastCol As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row 'LAST ROW ON SHEET
    '-------------------------------------
    'FIND ABNORMAL ROWS AND POPULATE ARRAY
    For I = 2 To LastRow
    '-------------------------------------
    'NORMAL ROW
        If Cells(I, 1) <> "" Then 'NORMAL ROW
            Row = I  'REMEMBER NORMAL ROW NUMBER
    '-------------------------------------
    'ABNORMAL ROW
        Else:
            LastCol = ActiveSheet.Cells(I, Application.Columns.Count).End(xlToLeft).Column 'LAST COL ABN ROW
            ReDim Preserve s(LastCol - 2)  'REDIM S BY THE NUMBER OF ITEMS IN ABN ROW
            For J = 2 To LastCol
                s(J - 2) = Cells(I, J) 'ASSIGN ARRAY TO ROW ITEMS
            Next
    '-------------------------------------
    'SHIFT VALUES TO NORMAL ROW
            NextCol = ActiveSheet.Cells(Row, Application.Columns.Count).End(xlToLeft).Column + 1 'LAST COL OF LAST NORMAL ROW
            For J = 0 To UBound(s)
                Cells(Row, NextCol) = s(J) 'CONCATENATE ABN ROW TO NORMAL ROW
                NextCol = NextCol + 1  'INCREMENT TO NEXT COLUMN
            Next J
        End If
    Next I
    '-------------------------------------
    'DELETE BLANK ROWS
    For I = LastRow To 2 Step -1
        If Cells(I, 1) = "" Then Cells(I, 1).EntireRow.Delete 'DELETE ABN ROWS
    Next I
    '-------------------------------------
    'CLEANUP
    Erase s()
    End Sub
    HTH,
    Maud
    Last edited by Maudibe; 2015-11-25 at 17:50.

  10. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    My reading was that only Column C gets the data from the abnormal rows, concatenated. Whether it's straight concatenation or space delimited I don't know.

    cheers, Paul

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Not sure exactly either. Just took a stab at it

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    jweissmn1 (2015-11-27)

  13. #11
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    English is amazingly slippery, isn't it? Your code is a great direction-finder, Maud, and I can probably get from what you have to what I need. The relevant fields are text, and I need to pull the text from the lines beginning with blank cells into the nearest non-blank row above, by column. In the sample I've attached only one column (project scope) needs upward concatenation, but in real life more of them might. In any case, the problem is signaled by a blank in the first relevant column (the Master Project column).

    Concatenation needs a space in between.

    I've attached a sanitized sample file. The sheet named SummaryList is what I get from our external data source. What I want to get as a result is shown in SummaryList Desired.

    Many thanks.

    Program Summary Status Report Sanitized.xlsm

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    jw,

    Thank you for clarifying what you wanted to achieve.

    Here is your revised workbook with the code to convert the SummaryList sheet to the SummaryListDesred sheet as you specified. I added additional values in several columns for testing. The code assumes that you will have a header in column B called Master Project. It does not matter which row as long as it is the header. Click the Run Code button to format the sheet and the Reset button to revert back to the original unformatted data.

    HTH,
    Maud
    Attached Files Attached Files

  15. The Following User Says Thank You to Maudibe For This Useful Post:

    jweissmn1 (2015-11-30)

  16. #13
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •