Solved: All matched values in one Row

[asingh]

Hi,

Need help..!

Based on a certain criteria, I want all the corresponding cell values to appear in the same cell.

Am attaching an example:
Where ever the metric = 2, I want the value(from column A) returned in the same cell. Basically the correponding values should be concatendated and displayed in a single cell.

So the output in the cell would be: a;d;e;f.

Also, I have to do this without VBA.

thanks a lot for the help,

asingh

[MaximS]

I'm not sure if I got you right. Check attachement for details.

[asingh]

Hi,

MaximS: Actually what I want, when ever there is an occurrence of a 2 in column B, I want the corresponding value from A returned. But all the Values from A should be concatendated in one cell.

[Bob Phillips]

=IF(B2=2,A2&";","")&IF(B3=2,A3&";","")&IF(B4=2,A4&";","")&IF(B5=2,A5&";","" )&IF(B6=2,A6&";","")&IF(B7=2,A7&";","")&IF(B8=2,A8&";","")

[asingh]

Hi,

XLD, thanks for the response, though I was trying this:

=CONCATENATE(IF($B$2:$B$8=2,"Found;",""))

As an array function. But it is not working....Was using the "Found;" to test for occurence, would this be a correct approach too..???

[Bob Phillips]

It won't work, you cannot use CONCATENATE in an array, shame, but that is how it is.

[asingh]

Okay,

I guess I should have mentioned this before..so sorry, but my rows are going to be dynamic. So I need a method, where I do not have to right the formula as per the rows being evaluated, it will get too long.

I would need to find the occurrence, and trap that "hit", and generate a value, and concatenate them ( as per the number of occurrences)..all in one cell....??

[Bob Phillips]

Then you need VBA.

[david000]

[VBA]
Sub aaa()
Dim FoundCell, rCell As Range
Dim d, strFirstAddress As String

With Sheet1.Columns(2)
Set rCell = .Find(2, lookat:=xlWhole, LookIn:=xlValues)
strFirstAddress = rCell.Address
Set FoundCell = rCell
Do
d = d & rCell.Offset(, -1) & ";"
Set rCell = .FindNext(rCell)
Set FoundCell = Union(FoundCell, rCell)
Loop While Not rCell Is Nothing And rCell.Address <> strFirstAddress

MsgBox Left(d, Len(d) - 1)
End With
End Sub

[/VBA]