Extract data from a column, based on criteria in an adjacent column

[bradbitz]

I have a table that lists elements in column A and has a Y/N quality assessment in column B. i.e.:

(A) (B)
E1 Y
E2 N
E3 Y
E4 Y
E5 N
E6 Y

Everytime (B) is "Y", I would like Excel to take the element id in (A) and copy it into a cell on another sheet. Ideally, I'd like all of those id's to be pasted into the same cell as a comma delineated list. i.e.:

E1, E3, E4, E6

I've tried messing around with PivotTable, IF and V/H/LOOKUP, but couldn't get those to work (if they're even the right method). Any options or advice would be greatly appreciated.

Thanks in advance!

 

[SteveO59L]

I think you will need a vba solution for this

 

[How_Do_I]

Not if he can get hold of the morefunc add on...

Here's how to do it on the same sheet... Credit to Aladin who showed me how to do this.

Excel Workbook
	A	B	C	D	E	F	G
1	E1	Y		Y
2	E2	N	Count	4	E1		E1, E3, E4, E6
3	E3	Y			E3
4	E4	Y			E4
5	E5	N			E6
6	E6	Y
Sheet1

 

[bradbitz]

Everything worked great until the substitute and mconcat function. I'm using 2007, so I tried using the CONCATENATE function instead of loading the morefunc add on. That just adds some commas but still only returns one text item.

I'll have to load the add-in and report back.

Thanks so far!

 

[How_Do_I]

I can't help you with xl 2007 as I don't use it...

Somebody must know if the OP can get away without using morefunc in xl 2007 to solve this part of my reply...

{=SUBSTITUTE(MCONCAT(IF(E2:E10<>"",", "&E2:E10,"")),", ","",1)}

 

[Aladin Akyurek]

Everything worked great until the substitute and mconcat function. I'm using 2007, so I tried using the CONCATENATE function instead of loading the morefunc add on. That just adds some commas but still only returns one text item.

I'll have to load the add-in and report back.

Thanks so far!

I can't help you with xl 2007 as I don't use it...

Somebody must know if the OP can get away without using morefunc in xl 2007 to solve this part of my reply...

{=SUBSTITUTE(MCONCAT(IF(E2:E10<>"",", "&E2:E10,"")),", ","",1)}

ACONCAT is a work-alike...

Add the following function code in VBA (in-between the lines) to your workbook as a module. In order to add the function to your workbook, run...

ALt+F11
Insert | Module
File | Close and Return to Microsoft Excel.
_________________________________________________________

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[bradbitz]

Works great!
Thank you very much.

 

[Aladin Akyurek]

Works great!
Thank you very much.

You are welcome. Thanks for providing feedback.