I think that using an array formula will solve my little problem, but I only
understand a little about them, and the formulae are only returning blanks.

I have a table of data, that I wish to rearrange into a grid based on
entries into 2 columns with restricted values allowed.

The data is in the format, and the headers define the Named Ranges
ID# Description Y-axis X-axis
-------------------------------------------
01 Apples A Left
02 Oranges B Left
03 Bread C Center
04 Table B Right
05 Desk C Center
......

The description column is not to be returned, but I have included it in case
it requires a different solution.

I have then entered formula similar to the following in a grid
{=Concatenate(If(and(Y-Axis=C,X-Axis=Center),Text(ID#," 00,"),""))}
and was expecting it to return the result, " 03, 05,"

so the complete grid would like

| Left | Center | Right |
--|---------|-------------|---------|
A | 01, | | |
--|---------|-------------|---------|
B | 02, | | 04, |
--|---------|-------------|---------|
C | | 03, 05, | |
--|---------|-------------|---------|

Could someone let me know why this isnt working, or some other way in which
it can be achieved

Thanks very much in advance

Kris

Well, there are a couple of things that occur to me:

1) Are Y-Axis and X-Axis defined names, or are they values? If they are the
latter, then your IF(AND( statement won't work.

I would do something like =IF(AND(C1="A",D1="Left"),CONCATENATE(A1,B1),"")
and fill down as necessary. This formula assumes that the ID column is in
A:A, etc. Adjust the formula to suit your needs.

But I would put the IF(AND( statement on the outside, and, depending on
whether the condition returns TRUE, then CONCATENATE, else return an
alternative (in my example, an empty string.)

Dave
--
Brevity is the soul of wit.


"Kris_Wright_77" wrote:

I think that using an array formula will solve my little problem, but I only
understand a little about them, and the formulae are only returning blanks.

I have a table of data, that I wish to rearrange into a grid based on
entries into 2 columns with restricted values allowed.

The data is in the format, and the headers define the Named Ranges
ID# Description Y-axis X-axis
-------------------------------------------
01 Apples A Left
02 Oranges B Left
03 Bread C Center
04 Table B Right
05 Desk C Center
.....

The description column is not to be returned, but I have included it in case
it requires a different solution.

I have then entered formula similar to the following in a grid
{=Concatenate(If(and(Y-Axis=C,X-Axis=Center),Text(ID#," 00,"),""))}
and was expecting it to return the result, " 03, 05,"

so the complete grid would like

| Left | Center | Right |
--|---------|-------------|---------|
A | 01, | | |
--|---------|-------------|---------|
B | 02, | | 04, |
--|---------|-------------|---------|
C | | 03, 05, | |
--|---------|-------------|---------|

Could someone let me know why this isnt working, or some other way in which
it can be achieved

Thanks very much in advance

Kris

Dave

Thanks for your quick response.

I have tried the formula you proposed, but I cannot get it to produce the
results in the grid as I wanted. - Your formula concatenates the ID# &
Description if 2 conditions are true.
What I need is to concatenate several ID# where the 2 conditions are true.

And the X-Axis, Y-Axis and ID# are defined named ranges (single column array).

I did see another similar post with If and Arrays that gave me the idea of
trying to do it this way (but I cant find it now).

If you need any more detail on what I am aiming to do, please let me know.

Kris



"Dave F" wrote:

Well, there are a couple of things that occur to me:

1) Are Y-Axis and X-Axis defined names, or are they values? If they are the
latter, then your IF(AND( statement won't work.

I would do something like =IF(AND(C1="A",D1="Left"),CONCATENATE(A1,B1),"")
and fill down as necessary. This formula assumes that the ID column is in
A:A, etc. Adjust the formula to suit your needs.

But I would put the IF(AND( statement on the outside, and, depending on
whether the condition returns TRUE, then CONCATENATE, else return an
alternative (in my example, an empty string.)

Dave
--
Brevity is the soul of wit.


"Kris_Wright_77" wrote:

I think that using an array formula will solve my little problem, but I only
understand a little about them, and the formulae are only returning blanks.

I have a table of data, that I wish to rearrange into a grid based on
entries into 2 columns with restricted values allowed.

The data is in the format, and the headers define the Named Ranges
ID# Description Y-axis X-axis
-------------------------------------------
01 Apples A Left
02 Oranges B Left
03 Bread C Center
04 Table B Right
05 Desk C Center
.....

The description column is not to be returned, but I have included it in case
it requires a different solution.

I have then entered formula similar to the following in a grid
{=Concatenate(If(and(Y-Axis=C,X-Axis=Center),Text(ID#," 00,"),""))}
and was expecting it to return the result, " 03, 05,"

so the complete grid would like

| Left | Center | Right |
--|---------|-------------|---------|
A | 01, | | |
--|---------|-------------|---------|
B | 02, | | 04, |
--|---------|-------------|---------|
C | | 03, 05, | |
--|---------|-------------|---------|

Could someone let me know why this isnt working, or some other way in which
it can be achieved

Thanks very much in advance

Kris