How to merge these cells quickly?

[robertdino]

I have a huge bank statement in excel and this is what a few first entries look like

As you can see, the statement is converted from a PDF and the PDF had split the entries in the description column to multiple cells.

So there is 1 date column, withdrawal/deposit column, but there might be multiple cells the description is split into. I want to merge the data in the multiple vertical description cells to the 1st one so all data (DATE-DESCRIPTION-WITHDRAWAL/DEPOSIT) can be in a single row so I can import these in my account software.

I can't do this manually as the statement is too large. What would be the best method to automate this? Something that detects cells with no other cells having data in their row and then shifts the data from those cells to the cell above (merge without spacing) until it touches a cell that has other data-filled cells in its row.

I might need an expert opinion here.

 

[hyperchondriac]

Hi robertdino, if this is a one-off thing I would do a semi-automatic method as follows:

1. Insert a new column between B and C

2. Put "=B2" in the newly created C2

3. Put "=IF(A3="",C2&" "&B3,B3)" in C3

4. Copy C3 down to the bottom of the data

5. Copy column C and paste over itself as values

You will now have a list that builds up each entry, but they will not line up with the dates

6. Delete the first few cells in column C so the first fully built entry lines up with the first date

7. Use Excel's filter function to only show rows where there is a date in column A

8. Copy that data to a new sheet and/or your accounting software

How is that? If you have to do this every day then you might want something a bit more automated...

HC

 

[robertdino]

Hi robertdino, if this is a one-off thing I would do a semi-automatic method as follows:

1. Insert a new column between B and C

2. Put "=B2" in the newly created C2

3. Put "=IF(A3="",C2&" "&B3,B3)" in C3

4. Copy C3 down to the bottom of the data

5. Copy column C and paste over itself as values

You will now have a list that builds up each entry, but they will not line up with the dates

6. Delete the first few cells in column C so the first fully built entry lines up with the first date

7. Use Excel's filter function to only show rows where there is a date in column A

8. Copy that data to a new sheet and/or your accounting software

How is that? If you have to do this every day then you might want something a bit more automated...

HC

Thanks bro, but I think this can be automated.
The complete description (merged) now comes in the cell under C 'right' to the 'last split part of the description' in B.

If we can somehow make the cell in B (next to the A cells which contain dates) to retrieve data from the cells in C 'only below which the row contains a populated cell in column A', this can be automated.

What do you propose?

 

[robertdino]

An image to show how it looks like now

 

[hyperchondriac]

Yes, this can definitely all be automated. I suggested the method I did because for one-off jobs it can be quicker than writing a macro etc. It looks like you have got to step 5 in my post. Did you do steps 6-8? Also, I forgot to say that you should delete column B before you export the data.

If you are going to be doing it a lot I can look at a VBA macro solution for you.

HC

 

[Michael M]

Maybe this might do the trick

Sub MM2()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("A" & r).Value = "" Then
        Range("B" & r - 1).Value = Range("B" & r - 1).Value & " " & Range("B" & r).Value
        Rows(r).Delete
    End If
Next r
End Sub

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub FixDescriptions()
  Dim DateBlanks As Range, Ar As Range
  Set DateBlanks = Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlBlanks)
  Application.ScreenUpdating = False
  For Each Ar In DateBlanks.Areas
    Ar(1).Offset(-1, 1) = Join(Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Rows.Count + 1)))
  Next
  DateBlanks.EntireRow.Delete
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[robertdino]

Here is another macro that you can try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub FixDescriptions()
  Dim DateBlanks As Range, Ar As Range
  Set DateBlanks = Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlBlanks)
  Application.ScreenUpdating = False
  For Each Ar In DateBlanks.Areas
    Ar(1).Offset(-1, 1) = Join(Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Rows.Count + 1)))
  Next
  DateBlanks.EntireRow.Delete
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Perfect. Thank you

 

[Mike Szczesny]

Can you use something like this. I will create a very simple example. Assume your data is B1:B4. Assume my formula is in D1. My formula is =concatenate(transpose(b1:b4&" ")). Highlight the transpose(.... ) part of your formula with your mouse. Hit F9 and remove both { and }. Then you can hit enter.

[TABLE="width: 309"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]14-Jun[/TD]
[TD]Purchase[/TD]
[TD][/TD]
[TD]Purchase a bb ccc [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]bb[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ccc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]