VBA how to transform the data unmerging cells, deleting empty rows and organising it properly.

Try this

Option Explicit

Sub FormatData()
    With Sheets("Current")
        .Cells.UnMerge
        .Cells.Font.Size = "11"
        On Error Resume Next
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
        Cells.RowHeight = 15
    End With
End Sub

Select allOpen in new window

Are you familiar with  macros? Do you know where to copy the code to?

Hi,

Try below:

Sub FormatSheet()
Dim CurrentWs As Worksheet, AfterWs As Worksheet
Dim AfterShName As String
Dim i As Long
Dim LR As Long

With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
'Delete the sheet "After Macro Ran" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("After Macro Ran").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set CurrentWs = Worksheets("Current")

'Add a worksheet with the name "After Macro Ran"
AfterShName = "After Macro Ran"
Application.CopyObjectsWithCells = False
CurrentWs.Copy After:=CurrentWs
ActiveSheet.Name = AfterShName
Application.CopyObjectsWithCells = True

Set AfterWs = Worksheets("After Macro Ran")
LR = AfterWs.Range("B" & Rows.Count).End(xlUp).Row
With AfterWs.Range("A2:B" & LR)
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With AfterWs.Range("B2:B" & LR)
    .TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End With
Application.DisplayAlerts = False
With AfterWs.Range("C2:C" & LR)
    .TextToColumns Destination:=Range("C2"), DataType:=xlFixedWidth, _
        OtherChar:=":", FieldInfo:=Array(Array(0, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
End With
Application.DisplayAlerts = True
AfterWs.Columns("C:C").Delete
AfterWs.Range("B1").Value = "Type"
AfterWs.Range("C1").Value = "Description"
For i = LR To 1 Step -1
    If AfterWs.Cells(i, 1) = "" And AfterWs.Cells(i, 2) = "" Then
        AfterWs.Rows(i).Delete
    End If
Next i
With AfterWs.Rows("1:1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With AfterWs.Rows("1:1").Font
    .Name = "Trebuchet MS"
    .Size = 14
    .Bold = True
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
With AfterWs.Range("A1:C1").Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.599993896298105
    .PatternTintAndShade = 0
End With
With AfterWs.Range("A2:C" & LR).Font
    .Name = "Calibri"
    .Size = 11
    .Bold = False
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
AfterWs.Range("A2:C" & LR).EntireRow.AutoFit
AfterWs.Range("A2").Activate
ActiveWindow.FreezePanes = True
AfterWs.Columns("B:C").ColumnWidth = 65
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

In attached press Format Data...
Hope this helps
EE_v2.xlsm

thanks Shams. you nailed it.   almost close.

one small issue is left there. just look at the row 34 after running the macro. it will leave this text In Excel 2007, this is a Statistical function.
while this text belongs to the BETADIST function of row 33 and it should be appended to the description of row 33 and then that empty entry of row 34 should be deleted.

i have attached the example.  you can see that red text appended with the existing desc line of row 33 and then that row which has no value in Column A to be deleted.  i highlighted the row in yellow.
EE.xlsb

Yes I can see that, but in Current sheet its appearing in Column B, it would be difficult to move to Column C after the text. Let me try

thank you Shams

thank you very much Shams.

You're Welcome! Glad I was able to help.

a quick question, where do i start as a beginner to learn macros, so that i would be able to write VBA code like you?
any tip?  where did you learn VBA?

You will learn eventually first create logic and transform into Excel VBA language, if you stumble then google. This is how I learned. :)