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

bradbitz

New Member
Joined
Nov 10, 2011
Messages
3
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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFG
1E1YY
2E2NCount4E1E1, E3, E4, E6
3E3YE3
4E4YE4
5E5NE6
6E6Y 
Sheet1
 

bradbitz

New Member
Joined
Nov 10, 2011
Messages
3
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,086
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top