Hi, I have two worksheets that have data in them and they are both in one
workbook. Sheet1 contains unique group names in B1:Z1 and unique user names
in A2:A256. Sheet1 is used to put an "X" in each cell where there is a user
that has access to a group(ex. B4 has an "X" because that user has access to
that group). In Sheet2, I have the group names going down Column A, and I
have the users going down Column B through E. Just as listed below.
A B C D E
1 $N2ARMIX LADAFN LADBJG LADFXD LADP3B
2 $N2LAD6 ACSCXM ACSDXP ACSJAA CCCM1K
3 $N2LAD6 EUSAJC EUSAXM EUSCRG EUSDXR
4 $N2LAD6 EUSEHS EUSJAB EUSNXB EUSRMW

My questions is, how do I put an "X" in a cell in sheet1 where that user and
that group matches to the user and group from sheet2? Vlookup didn't work so
does anyone know of a function that can do this. Thanks!

Hi,

the following array formula was tested. It can be made simpler if
necessary. Its range and sheet name assumptions are according to your
post.

It is an *array* formula, so you need to commit it with
Ctrl+Shift+Enter.

Place this formula in B1 of Sheet2 and copy down and accross.

=IF(ISNUMBER(SMALL(IF(T(OFFSET(Sheet1!$A$2:$A$256, ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2),MATCH($A2,Sheet1!$B$1:$G$1,0)))=" X",ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2)+1),COLUMNS($B$1:B$1))),INDEX(Shee t1!$A$2:$A$256,SMALL(IF(T(OFFSET(Sheet1!$A$2:$A$25 6,ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2),MATCH($A2,Sheet1!$B$1:$G$1,0)))=" X",ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2)+1),COLUMNS($B$1:B$1))),"")

HTH
Kostis Vezerides

I don't know why I would put that formula in B1 of sheet2, when sheet2 is the
source and i'm trying to put x on sheet1. Was that an error on your part?

"vezerid" wrote:

Hi,

the following array formula was tested. It can be made simpler if
necessary. Its range and sheet name assumptions are according to your
post.

It is an *array* formula, so you need to commit it with
Ctrl+Shift+Enter.

Place this formula in B1 of Sheet2 and copy down and accross.

=IF(ISNUMBER(SMALL(IF(T(OFFSET(Sheet1!$A$2:$A$256, ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2),MATCH($A2,Sheet1!$B$1:$G$1,0)))=" X",ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2)+1),COLUMNS($B$1:B$1))),INDEX(Shee t1!$A$2:$A$256,SMALL(IF(T(OFFSET(Sheet1!$A$2:$A$25 6,ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2),MATCH($A2,Sheet1!$B$1:$G$1,0)))=" X",ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2)+1),COLUMNS($B$1:B$1))),"")

HTH
Kostis Vezerides

jac007 wrote...
....
. . . Sheet1 contains unique group names in B1:Z1 and unique user names
in A2:A256. Sheet1 is used to put an "X" in each cell where there is a user
that has access to a group(ex. B4 has an "X" because that user has access to
that group). . . .

OK, groups along the top row and users down the left side, and you want
X's in the cells framed by the group and user IDs.

. . . In Sheet2, I have the group names going down Column A, and I
have the users going down Column B through E. Just as listed below.
....
My questions is, how do I put an "X" in a cell in sheet1 where that user and
that group matches to the user and group from sheet2? Vlookup didn't work so
does anyone know of a function that can do this. Thanks!

INDEX/MATCH would be more useful, along with COUNTIF.

If the table of groups and users in Sheet2 were named GUTable, you
could try the following formula in Sheet1 cell B2.

Sheet1!B2:
=REPT("X",COUNTIF(INDEX(GUTable,MATCH(B$2,INDEX(GU Table,0,1),0),$A2))

I get an error of "few arguments". And I did name the range of sheet2 as
GUTable, I don't understand why you put the B$2 in the match function and $A2
in the countif function. What are those suppose to represent?

"Harlan Grove" wrote:

jac007 wrote...
....
. . . Sheet1 contains unique group names in B1:Z1 and unique user names
in A2:A256. Sheet1 is used to put an "X" in each cell where there is a user
that has access to a group(ex. B4 has an "X" because that user has access to
that group). . . .

OK, groups along the top row and users down the left side, and you want
X's in the cells framed by the group and user IDs.

. . . In Sheet2, I have the group names going down Column A, and I
have the users going down Column B through E. Just as listed below.
....
My questions is, how do I put an "X" in a cell in sheet1 where that user and
that group matches to the user and group from sheet2? Vlookup didn't work so
does anyone know of a function that can do this. Thanks!

INDEX/MATCH would be more useful, along with COUNTIF.

If the table of groups and users in Sheet2 were named GUTable, you
could try the following formula in Sheet1 cell B2.

Sheet1!B2:
=REPT("X",COUNTIF(INDEX(GUTable,MATCH(B$2,INDEX(GU Table,0,1),0),$A2))

jac007 wrote...
I get an error of "few arguments". . . .

Sorry, it should be

Sheet1!B2:
=REPT("X",COUNTIF(INDEX(GUTable,MATCH(B$1,INDEX(GU Table,0,1),0),0),$A2))

Omitted the 2nd ,0) .

. . . I don't understand why you put the B$2 in the match function and $A2
in the countif function. What are those suppose to represent?

The B$2 in my original formula was a mistake. It should have been B$1,
which would be the group name in the 1st row. $A2 would be the user
name in the 1st column. That's what you said would be in row 1 (groups
in B1:Z1) and column A (users in A2:A256) in Sheet1. Cell B2 in Sheet1
would presumably need to reference the group in the same column, so
cell B1 with row absolute - B$1, and the user in the same row, so cell
A2 with column absolute - $A2.

Picking this apart, MATCH(B$1,INDEX(GUTable,0,1),0) returns the row
index of the 1st match for the group in B1 in the 1st column of
GUTable. INDEX(GUTable,MATCH(..),0) return the entire row in GUTable
corresponding to that match. COUNTIF(INDEX(..),$A2) counts the number
instances the user in A2 appears in that row in GUTable. Presumably no
user would appear more than once in any row in GUTable, so the COUNTIF
call would return 0 or 1. The REPT("X",COUNTIF(..)) call then returns
"X" if the user in A2 appears in the first row in GUTable corresponding
to the group in B1 and "" (nothing) otherwise.

Thanks! That worked fine, the only problem that i'm having with it is that it
only returns the first 4 rows. (Ex. profile 1 brings the first 4 users it
finds, it doesn't bring others). Profile 1 is in A2 and A3 And A4, so the
users are in B2, C2, D2, B3, C3, D3, B4, C4, and D4. So it only brings back
B2, C2, and D2. Not B3 and so on.

"Harlan Grove" wrote:

jac007 wrote...
I get an error of "few arguments". . . .

Sorry, it should be

Sheet1!B2:
=REPT("X",COUNTIF(INDEX(GUTable,MATCH(B$1,INDEX(GU Table,0,1),0),0),$A2))

Omitted the 2nd ,0) .

. . . I don't understand why you put the B$2 in the match function and $A2
in the countif function. What are those suppose to represent?

The B$2 in my original formula was a mistake. It should have been B$1,
which would be the group name in the 1st row. $A2 would be the user
name in the 1st column. That's what you said would be in row 1 (groups
in B1:Z1) and column A (users in A2:A256) in Sheet1. Cell B2 in Sheet1
would presumably need to reference the group in the same column, so
cell B1 with row absolute - B$1, and the user in the same row, so cell
A2 with column absolute - $A2.

Picking this apart, MATCH(B$1,INDEX(GUTable,0,1),0) returns the row
index of the 1st match for the group in B1 in the 1st column of
GUTable. INDEX(GUTable,MATCH(..),0) return the entire row in GUTable
corresponding to that match. COUNTIF(INDEX(..),$A2) counts the number
instances the user in A2 appears in that row in GUTable. Presumably no
user would appear more than once in any row in GUTable, so the COUNTIF
call would return 0 or 1. The REPT("X",COUNTIF(..)) call then returns
"X" if the user in A2 appears in the first row in GUTable corresponding
to the group in B1 and "" (nothing) otherwise.

Harlan Grove wrote...
jac007 wrote...
I get an error of "few arguments". . . .

Sorry, it should be

Sheet1!B2:
=REPT("X",COUNTIF(INDEX(GUTable,MATCH(B$1,INDEX(G UTable,0,1),0),0),$A2))

Omitted the 2nd ,0) .
....

However, now I see that GUTable could have multiple rows for the same
group. In that case, use the following array formula in Sheet1 cell B2.

=REPT("X",Min(1,SUM(--(IF(INDEX(GUTable,0,1)=B$1,
INDEX(GUTable,1,2):INDEX(GUTable,ROWS(GUTable),5)) =$A2))))

Fill B2 right into C2:Z2, then select B2:Z2 and fill down into B3:Z256.

You are a genious! Thanks so much!!!!

"Harlan Grove" wrote:

Harlan Grove wrote...
jac007 wrote...
I get an error of "few arguments". . . .

Sorry, it should be

Sheet1!B2:
=REPT("X",COUNTIF(INDEX(GUTable,MATCH(B$1,INDEX(G UTable,0,1),0),0),$A2))

Omitted the 2nd ,0) .
....

However, now I see that GUTable could have multiple rows for the same
group. In that case, use the following array formula in Sheet1 cell B2.

=REPT("X",Min(1,SUM(--(IF(INDEX(GUTable,0,1)=B$1,
INDEX(GUTable,1,2):INDEX(GUTable,ROWS(GUTable),5)) =$A2))))

Fill B2 right into C2:Z2, then select B2:Z2 and fill down into B3:Z256.