if value of x passes criteria then copy y

Hi,

You could download Laurent Longre's MOREFUNC.XLL and use the MCONCAT function in B2 would be:

=MCONCAT(IF(Sheet1!$I$2:$I$31={"3a","3b","3c","2a","2b"},Sheet1!$A$2:$A$31," "),)

Use Ctrl+Shift+Enter when entering the formula so the resulting formula would be an array formula and look like:

{=MCONCAT(IF(Sheet1!$I$2:$I$31={"3a","3b","3c","2a","2b"},Sheet1!$A$2:$A$31," "),)}

If it were me I'd probably try to arrange my data differently and use a Pivot Table with the appropriate qualifying data selected for each option.

HTH
Steve

Hi Steve, that works a treat...one question after trying it, is there anyway to set the results in B2 (the names that it pulls through) so that they are displayed one per line in the single cell?

Thanks

Glad it worked for you. You can add 5 spaces at the end of MCONCAT function like below.

=MCONCAT(IF(Sheet1!$I$2:$I$31={"3a","3b","3c","2a","2b"},Sheet1!$A$2:$A$31," "))

Again, commit with Ctrl+Shift+Enter.

HTH
Steve

Sorry, pasted the wrong one in the reply.

=MCONCAT(IF(Sheet1!$I$2:$I$31={"3a","3b","3c","2a","2b"},Sheet1!$A$2:$A$31," "))


Steve

Okay, it wasn't me looks like once I post it the extra spaces in quotes revert to a single space so I'll put in a b for the space.

MCONCAT(IF(Sheet1!$I$2:$I$31={"3a","3b","3c","2a","2b"},Sheet1!$A$2:$A$31,"bbbbb"))


Steve

I have replaced the 5 b's with 5 spaces, this seems to work for all but the first name that is pulled through, before this there are a number of spaces which send the name into the centre of the box, the following names are all down the left side of the box. Is there anything to fix this?

Thanks

I am not experiencing that issue. I've attached my version.

I just realized this is happening when the first match is not the first item in the list. I'll play around with it and let you know.

Put 10 spaces where you have 5. When the first person that meets the criteria is not the first person in the list it will create a blank space above the first name returned. If you can live with that this will work. I tried it using real names with varying lengths and it keeps them aligned. To Martin's point, I'd be curious as to why you need the data in this format? I also think using a pivot table with some filtering and calculated fields might be easier to maintain in the long run.

Steve

We have been using this for a few weeks now with no problem. Another school that I deal with has shown an interest in the sheet, however on their machines it doesn't work. They are using Office 2003 and have the MoreFunc Add-In installed.

I have converted the sheet to a 2003 version and this works fine on my laptop (2007) but when it is opened in 2003, only a few of the names are pulled through.

Is there anything I can do?