CODEWITHSUNDEEP
excelvba
Suresh Jambhalkar
Suresh Jambhalkar

Reputation: 71

Group Values in excel according duplicate value in column

Suppose, I have Data 

Column1     Column2  
1            1000   
1           -2000  
1            3000  
2            2000  
2           -1000  
3            5000  
3           -4000

I want to display it like 

Column1    Column2   Column3  
1           1000      3000   
2           2000  
3           5000

I want to take only positive value from column2 where column1 have same value(e.g. for 1 have 2 positive values. I want to display them in format shown above.)

How can I achieve this using Manual Methods(Formulas) or using VBA?? I have written a code where it takes positive values from column1 where column1.value=1. But how to iterate through next values(i.e. 2 and 3)

Sheets("Sheet1").Select
myvalue = Cells(2, 1).Value
MsgBox myvalue


Dim negativevalue(0 To 10) As Long
Dim colum As Integer
Dim row As Integer

colum = 1
row = 2
i = 0
While Cells(row, colum).Value = myvalue
If (Cells(row, 2).Value < 0) Then

MsgBox Cells(row, 2).Value
negativevalue(i) = Cells(row, 2).Value


End If

Upvotes: 2

Views: 17083

Answers (2)

chuff
chuff

Reputation: 5876

Here is a pure formula-based approach to your question.

Two sets of formulas are needed, the first set to create an unduplicated list of distinct values from column 1 and the second set to look up and place the positive values in column 2.

The formula to create the list of distinct column 1 values is placed in cell D2 and copied down the column. The formula uses a named range for the column 1 values. If you put it in another column, adjust the $D1$D:D1 to the column you are using, and make sure it refers to the cell just above where you put the formula. For example, if you put the formula in cell C4, the column reference in the formula should be $C$3:C3

Formula to create list of distinct values from column 1
Cell D2  =IFERROR(INDEX(Column1,MATCH(0,INDEX(COUNTIF($D$1:D1,Column1),
          0,0),0)),"-")

The column 2 lookup is an array formula; in the example worksheet, it is entered in cell E2 (using the Ctrl-Shift-Enter key combination) and then copied down and across.

Array Formula to lookup and place column 2 values
Cell E2  =IFERROR(INDEX(Column2,1/LARGE(IFERROR(1/((Column1=$D2)*
          (Column2>=0)*ROW(INDIRECT("1:"&COUNTA(Column2)))),0),
          COLUMNS($E$2:E$2)),1),"-")

enter image description here

Upvotes: 3

neizan
neizan

Reputation: 2321

There might be shorter ways, but this works. Select the desired range and run the following macro:

Sub ProcessData()
    'Sort the data
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Selection.Cells(1, 1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Selection
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'Process data
    Dim cl As Object, r As Integer, c As Integer, cNum As Integer, cSt As Integer
    Dim first As Boolean, update As Boolean
    r = Selection.Cells(1, 1).Row - 1
    cNum = Selection.Cells(1, 2).Column + 2
    cSt = cNum + 1
    first = True
    update = False

    For Each cl In Selection.Cells.Columns(1).Cells
        If cl.Offset(0, 1).Value >= 0 Then
            update = False
            If first Then
                first = False
                update = True
            ElseIf cl.Value <> Cells(r, cNum).Value Then
                update = True
            End If
            If update Then
                r = r + 1
                c = cSt
                Cells(r, cNum).Value = cl.Value
            End If
            Cells(r, c).Value = cl.Offset(0, 1).Value
            c = c + 1
        End If
    Next
End Sub

Upvotes: 2

Related Questions