Concatenate, with multiple test criteria

[Mairig]

Hi,

I've been reading some of the other threads about concatenating and can only find a solution that allows 1 test condition.

I have a problem at work where I need at least 3 test conditions to be met before the concatenation is valid.

Hoping the amazing people on here can help me.

So to simplify my work data...

A1="Name", A2="Sheila", A3="Mark", A4="Donald", A5="David", A6="Peter", A7="Edward", A8="Leah", A9="Maggie", A10="Sarah", etc.
B1="Gender", B2="F", B3="M", B4="M", B5="M", B6="M", B7="M", B8="F", B9="F", B10="F", etc.
C1="Age", C2=”41″, C3=”46″, C4=”37″, C5=”59″, C6=”42″, C7=”23″, C8=”35″, C9=""54"", C10=""48"", etc

F2=”M”, F3=”F”
G2="40", G3="49"

I want F8 to be all values from column A (Name) where corresponding value from column B (gender) equals to whatever is in F2 and where corresponding value from column C (Age) is greater than or equal to G2 and less than or equal to G3 (“Mark”, “Peter”).
I want F9 to be all values from column A (Name) where corresponding value from column B (gender) equals to whatever is in F3 and where corresponding value from column C (Age) is greater than or equal to G2 and less than or equal to G3 (“Sheila”, "Sarah").

Also, this should work without the list being ordered by any of the columns.
It also would be nice to be able to define what the seperator is.
Is there a way to do all this plus have it recognise which was the last name in the list & replace the final seperator with the word "and"?

I have attached a sample file with the cells populated

Thanks & regards
Mairig

 

[Hui]

Mairig

Firstly, Welcome to the Chandoo.org Forums

Using the code I posted in your original question at Chandoo.org with a small modification, you get


See attached file:

The function is used thus:

In use it will be
=ConcatIf(Rangge to Concatenate, Rng1, Val1, [Rng2], [Val2], [Val3], [Separator])

=ConcatIf(A1:A10,B1:B10,”M”,C1:C9,G1,G2", ")
Will concatenate A1:A10 where B1:B10=M and C1:C10>=G1 & <=G2 with a , and a space as a separator

all [] parameters are optional

 

[Mairig]

Hi Hui,
thank you very much for this, I really appreciate your efforts.

when I copy this into my workfile I get #Value! errors and cant figure out why.

the only thing I can think of might be the fact that one of my columns is dates not numbers?
or maybe the fact that the formula is in a different worksheet than the data being checked?

Regards
Mairig

 

[Mairig]

Hi Hui,
I've just been having a play with the sample file that you attached, and if I add a new column D and Make it date of birth, then change F2 & F3 to be dates, the formula doesnt give the #Value! error, but it also doesnt give the right answer either (see sample file attached)
Mairig

 

[Hui]

see attached: