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

Answers


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


Need Your Help

Can an upgradeable MSI be generated using WIX for automated integration builds?

build wix windows-installer build-automation

There are articles stating how to upgrade an MSI package using WIX here, and here, but for automated integration builds this may not work. Specifically, those articles state that we need to provide...

Passing HTMLPage as a return value of WCF REST Service

wcf html5

I want to pass HTMLPage as a return value of my WCF REST Service to my HTML5 application (client).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.