Consolidating in excel

[ctjacobs2010]

If I need to choose (Column B) from a list of numbers (Column A). How do I consolidate the numbers in column B to look like column C> See below for example:



Column A Column B Column C
3216549871 1 3216549871
7894561232 5468949745
5468949745 1 6546548845
6352418497 2255454545
2316548974
6549879871
3213212134
6546548845 1
9875588545
2255454545 1

I dont mind if helper cells are needed, but I dont wan to use macros if I dont need to. Thank You

 

[ctjacobs2010]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column A [/TD]
[TD]Column B [/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]3216549871[/TD]
[TD]1[/TD]
[TD]3216549871[/TD]
[/TR]
[TR]
[TD]7894561232[/TD]
[TD][/TD]
[TD]5468949745[/TD]
[/TR]
[TR]
[TD]5468949745[/TD]
[TD]1[/TD]
[TD]6352418497[/TD]
[/TR]
[TR]
[TD]6352418497[/TD]
[TD]1[/TD]
[TD]3213212134[/TD]
[/TR]
[TR]
[TD]2316548974[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6549879871[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3213212134[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6546548845[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

















Hopefully you can see this better

 

[mse330]

Try the below with adding 2 helper columns … I think the same could be achieve without any helper columns using array formulas (which I am still not good at )

C2 : =IFERROR(IF(VLOOKUP(E2,D:E,2,0)=0,0,OFFSET($A$1,VLOOKUP(E2,D:E,2,0),0)),0)
D2 : =SUM($B$2:B2)
E2 : =ROWS($A$2:A2)

[TABLE="width: 353"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[TD="align: center"]Column C[/TD]
[TD="align: center"]Column D[/TD]
[TD="align: center"]Column E[/TD]
[/TR]
[TR]
[TD="align: center"]3216549871[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3216549871[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7894561232[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5468949745[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5468949745[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6352418497[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]6352418497[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3213212134[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]2316548974[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6549879871[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3213212134[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]6546548845[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]


Or if interested, you can use the below VBA code without any helper columns

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lRowA As Double, lRowC As Double
lRowA = Range("A" & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range("B2:B" & lRowA)) Is Nothing Then
    Range("C2:C" & lRowA).ClearContents
    lRowC = 2
    For x = 2 To lRowA
        If Cells(x, 2) = 1 Then
            Cells(lRowC, 3) = Cells(x, 1)
            lRowC = lRowC + 1
        End If
    Next x
End If

End Sub