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]

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

<tbody>
</tbody>

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)

Column A	Column B	Column C	Column D	Column E
3216549871	1	3216549871	1	1
7894561232		5468949745	1	2
5468949745	1	6352418497	2	3
6352418497	1	3213212134	3	4
2316548974		0	3	5
6549879871		0	3	6
3213212134	1	0	4	7
6546548845		0	4	8

<tbody>
</tbody>

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