Using an array formula to create lists from a range of data.

[mbefc]

Hi,
I'm looking to create a list of returns/ statements from one sheet and then list them on a second sheet.
I have an array formula ( see image ) which lifts statements from a column based on criteria I set ( there are 3 criteria and I've used the array to create 3 lists - but only for the column highlighted ).

I now want, on my other sheet, to be able to choose which column that array formula lifts the statements from and then list these statements linked to the criteria assigned to the array. i.e. I want to be able to show only the statements from Rec Aut or y3 Spr or Y5 Sum etc columns on the 'other' sheet. I know I could create 21 separate sheets to do this and get it to simply copy the lists created by the array but that seems to be a very convoluted way of doing this.

I'm not very good at Excel but I hope I've explained that well enough that someone might be able to give a simple solution.

Many thanks

 

[StephenCrump]

Welcome to the Forum!

It's a little hard to tell from the picture, but I think you're looking to do something along these lines:

	A	B	C	D	E	F	G	H	I
1
2		LookFor		Name		Result1	Result2	Result3	Result4
3		Sam		Sam		84	51	85	51
4		Result2		Chris		85	53	47	75
5				Someone else		59	77	57	79
6		Results		Sam		71	73	53	66
7		51		Sam		54	76	72	84
8		73		Chris		56	86	87	65
9		76		Chris		75	74	88	62
10		64		Someone else		78	69	67	58
11		54		Sam		79	64	49	49
12				Someone else		90	60	69	46
13				Someone else		90	88	78	48
14				chris		69	60	66	65
15				Sam		49	54	71	74
16				Chris		65	59	63	76
Sheet1

Cell Formulas
Range	Formula
B7:B14	B7	=IFERROR(INDEX(F$3:I$16,SMALL(IF(D$3:D$16=B$3,ROW(D$3:D$16)-ROW(D$3)+1),ROWS(B$7:B7)),MATCH(B$4,F$2:I$2,)),"")

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=$F$2:$I$2

(If you have Excel 365 (?) there's a better way to do this).

 

[mbefc]

Hi,
thanks for your reply, much appreciated. I've had a go at modifying your formula to work on my spreadsheet but can't quite seem to get it to work.

This is the array formula I used to create my lists based on the criteria ( WTd, Ach, NoA ) I'm searching for.

=IFERROR(INDEX($C$6:$C$348,SMALL(IF($V$6:$V$350=$AX$2,ROW($C$6:$C$350)),ROW(1:1))-1,1),"")

AX2 is the reference cell for my first criteria ( WTd )and then I just copied and modified the formula to create the 2 other criteria lists.

This works well but obviously only produces lists based on the criteria in the first column ( V6 ) and I want to be able to get it to produce a list for any column. I have created a data validation list on the other sheet with the column headings ( Aut Rec , Spr Rec etc ) and I somehow need to get that 'criteria' into my formula so it only looks for the results in the specific column I'm after, based on my selection from the data validation list.

Apologies if I'm being a bit thick with this as I can see that your answer appears to do this!!! If you can offer any additional guidance then that would be great.

Thanks

 

[KRice]

Please post a clearer sample of your worksheet...even if it is populated with nonsensical content. The row and column headings should be visible. Ideally the XL2BB add-in would be used, but if that presents some issues, then try uploading a copy to a file exchange site and share the link (e.g., Dropbox)....or repost a picture that shows the headings. From what I can guess, your formula does not have any feature to refer to the preferred column, like this construction... INDEX(ref, row, column)... but I'm only guessing because I can't understand what you are trying to accomplish.

 

[mbefc]

Hi,
thanks for taking the time to reply. Unfortunately I can't install the XL2BB add in as the organisation I work for don't allow that.
I have, however, tried to post a clearer picture with the column and row headings showing.

Your assumption, I think, is correct and I was querying whether that could be incorporated into the formula I already have so that I can choose which columns ( V - AQ ) I can draw the information from that I want.
At present I have a second sheet with a data validation list which refers to the text in cells V3-AQ3. I want to be able to choose a value e.g. Spr Yr 1 from that list which then lists the statements ( column C ) which have specific criteria attached to them e.g. list all the statements with 'WTd' linked to them or those with 'Ach'. This list will then be copied to the other sheet.

I think stephen's response would work but I have been unable to adapt it to work on my sheet.

Excel is not something I have any particular competence in, which I'm sure you've guessed, so apologies if I'm being unclear with my explanations!

Thanks

 

[KRice]

Thanks for the additional information. I almost understand the objective...
You mention that you have two sheets. The sheet shown in your snapshot...I'm assuming that is the source sheet, which I'll call "Sheet1". Sheet1 has a block of information in the range $V$6:$AQ$350 whose cells contain either a blank or "Ach" or "WTd" or "NotA". The headings for this information block are in the range $V$4:$AQ$4. How would one know which of these columns you want to specify?...In other words, what cell holds the text that could be used to match with the column headings in $V$4:$AQ$4? Or do you want all columns that contain the search criterion?

You mention three criteria, and the "first" is described as being located in $AX$2. What does this mean? Are you using only cell $AX$2 to specify only one of three criteria (Ach, WTd, NotA), and then that single criterion is used to query the large information block in V6:AQ350? Or are you doing a more complex match where multiple terms in the large block of information are sought?

The formula you want...where should this formula be shown? It's not clear where you want the return list.

 

[KRice]

See if something like this is what you had in mind. Given the main Sheet1...

Book1
	A	B	C																		U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2
3																						Rec Aut	Rec Spr	Rec Sum	Yr 1 Aut	Yr 1 Spr	Yr 1 Sum	Yr 2 Aut	Yr 2 Spr	Yr 2 Sum	Yr 3 Aut
4			Anticipation & Enjoyment of Interaction
5
6		1.1	Generally accepts																				WTd		NotA
7
8		1.1	Accepts presences																			Ach
9
10		1.1	Acccepts exposure																				WTd	Ach	NotA
11
12		1.1	Acknowledges others'																			NotA		NotA
13
14		1.2	Shows anticipation																						Ach
15
16		1.2	Communicates awareness																					Ach	NotA
17
18		1.2	Accepts or																			NotA
19
20		1.2	Anticipates when																				Ach
21
22		1.3	Expresses pleasure																					NotA
23
24		1.3	Develops clear																				Ach		NotA
25
26		1.3	Restores enjoyment																			Ach		Ach
27
28		1.3	Gives evidence																				NotA
29
30		1.3	Shows anticipation																				Ach
Sheet1

A query is made in Sheet2 using two inputs (blue cells): 1) one of three search terms to be found in the main data table, and 2) the column name of the main data table. The list is returned in the green cells.

Book1
	B	C	D
1		1st Criteria	Column
2		NotA	Rec Aut
3
4	List of Sheet1 Column C entries
5	Acknowledges others'
6	Accepts or
7
8
9
10
11
12
13
14
15
16
Sheet2

Cell Formulas
Range	Formula
B5:B16	B5	=IFERROR(INDEX(Sheet1!$C$6:$C$350,AGGREGATE(15,6,(ROW(Sheet1!$B$6:$B$350)-ROW(Sheet1!$B$5))/((Sheet1!$V$6:$AQ$350=$C$2)*(Sheet1!$V$3:$AQ$3=$D$2)),ROWS(B$5:B5))),"")

Cells with Data Validation
Cell	Allow	Criteria
C2	List	WTd, Ach, NotA
D2	List	=Sheet1!$V$3:$AQ$3

 

[mbefc]

See if something like this is what you had in mind. Given the main Sheet1...

Book1
	A	B	C																		U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2
3																						Rec Aut	Rec Spr	Rec Sum	Yr 1 Aut	Yr 1 Spr	Yr 1 Sum	Yr 2 Aut	Yr 2 Spr	Yr 2 Sum	Yr 3 Aut
4			Anticipation & Enjoyment of Interaction
5
6		1.1	Generally accepts																				WTd		NotA
7
8		1.1	Accepts presences																			Ach
9
10		1.1	Acccepts exposure																				WTd	Ach	NotA
11
12		1.1	Acknowledges others'																			NotA		NotA
13
14		1.2	Shows anticipation																						Ach
15
16		1.2	Communicates awareness																					Ach	NotA
17
18		1.2	Accepts or																			NotA
19
20		1.2	Anticipates when																				Ach
21
22		1.3	Expresses pleasure																					NotA
23
24		1.3	Develops clear																				Ach		NotA
25
26		1.3	Restores enjoyment																			Ach		Ach
27
28		1.3	Gives evidence																				NotA
29
30		1.3	Shows anticipation																				Ach
Sheet1

A query is made in Sheet2 using two inputs (blue cells): 1) one of three search terms to be found in the main data table, and 2) the column name of the main data table. The list is returned in the green cells.

Book1
	B	C	D
1		1st Criteria	Column
2		NotA	Rec Aut
3
4	List of Sheet1 Column C entries
5	Acknowledges others'
6	Accepts or
7
8
9
10
11
12
13
14
15
16
Sheet2

Cell Formulas
Range	Formula
B5:B16	B5	=IFERROR(INDEX(Sheet1!$C$6:$C$350,AGGREGATE(15,6,(ROW(Sheet1!$B$6:$B$350)-ROW(Sheet1!$B$5))/((Sheet1!$V$6:$AQ$350=$C$2)*(Sheet1!$V$3:$AQ$3=$D$2)),ROWS(B$5:B5))),"")

Cells with Data Validation
Cell	Allow	Criteria
C2	List	WTd, Ach, NotA
D2	List	=Sheet1!$V$3:$AQ$3

Morning,
thanks for your replies. That's brilliant what you have shared as that works perfectly.
Many thanks for all your help.

 

[KRice]

We're happy to help. Thanks for the feedback.

 

[Peter_SSs]

@mbefc
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have Excel 365 then you can do this with a fairly short single formula in a single cell. With the formula shown in B5 below, the other results will automatically 'spill' down the column for the required number of rows.

Same Sheet1 as post #7

mbefc.xlsm
	B	C	D
1		1st Criteria	Column
2		Ach	Rec Spr
3
4	List of Sheet1 Column C entries
5	Anticipates when
6	Develops clear
7	Shows anticipation
8
9
Sheet2

Cell Formulas
Range	Formula
B5:B7	B5	=FILTER(Sheet1!C6:C350,INDEX(Sheet1!V6:AQ350,0,MATCH(D2,Sheet1!V3:AQ3,0))=C2,"")
Dynamic array formulas.