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

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