Changing format of data

[shreeram.maroo]

Hi ,

I have attached a file, basically i want to present the data provided in base sheet tab in the format presented in result tab. Can anyone suggest me a way out.

I need to keep only values (after removing all blank and zero values) for each column.

Regards
Shreeram

[HansV]

Here is a macro:

Code: Select all

Sub CombineColumns()
    Dim wshBase As Worksheet
    Dim wshResult As Worksheet
    Dim col As Long
    Dim rng As Range
    Dim r As Long
    Dim Letter As String
    Application.ScreenUpdating = False
    Set wshBase = Worksheets("Base sheet")
    Set wshResult = Worksheets.Add(After:=wshBase)
    wshResult.Range("A1:C1").Value = Array("Letter", "Code", "Amount")
    r = 1
    For col = 2 To 5
        Letter = wshBase.Cells(1, col).Value
        With wshBase.Columns(col)
            Set rng = .Find(What:="*", After:=wshBase.Cells(1, col))
            If Not rng Is Nothing Then
                Do
                    If rng.Value <> 0 Then
                        r = r + 1
                        wshResult.Cells(r, 1).Resize(1, 3).Value = Array(Letter, wshBase.Cells(rng.Row, 1).Value, rng.Value)
                    End If
                    Set rng = .FindNext(After:=rng)
                    If rng Is Nothing Then Exit Do
                Loop Until rng.Row = 1
            End If
        End With
    Next col
    Application.ScreenUpdating = True
End Sub

[shreeram.maroo]

Thanks Hans, this is very helpful and saves lots of efforts.

[p45cal]

Power Query in attached.
Right-click the table at cell E3 of the Results tab of the attached and choose Refresh (after deleting some of the data in that table or after changing the table on the Base sheet (by changing the data and/or changing the extent of the table).