Code Modify

A

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
How should i modify mentioned below codes to take result c2 too c25 in sheet3 by matching a2 to a25 ?

HTML: 
Sub Commission_Total()

Dim ThisCell As Range
Dim CommTotal As Long

For Each ThisCell In Sheet2.Range("A1:A" & Range("A65536").End(xlUp).Row)
If ThisCell.Value = Sheet3.Range("A2") And ThisCell.Offset(, 1) = "sc" Then
CommTotal = CommTotal + ThisCell.Offset(, 2).Value
End If
Next ThisCell

Sheet3.Range("C2") = CommTotal

End Sub
 
Sort by date Sort by votes
AyazGreat,
I won't say this is the best solution but I've used some routines to get the data into arrays and work with it there. I guess its another approach to consider - I have the feeling I've somehow taken the long way home here with all the array processing but hopefully it will work fairly fast since the routine works mostly in memory.

Regards, AB

Code: 
Sub Test()
Dim a
Dim b
Dim LRow As Long
Dim i As Long, j As Long
Dim ws As Worksheet, wsDest As Worksheet

Set ws = ActiveSheet
LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Capture values in arrays; get uniques for column A
a = To1D(ws.Range("A2:A" & LRow).Value)
a = GetUniques(a)
a = To2D(a, 2)
b = ws.Range("A2:C" & LRow).Value

'Process for sum of Qty's by ID
For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)
        If b(j, 1) = a(i, 1) Then
            If b(j, 2) = "sc" Then
                a(i, 2) = a(i, 2) + b(j, 3)
            End If
        End If
    Next j
Next i

'Write to worksheet
Set wsDest = Worksheets.Add(After:=ws)
With wsDest
    .Cells(1, 1).Value = "ID"
    .Cells(1, 2).Value = "Qty"
    .Cells(2, 1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End With


End Sub
'----------------------------------------------------------------------
Function GetUniques(Arg1 As Variant) As Variant
Dim b
Dim i As Long
Dim lngCount As Long

'Sort
Arg1 = BubbleSort(Arg1)

ReDim b(1 To 1): b(1) = Arg1(1)

lngCount = 1
For i = 2 To UBound(Arg1)
    If Arg1(i) <> Arg1(i - 1) Then
        lngCount = lngCount + 1
        ReDim Preserve b(1 To lngCount)
        b(lngCount) = Arg1(i)
    End If
Next i

Arg1 = Empty

GetUniques = b

End Function
'----------------------------------------------------------------------
Function BubbleSort(ByVal TempArray As Variant) As Variant
    'From http://support.microsoft.com/kb/133135
    
    Dim Temp As Variant
    Dim i As Integer
    Dim NoExchanges As Integer
    Dim x As Long

    ' Loop until no more "exchanges" are made.
    Do
        NoExchanges = True

        ' Loop through each element in the array.
        For i = 1 To UBound(TempArray) - 1

            ' If the element is greater than the element
            ' following it, exchange the two elements.
            If TempArray(i) > TempArray(i + 1) Then
                NoExchanges = False
                Temp = TempArray(i)
                TempArray(i) = TempArray(i + 1)
                TempArray(i + 1) = Temp
            End If
        Next i
    Loop While Not (NoExchanges)
    
    BubbleSort = TempArray
End Function
'----------------------------------------------------------------------
Function To1D(ByRef Arg1 As Variant) As Variant
'Converts a two-dimensional variant/array to a one dimensional variant/array
'Base 1
Dim a As Variant
Dim x As Long

ReDim a(1 To UBound(Arg1, 1))
For x = 1 To UBound(Arg1, 1)
    a(x) = Arg1(x, 1)
Next x

To1D = a

End Function
'----------------------------------------------------------------------
Function To2D(ByRef Arg1 As Variant, ByRef lngNumColumns) As Variant
'Converts a one-dimensional variant/array to a two dimensional variant/array
'Base 1
Dim a
Dim x As Long

ReDim a(1 To UBound(Arg1), 1 To lngNumColumns)
For x = 1 To UBound(Arg1)
    a(x, 1) = Arg1(x)
Next x

To2D = a

End Function

You'll need to change the last lines of the first routine to have the final results where you like. For instance, if on Sheet2 Cell A1 Then:
Code: 
Set wsDest = Worksheets.("Sheet2")
With wsDest
    .Range("A1").Value = "ID"
    .Range("B1").Value = "Qty"
    .Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End With
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sorry Sir Alexander Barnes for giving you late reply because my windows got crashed and I was not able to connect to see your reply but thank you very much for the efforts you have made for us I can feel how you worked hard to develop these code and spent your precious time

I have nothing words to say but thanks.

Sir if you don't mind me saying that these codes gives me total sum in sheet2 in column b of each unque ID which is equal to "sc" if I want to take sum of more products in columns c, d, e,and f which for example equal to "jp","jl","jc" and "jm" how to modify these codes?
 
Upvote 0
I believe this will do the job then...
I've set this up so that:
--you select a cell in the column you want to sum up,
--it will sum up quantities in that column.
--you can enter any value for the criteria ("sc", etc.)
--but it will always default to the value in column B of the currently active cell's row.

Code: 
Sub Test()
Dim a
Dim b
Dim c
Dim LRow As Long
Dim i As Long, j As Long
Dim ws As Worksheet, wsDest As Worksheet
Dim msg As String
Dim strPIValue As String
Dim strColumn As String

Set ws = ActiveSheet
LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'PI value to test. Default is value in column B of same row as active cell
strPIValue = InputBox( _
        Prompt:="Enter the PI Value Criteria: ", _
        Title:="PI Criteria", _
        Default:=ws.Cells(ActiveCell.Row, "B").Value _
        )

'Column to search in will be the column of the activecell!
strColumn = Chr(ActiveCell.Column + 64)

'Capture values in arrays; get uniques for column A
a = To1D(ws.Range("A2:A" & LRow).Value) 'ID values
a = GetUniques(a)
a = To2D(a, 2) 'Unique ID's
b = ws.Range("A2:B" & LRow).Value 'ID's and PI values
c = ws.Range(strColumn & "2:" & strColumn & LRow).Value 'qty values

'Process for sum of Qty's by ID
For i = 1 To UBound(a, 1) 'unique id's
    For j = 1 To UBound(b, 1) 'PI values
        If b(j, 1) = a(i, 1) Then 'Id is matched
            If b(j, 2) = strPIValue Then 'PI value criteria is met
                a(i, 2) = a(i, 2) + c(j, 1) 'Qty from qty column is added to running total
            End If
        End If
    Next j
Next i

'Write to worksheet
Set wsDest = Worksheets.Add(After:=ws)
With wsDest
    .Cells(1, 1).Value = "ID"
    .Cells(1, 2).Value = "Qty"
    .Cells(2, 1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End With

MsgBox "Sums totaled based on quantities in Column " & strColumn & "."
End Sub
'----------------------------------------------------------------------
Function GetUniques(Arg1 As Variant) As Variant
Dim b
Dim i As Long
Dim lngCount As Long

'Sort
Arg1 = BubbleSort(Arg1)

ReDim b(1 To 1): b(1) = Arg1(1)

lngCount = 1
For i = 2 To UBound(Arg1)
    If Arg1(i) <> Arg1(i - 1) Then
        lngCount = lngCount + 1
        ReDim Preserve b(1 To lngCount)
        b(lngCount) = Arg1(i)
    End If
Next i

Arg1 = Empty

GetUniques = b

End Function
'----------------------------------------------------------------------
Function BubbleSort(ByVal TempArray As Variant) As Variant
    'From http://support.microsoft.com/kb/133135
    
    Dim Temp As Variant
    Dim i As Integer
    Dim NoExchanges As Integer
    Dim x As Long

    ' Loop until no more "exchanges" are made.
    Do
        NoExchanges = True

        ' Loop through each element in the array.
        For i = 1 To UBound(TempArray) - 1

            ' If the element is greater than the element
            ' following it, exchange the two elements.
            If TempArray(i) > TempArray(i + 1) Then
                NoExchanges = False
                Temp = TempArray(i)
                TempArray(i) = TempArray(i + 1)
                TempArray(i + 1) = Temp
            End If
        Next i
    Loop While Not (NoExchanges)
    
    BubbleSort = TempArray
End Function
'----------------------------------------------------------------------
Function To1D(ByRef Arg1 As Variant) As Variant
'Converts a two-dimensional variant/array to a one dimensional variant/array
'Base 1
Dim a As Variant
Dim x As Long

ReDim a(1 To UBound(Arg1, 1))
For x = 1 To UBound(Arg1, 1)
    a(x) = Arg1(x, 1)
Next x

To1D = a

End Function
'----------------------------------------------------------------------
Function To2D(ByRef Arg1 As Variant, ByRef lngNumColumns) As Variant
'Converts a one-dimensional variant/array to a two dimensional variant/array
'Base 1
Dim a
Dim x As Long

ReDim a(1 To UBound(Arg1), 1 To lngNumColumns)
For x = 1 To UBound(Arg1)
    a(x, 1) = Arg1(x)
Next x

To2D = a

End Function
 
Upvote 0
try
Code: 
Sub test()
Dim a, b(), i As Long, n As Long, t As Long
Dim dic1 As Object, dic2 As Object
Set dic1 = CreateObject("Scripting.Dictionary")
dic1.CompareMode = vbTextCompare
Set dic2 = CreateObject("Scripting.Dictionary")
dic2.CompareMode = vbTextCompare
a = Sheet2.Range("a1"(.CurrentRegion.Resize(,3).Value
ReDim b(1 To UBound(a,1), 1 To Columns.Count)
n = 1 : t = 1
For i = 2 To UBound(a,1)
    If Not dic1.exists(a(i,1)) Then
        n = n + 1 : dic1.add a(i,1), n : b(n,1) = a(i,1)
    End If
    If Not dic2.exists(a(i,2)) Then
        t = t + 1 : dic2.add a(i,2), t : b(1,t) = a(i,2)
    End If
    b(dic1(a(i,1)), dic2(a(i,2)) = b(dic1(a(i,1)), dic2(a(i,2)) + a(i,3)
Next
Sheet3.Range("a1").Resize(n,t).Value = b
Set dic1 = Nothing : Set dic2 = Nothing
End Sub
 
Upvote 0
Thank you very much both of you

Sir Jindon
There is a error in your code at this line

HTML: 
b(dic1(a(i,1)), dic2(a(i,2)) = b(dic1(a(i,1)), dic2(a(i,2)) + a(i,3)
and when I change it

HTML: 
b (dic1(a(i, 1))), dic2(a(i, 2)) = b(dic1(a(i, 1))), dic2(a(i, 2)) + a(i, 3)

It gives me a error message "Compile error", Expected Sub, Function, or Property
 
Upvote 0
chabge to
Rich (BB code): 
b(dic1(a(i,1)), dic2(a(i,2))) = b(dic1(a(i,1)), dic2(a(i,2))) + a(i,3)
 
Upvote 0
Hi Sir JIndon

Coming back after a long illness but thanks for your reply i will test your codes and then reply you
 
Upvote 0
Sir Jindon

I have made some changes and you can see mentioned below sheets

Sheet2 is containg the data

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99cc00" width=64 height=17>
ID​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St100​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St300​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St600​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St1000​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
JP​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
HP​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1001​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1000">
1,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="50">
50 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="500">
500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="5">
5 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1025​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1000">
1,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2">
2 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1089​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="4000">
4,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="900">
900 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="0">
- ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="0">
- ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="500">
500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1">
1 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1236​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1200">
1,200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="10">
10 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1001​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="12000">
12,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="9000">
9,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="500">
500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1000">
1,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3">
3 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1025​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1200">
1,200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="50">
50 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="500">
500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1">
1 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1089​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="800">
800 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="25">
25 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="750">
750 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="9">
9 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1236​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1600">
1,600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="50">
50 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="850">
850 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="4">
4 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1258​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="20000">
20,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1800">
1,800 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2500">
2,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="25">
25 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1892​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3000">
3,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="20">
20 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="10">
10 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1">
1 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1258​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="900">
900 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="20">
20 ​
</TD></TR></TBODY></TABLE>
​

And in Sheet3 I wish to have Result as mentioned below

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99cc00" width=64 height=17>
ID​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St100​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St300​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St600​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St1000​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
JP​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
HP​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1001​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="13000">
13,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="9600">
9,600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="250">
250 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="8">
8 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1025​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3200">
3,200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="150">
150 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3">
3 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1089​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="4800">
4,800 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="25">
25 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1250">
1,250 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="10">
10 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1236​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3600">
3,600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="250">
250 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2850">
2,850 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="14">
14 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1258​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="22000">
22,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3800">
3,800 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1200">
1,200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3400">
3,400 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="45">
45 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1892​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3000">
3,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="20">
20 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="10">
10 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1">
1 ​
</TD></TR></TBODY></TABLE>

Could you please help to resolve the same.

Thanks in advance
 
Upvote 0
try
Code: 
Sub test()
Dim a, b(), i As Long, ii As Long, n As Long
a = Sheets("Sheet2").Range("a1").CurrentRegion.Value
ReDim b(1 To UBound(a,1), 1 To UBound(a,2))
n = 1
For i = 1 To UBound(a,2) : b(1, i) = a(1, i) : Next
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a,1)
        If Not .exists(a(i,1)) Then
            n = n + 1 : .add a(i,1), n : b(n,1) = a(i,1)
        End If
        For ii = 2 To UBound(a,2)
            b(.item(a(i,1)), ii) = b(.item(a(i,1)), ii) + a(i, ii)
        Next            
    Next
End With
With Sheets("Sheet3").Range("a1")
    .CurrentRegion.ClearContents
    .Resize(n,UBound(b,2)).Value = b
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Macro that copies Excel data into pdf field doesn't always paste all the data
Replies
3
Views
630
RickOlson
R
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
207
mumps
mumps
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
62
VanGirl
V
S
  • Question
VBA: All Sheets data to Master Sheet (excluding some sheet)
2
Replies
11
Views
390
kevin9999
K
L
  • Question
VBA query - not opening outlook email but one drive
Replies
0
Views
171
Liverlee
L

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top