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

mbefc

New Member
Joined
Dec 17, 2014
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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 ).
1613472530989.png

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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,116
Office Version
  1. 365
Platform
  1. Windows
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:

ABCDEFGHI
1
2LookForNameResult1Result2Result3Result4
3SamSam84518551
4Result2Chris85534775
5Someone else59775779
6ResultsSam71735366
751Sam54767284
873Chris56868765
976Chris75748862
1064Someone else78696758
1154Sam79644949
12 Someone else90606946
13 Someone else90887848
14 chris69606665
15Sam49547174
16Chris65596376
Sheet1
Cell Formulas
RangeFormula
B7:B14B7=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
CellAllowCriteria
B4List=$F$2:$I$2

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

mbefc

New Member
Joined
Dec 17, 2014
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,055
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 17, 2014
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
1613569731776.png

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

Well-known Member
Joined
Dec 9, 2003
Messages
1,055
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,055
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

See if something like this is what you had in mind. Given the main Sheet1...
Book1
ABCUVWXYZAAABACADAE
1 
2
3Rec AutRec SprRec SumYr 1 AutYr 1 SprYr 1 SumYr 2 AutYr 2 SprYr 2 SumYr 3 Aut
4Anticipation & Enjoyment of Interaction
5
61.1Generally acceptsWTdNotA
7
81.1Accepts presencesAch
9
101.1Acccepts exposureWTdAchNotA
11
121.1Acknowledges others'NotANotA
13
141.2Shows anticipationAch
15
161.2Communicates awarenessAchNotA
17
181.2Accepts orNotA
19
201.2Anticipates whenAch
21
221.3Expresses pleasureNotA
23
241.3Develops clearAchNotA
25
261.3Restores enjoymentAchAch
27
281.3Gives evidenceNotA
29
301.3Shows anticipationAch
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
BCD
11st CriteriaColumn
2NotARec Aut
3
4List of Sheet1 Column C entries
5Acknowledges others'
6Accepts or
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
Sheet2
Cell Formulas
RangeFormula
B5:B16B5=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
CellAllowCriteria
C2ListWTd, Ach, NotA
D2List=Sheet1!$V$3:$AQ$3
 
Solution

mbefc

New Member
Joined
Dec 17, 2014
Messages
5
Office Version
  1. 365
Platform
  1. Windows
See if something like this is what you had in mind. Given the main Sheet1...
Book1
ABCUVWXYZAAABACADAE
1 
2
3Rec AutRec SprRec SumYr 1 AutYr 1 SprYr 1 SumYr 2 AutYr 2 SprYr 2 SumYr 3 Aut
4Anticipation & Enjoyment of Interaction
5
61.1Generally acceptsWTdNotA
7
81.1Accepts presencesAch
9
101.1Acccepts exposureWTdAchNotA
11
121.1Acknowledges others'NotANotA
13
141.2Shows anticipationAch
15
161.2Communicates awarenessAchNotA
17
181.2Accepts orNotA
19
201.2Anticipates whenAch
21
221.3Expresses pleasureNotA
23
241.3Develops clearAchNotA
25
261.3Restores enjoymentAchAch
27
281.3Gives evidenceNotA
29
301.3Shows anticipationAch
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
BCD
11st CriteriaColumn
2NotARec Aut
3
4List of Sheet1 Column C entries
5Acknowledges others'
6Accepts or
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
Sheet2
Cell Formulas
RangeFormula
B5:B16B5=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
CellAllowCriteria
C2ListWTd, Ach, NotA
D2List=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

Well-known Member
Joined
Dec 9, 2003
Messages
1,055
Office Version
  1. 2019
Platform
  1. Windows
We're happy to help. Thanks for the feedback.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,344
Office Version
  1. 365
Platform
  1. Windows
@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
BCD
11st CriteriaColumn
2AchRec Spr
3
4List of Sheet1 Column C entries
5Anticipates when
6Develops clear
7Shows anticipation
8
9
Sheet2
Cell Formulas
RangeFormula
B5:B7B5=FILTER(Sheet1!C6:C350,INDEX(Sheet1!V6:AQ350,0,MATCH(D2,Sheet1!V3:AQ3,0))=C2,"")
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,426
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top