Edit code to get values of one extra column in the destination sheet

[RAJESH1960]

Hello Experts
This is Bebo02's code which is running perfectly. I just wanted to get the values of one more additional column of Invoice value from 2B to Portal sheet as it is, not combined values. All the other amounts are combined and displayed in the portal sheet. I tried to edit the code myself, but was not able to. So, if anybody can edit the code and after running the code, get the invoice value amount in the portal sheet and include this column in the format columns to number, I would really appreciate it.
convert 2B to Portal.xlsm

 

[RAJESH1960]

The invoice value is already combined value of each multiple rate by default, but mentioned multiple times in every row with multiple rates. I expect the result value as in column F, from any one row from the 2B sheet like in the below image

 

[johnnyL]

How about:

    Option Explicit
Sub add()
'solved & shared by Bebo02

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, 4) & "|" & cell.Offset(0, 5) & "|" & cell.Offset(0, 8) & "|" & cell.Offset(0, 9) & "|" & cell.Offset(0, 10) _
                & "|" & cell.Offset(0, 11) & "|" & cell.Offset(0, 12) & "|" & cell.Offset(0, 13)
            If Not dic.exists(id) Then
                dic.add id, item
            Else
                s = Split(dic(id), "|")
                dic(id) = s(0) & "|" & s(1) & "|" & s(2) & "+" & cell.Offset(0, 8) & "|" & _
                s(3) + cell.Offset(0, 9) & "|" & _
                s(4) + cell.Offset(0, 10) & "|" & _
                s(5) + cell.Offset(0, 11) & "|" & _
                s(6) + cell.Offset(0, 12) & "|"
            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 5
                    arr(k, j) = Split(dic(key), "|")(0)
                Case 6
                    arr(k, j) = Split(dic(key), "|")(1)
                Case 9, 10, 11, 12, 13
                    arr(k, j) = Split(dic(key), "|")(j - 7)
            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]

I tried many a times but maybe due to lack of knowledge about codes I couldn't know exactly which was which. I even tried editing this line the same way but it didn't work. Maybe it is connected to the remaining lines too. Not sure..

item = cell.Offset(0, 4) & "|" & cell.Offset(0, 5) & "|" & cell.Offset(0, 8) & "|" & cell.Offset(0, 9) & "|" & cell.Offset(0, 10) _

Anyways, It worked good JohnnyL. Problem solved. Thanks man.

 

[johnnyL]

Glad to help.