Combine columns of data

[brokerCA]

I have 4 columns that need to be combined into 3 new columns
Product,revenue,product,cost into a combined product, revenue, cost.
The product data does not always have both cost and revenue so a zero will have to be added if product has only one.

A $1 A .5
B $2 C .2
D $3

Result
A $1 .5
B $2 0
C 0 .2
D $3 0

No VBA formulas please. Thanks.

 

[brokerCA]

Hi. Assume the columns used are 7 left to right A to G.

 

[MickG]

Assuming Data start row 2, then try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG27Mar58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
     Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    [COLOR="Navy"]For[/COLOR] Ac = 0 To Lst - 1 [COLOR="Navy"]Step[/COLOR] 2
        [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Offset(, Ac).Value, Dn.Offset(, Ac + 1).Value
        [COLOR="Navy"]Else[/COLOR]
            .Item(Dn.Offset(, Ac).Value) = .Item(Dn.Offset(, Ac).Value) _
            + Dn.Offset(, Ac + 1).Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
ReDim ray(1 To .Count, 1 To 2)

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    c = c + 1
    ray(c, 1) = K: ray(c, 2) = .Item(K)
[COLOR="Navy"]Next[/COLOR] K
Sheets("Sheet2").Range("A2").Resize(c, 2) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[brokerCA]

Thanks. I have not used vba. Is there a way to do this using index match or vlookup?

 

[brokerCA]

column F:
=IFERROR(VLOOKUP(E2,$A:$B,2,FALSE),0)
column G:
=IFERROR(VLOOKUP(E2,$C:$D,2,FALSE),0)