• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate, with multiple test criteria

Mairig

New Member
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
 

Attachments

Hui

Excel Ninja
Staff member
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
upload_2015-4-28_10-16-18.png

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
 

Attachments

Mairig

New Member
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
 
Last edited:

Mairig

New Member
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
 

Attachments

Top