Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique values in a macro
Hello all, I have the sheet with the following data
12 1 01 6 18 1 01 10 12 1 01 6 11 5 09 3 12 1 10 1 12 2 01 12 12 1 01 2 11 5 09 5 11 5 11 23 The data on the last column is the count, I need to get the following output 12 1 01 12 (sum of row 1 & 3) 18 1 01 10 11 5 09 8 (sum of row 4 & 8) 12 1 10 1 12 2 01 12 12 1 01 2 11 5 11 23 This is what the above results is - data from row1 for colA, colB & colC is compared with the data with row2: ColA, ColB, ColC, and if they are same then the data from ColD is summed up. I was trying to do it through SumProduct, but could not achieve what I was trying to do. So in the above example row 1 & 3 are same when u compare ColA, ColB & ColC, so I add up the values in column D. Basically I need unique values to be selected from ColA, ColB & colC and if there are more than one row with the same value I need to sum the data in ColD. Hope I have not confused with too many things. I need this to be done as part of the macro That I'm creating Thankyou for any help. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique values in a macro
Public Sub ProcessData()
Dim i As Long Dim LastRow As Long Dim rng As Range Dim sFormula1 As String Dim sFormula2 As String With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row sFormula1 = "SUMPRODUCT(--(A1:A" & LastRow & "=A<)," & _ "--(B1:B" & LastRow & "=B<)," & _ "--(C1:C" & LastRow & "=C<)," & _ "D1:D" & LastRow & ")" sFormula2 = "SUMPRODUCT(--(A1:A<=A<)," & _ "--(B1:B<=B<)," & _ "--(C1:C<=C<))" For i = 1 To LastRow .Cells(i, "D").Value = .Evaluate(Replace(sFormula1, "<", i)) If .Evaluate(Replace(sFormula2, "<", i)) 1 Then If rng Is Nothing Then Set rng = .Rows(i) Else Set rng = Union(rng, .Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.Delete End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Hello all, I have the sheet with the following data 12 1 01 6 18 1 01 10 12 1 01 6 11 5 09 3 12 1 10 1 12 2 01 12 12 1 01 2 11 5 09 5 11 5 11 23 The data on the last column is the count, I need to get the following output 12 1 01 12 (sum of row 1 & 3) 18 1 01 10 11 5 09 8 (sum of row 4 & 8) 12 1 10 1 12 2 01 12 12 1 01 2 11 5 11 23 This is what the above results is - data from row1 for colA, colB & colC is compared with the data with row2: ColA, ColB, ColC, and if they are same then the data from ColD is summed up. I was trying to do it through SumProduct, but could not achieve what I was trying to do. So in the above example row 1 & 3 are same when u compare ColA, ColB & ColC, so I add up the values in column D. Basically I need unique values to be selected from ColA, ColB & colC and if there are more than one row with the same value I need to sum the data in ColD. Hope I have not confused with too many things. I need this to be done as part of the macro That I'm creating Thankyou for any help. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Values, not Unique Records | Excel Discussion (Misc queries) | |||
Macro to find and record Unique values? | Excel Discussion (Misc queries) | |||
Why does this code remove Duplicate Values, by showing only 1, but it does NOT show Unique values for some reason ? | Excel Programming | |||
macro to transpose cells in Column B based on unique values in Column A | Excel Programming | |||
How do I search thr'o column and put unique values in differnt sheet and sum corresponding values in | Excel Programming |