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.

[TABLE="class: grid, width: 20, align: center"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]10 Apple, 8 Car, 2 Home[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boy[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Column1	Column2	Column3
Apple	10, Apple 8, Car 2, Home
Mango
Car
Boy
Home

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

</tbody>

All​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=abhi(A2:B6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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