Edit code to get values of all columns

[RAJESH1960]

Hello experts
With the help of Bebo02's code, I have this sheet 2B which is converted to a new sheet PORTAL with the same headings and values of selected columns. of which values of some are edited. Earlier, I had left some columns blank in Portal sheet, but as they are also needed now, I tried to edit the code to get the values of all the columns in PORTAL, but I was not able to edit the code and get the result. One more issue is that I want the date columns to be converted in portal sheet to dd-mm-yyyy format. I need your help to edit the code accordingly.
I hope Bebo02 is watching this thread.
Get Data of All columns.xlsm

 

[RAJESH1960]

Sorry. Posted the wrong expected result sheet. This is the correct sheet. Please run the code to get the portal sheet.

 

[RAJESH1960]

Get Data of All columns.xlsm

 

[johnnyL]

First swing:

Option Explicit
Sub Convert_2B_POrtal()

On Error Resume Next                                                                            '
    Worksheets("2B").Activate                                                                   '
'
    If Err.Number = 9 Then
        MsgBox ("CHECK THE NAME OF THE SHEET..." & vbLf & _
                "1. If the data is 2B rename sheet as 2B ." & vbLf & _
                "2. If the data is 2A rename sheet as PORTAL.")                                 '
        Exit Sub                                                                                '
    End If
'
    Dim lr&, k&, j&, id As String, item As String, cell As Range, s, key, arr(), ws As Worksheet
    Dim dic As Object
'
    Set dic = CreateObject("scripting.dictionary")
'
    Application.ScreenUpdating = False                                                          '
'
    Application.DisplayAlerts = False                                                           '
    For Each ws In Sheets                                                                       '
        If ws.Name = "PORTAL" Then ws.Delete                                                    '   delete previous version of sheet PORTAL
    Next                                                                                        '
    Application.DisplayAlerts = True
'
    Worksheets("2B").Activate                                                                   '
'
    lr = Cells(Rows.Count, "A").End(xlUp).Row                                                   '
'
    For Each cell In Range("A7:A" & lr)                                                         '
        id = cell & "|" & cell.Offset(0, 1) & "|" & cell.Offset(0, 2)                           ' column A&B&C combination
'
        item = cell.Offset(0, 3) & "|" & cell.Offset(0, 4) & "|" & cell.Offset(0, 5) & "|" & _
                cell.Offset(0, 6) & "|" & cell.Offset(0, 7) & "|" & cell.Offset(0, 8) & "|" & _
                cell.Offset(0, 9) & "|" & cell.Offset(0, 10) & "|" & cell.Offset(0, 11) & "|" & _
                cell.Offset(0, 12) & "|" & cell.Offset(0, 13) & "|" & cell.Offset(0, 14) & "|" & _
                cell.Offset(0, 15) & "|" & cell.Offset(0, 16) & "|" & cell.Offset(0, 18)        '
'
        If Not dic.exists(id) Then                                                              '
            dic.add id, item                                                                    '
        Else                                                                                    '
            s = Split(dic(id), "|")                                                             '
            dic(id) = s(0) & "|" & s(1) & "|" & s(2) & "|" & s(3) & "|" & s(4) & "|" & _
                    s(5) & "+" & cell.Offset(0, 8) & "|" & s(6) + cell.Offset(0, 9) & "|" & _
                    s(7) + cell.Offset(0, 10) & "|" & s(8) + cell.Offset(0, 11) & "|" & _
                    s(9) + cell.Offset(0, 12) & "|" & s(10) & "|" & s(11) & "|" & _
                    s(12) & "|" & s(13) & "|" & s(14) & "|"                                     '
        End If
    Next                                                                                        '
'
    Sheets.add after:=ActiveSheet                                                               '
    ActiveSheet.Name = "PORTAL"                                                                 '
'
    Worksheets("2B").Range("A1:V6").Copy Range("A1")                                            '
'
    ReDim arr(1 To dic.Count, 1 To 22)                                                          '
'
    For Each key In dic.keys                                                                    '
        k = k + 1                                                                               '
        For j = 1 To 22                                                                         '
            Select Case j                                                                       '
                Case 1, 2, 3                                                                    '
                    arr(k, j) = Split(key, "|")(j - 1) & IIf(j = 3, "-Total", "")               '
                Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17                           '
                    arr(k, j) = Split(dic(key), "|")(j - 4)                                     '
                Case 19                                                                         '
                    arr(k, j) = Split(dic(key), "|")(14)                                        '
            End Select
        Next                                                                                    '
    Next                                                                                        '
'
    With Range("A7").Resize(dic.Count, 22)                                                      '
        .Value = arr                                                                            '
        .EntireColumn.AutoFit                                                                   '
        .Columns(9).HorizontalAlignment = xlCenter                                              '
    End With
'
    Range("F7:F" & dic.Count + 6).NumberFormat = "#,##0.00"                                     '
    Range("J7:M" & dic.Count + 6).NumberFormat = "#,##0.00"                                     '
'
    Application.ScreenUpdating = True                                                           '
End Sub

 

[RAJESH1960]

First swing.....SIXER..!! . Thanks buddy. Will check what you did to the code which I couldn't do, tonight. gtg. Have a nice day.

 

[johnnyL]

Glad to help.