Data Merge if data comes in different cell

[harinsh]

Hi Team,

Looking for one function or macro in order to identify the blank cell (in sl.no column) if yes then it should merge the two given cells.

I have data which is extract from PDF and in A1 column I have serial number the challenge here some cells divided in two different cells (down cell) for example if cell A10 contains serial number 10 and B10 contains text and some portion of B10 text will comes in B11 however serial number will not come.

I would like to merge the below continuation cell to above one. Here only condition is serial number for continuation cell does not has serial number.

example:
A1 B1
1 Computer Hardware &
Software
2 Microsoft Windows &
Microsoft MS Office

output:
A1 B1
1 Computer Hardware & Software
2 Microsoft Windows & Microsoft MS Office

Please not that I have rows around 800 to 1500 and most of the cells extracts in single cell. I want to make single cells those were not extracted properly.

Kindly let me know if you need further clarification.

 

[patel45]

Sub a()
r = 1
Do While Cells(r, 1) <> ""
  Cells(r, 1).Value = Cells(r, 1).Value & Cells(r + 1, 1).Value
  Rows(r + 1).Delete
  r = r + 1
Loop
End Sub

 

[harinsh]

Thanks for your code, I'm really not understanding my am I deleting the row here....I want to merge the cells (down cell) and also this code changing the my Serial number which is not my requirement.

 

[patel45]

need a sample file with data and desired result, try this

Sub a()
r = 1
Do While Cells(r, 1) <> ""
  If Val(Left(Cells(r + 1, 1).Value, 1)) = 0 Then
    Cells(r, 1).Value = Cells(r, 1).Value & Cells(r + 1, 1).Value
    Rows(r + 1).Delete
  End If
    r = r + 1
Loop
End Sub

 

[harinsh]

Thank you very much ...it is working fine now....just one thing need to add ...I would like to merge one blank space along with data because data getting paste next to text without blank space...How can I do it?

 

[patel45]

Sub a()
r = 1
Do While Cells(r, 1) <> ""
  If Val(Left(Cells(r + 1, 1).Value, 1)) = 0 Then
    Cells(r, 1).Value = Cells(r, 1).Value [COLOR=#ff0000]& " " &[/COLOR] Cells(r + 1, 1).Value
    Rows(r + 1).Delete
  End If
    r = r + 1
Loop
End Sub

 

[harinsh]

Perfect, its working fine now...Than you very much