unique list

[orsm6]

Hi all - is it possible to get a unique list across multiple sheets (5 sheets) with criteria?

Sheet 1 - A1 contains week number e.g. 32
Sheet 1 - A2 contains the formula where the unique list from the 5 sheets is returned:

each sheet holding the list of names has the same ranges....
- all sheets column B holds the list of names
- all sheets column A holds the week numbers

all sheets holding the data and ranges are arranged together if that helps with any formulas. first:last sheet name: PICCOLO:HART


TIA

 

[DanteAmor]

Try:

=LET(a,VSTACK(PICCOLO:HART!A1:B100),UNIQUE(FILTER(CHOOSECOLS(a,2),CHOOSECOLS(a,1)=A1)))

 

[orsm6]

Try:

=LET(a,VSTACK(PICCOLO:HART!A1:B100),UNIQUE(FILTER(CHOOSECOLS(a,2),CHOOSECOLS(a,1)=A1)))

hmmm.... it returns: #CALC!

I am not sure what was going through my head at the time i posted the thread... the column containing the week number is column W of all the sheets, not column A.... so sorry!!!

 

[DanteAmor]

the column containing the week number is column W of all the sheets

If the column with names is X, then:

=LET(a,VSTACK(PICCOLO:HART!W1:X100),UNIQUE(FILTER(CHOOSECOLS(a,2),CHOOSECOLS(a,1)=A1)))

Ex:

varios 25sep2024.xlsm
	A																						W	X
1																							30	a
2																							30	b
3																							30	c
4																							32	a
5																							32	b
6																							32	c
7																							32	a
8																							33	b
9																							32	c
10
PICCOLO

varios 25sep2024.xlsm
	A																						W	X
1																							30	a
2																							30	b
3																							30	c
4																							32	x
5																							32	y
6																							32	z
7																							33	a
8																							33	b
9																							33	c
10																							33	d
OTHER

varios 25sep2024.xlsm
	A																						W	X
1																							30	a
2																							30	b
3																							30	c
4																							32	a
5																							32	b
6																							32	x
7																							33	a
8																							33	b
9																							33	c
10																							32	d
11																							32	e
HART

 

[orsm6]

If the column with names is X, then:

=LET(a,VSTACK(PICCOLO:HART!W1:X100),UNIQUE(FILTER(CHOOSECOLS(a,2),CHOOSECOLS(a,1)=A1)))

Ex:

varios 25sep2024.xlsm
	A																						W	X
1																							30	a
2																							30	b
3																							30	c
4																							32	a
5																							32	b
6																							32	c
7																							32	a
8																							33	b
9																							32	c
10
PICCOLO

varios 25sep2024.xlsm
	A																						W	X
1																							30	a
2																							30	b
3																							30	c
4																							32	x
5																							32	y
6																							32	z
7																							33	a
8																							33	b
9																							33	c
10																							33	d
OTHER

varios 25sep2024.xlsm
	A																						W	X
1																							30	a
2																							30	b
3																							30	c
4																							32	a
5																							32	b
6																							32	x
7																							33	a
8																							33	b
9																							33	c
10																							32	d
11																							32	e
HART

View attachment 117330

Hi thank you for your help - I may have confused you.

on all sheets in range (e.g. PICCOLO) unique name list is Column B, week is column W

i tried the following formula and extended my range to 1000 rows, it works great.
=LET(a,VSTACK(PICCOLO:HART!B1:W1000),UNIQUE(FILTER(CHOOSECOLS(a,2),CHOOSECOLS(a,22)=A1)))


thank you for your help