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
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),"-")