Select a Cell Based on Value of Another Cell VBA

[abhi1407]

HI - I want to create a column with concatenated column 3 like below. Basically select values from column 1 and column 2 where column 2 value is non zero.

Can anyone please help ? Highly appreciated.

Column1	Column2	Column3
Apple	10	10 Apple, 8 Car, 2 Home
Mango	0
Car	8
Boy	0
Home	2

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function abhi(Rng As Range) As String
   Dim cl As Range
   
   For Each cl In Rng.Columns(2).Cells
      If Not cl = 0 Then
         abhi = abhi & cl.Value & ", " & cl.Offset(, -1).Value & vbLf
      End If
   Next cl
   abhi = Left(abhi, Len(abhi) - 1)
End Function

Used like

Excel 2013/2016
	A	B	C
1	Column1	Column2	Column3
2	Apple	10	10, Apple 8, Car 2, Home
3	Mango	0
4	Car	8
5	Boy	0
6	Home	2
All

Cell Formulas
Range	Formula
C2	=abhi(A2:B6)

 

[abhi1407]

Thanks so much for quick response. One last question, how do i change
10, Apple8, Car2, Home to 10 Apple, 8 Car, 2 Home

Hi & welcome to MrExcel.
How about

Function abhi(Rng As Range) As String
   Dim cl As Range
   
   For Each cl In Rng.Columns(2).Cells
      If Not cl = 0 Then
         abhi = abhi & cl.Value & ", " & cl.Offset(, -1).Value & vbLf
      End If
   Next cl
   abhi = Left(abhi, Len(abhi) - 1)
End Function

Used like

Excel 2013/2016

	A	B	C
1	Column1	Column2	Column3
2	Apple	10	10, Apple 8, Car 2, Home
3	Mango	0
4	Car	8
5	Boy	0
6	Home	2

<tbody>
</tbody>

All​

Worksheet Formulas Cell Formula C2 =abhi(A2:B6) <tbody> </tbody>

<tbody>
</tbody>

 

[Fluff]

use

Function abhi(Rng As Range) As String
   Dim cl As Range

   For Each cl In Rng.Columns(2).Cells
      If Not cl = 0 Then
         abhi = abhi & cl.Value & ", " & cl.Offset(, -1).Value & ", "
      End If
   Next cl
   abhi = Left(abhi, Len(abhi) - 2)
End Function

 

[abhi1407]

Thanks. It works great.

use

Function abhi(Rng As Range) As String
   Dim cl As Range

   For Each cl In Rng.Columns(2).Cells
      If Not cl = 0 Then
         abhi = abhi & cl.Value & ", " & cl.Offset(, -1).Value & ", "
      End If
   Next cl
   abhi = Left(abhi, Len(abhi) - 2)
End Function

 

[Fluff]

You're welcome & thanks for the feedback