Help with array formula

[alombia]

A	B	C	D	E	F	G	H	I…
1	Name	Year	Eng 1	Eng 2	Eng 3	Mat1	Mat2	Mat 3…
2	John	1	34	23	57	97	90	34
3	Sam	1	34	76	87	34	10	90
4	Luke	2	67	98	45	90	34	98
5	Lucy	3	98	67	45	67	86	90
6	May	3	7	54	76	23	94	38

<tbody>
</tbody>

Hi,
I would like help with a formula to retrieve filtered results from the above Sheet called Sheet1 and pasted into Sheet2 in say Cell A1
There are two selection criteria: Year and Subject. I need the formula to display all the results matching the selected criteria. For example, Year 1 and Eng. So Cell A1 has the Year value. Cell A2 has the subject value (subjects are in cell D1:I1)
I’m guessing an array formula would be good so that it can be pasted over into different cells.

 

[James006]

Hi,

As an initial step, with Data as the first sheet, in cell B2 of your second sheet, you could test :

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$C$2:$C$6,$A$1),INDEX(Data!$B$2:$B$6,SMALL(IF(Data!$C$2:$C$6=$A$1,ROW(Data!$C$2:$C$6)-ROW(Data!$C$2)+1),ROWS(B$2:B2))),"")

HTH

 

[alombia]

Hi,

As an initial step, with Data as the first sheet, in cell B2 of your second sheet, you could test :

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$C$2:$C$6,$A$1),INDEX(Data!$B$2:$B$6,SMALL(IF(Data!$C$2:$C$6=$A$1,ROW(Data!$C$2:$C$6)-ROW(Data!$C$2)+1),ROWS(B$2:B2))),"")

HTH

Imasad
Im confused. Your formula does not reference the search criteria. The search values are contained in cell A1 and B1 of sheet2. The formula needs to return results that match both criteria. Here is an example of a returned result:

Criteria in A1= 1 Criteria in B1 = Eng

Results:

1	Name	Year	Eng 1	Eng 2	Eng 3
2	John	1	34	23	57
3	Sam	1	34	76	87

<tbody>
</tbody>

Criteria in A1 = 3 Criteria in B1= Mat
Results:

1 2	Name Lucy	Year 3	Mat1 67	Mat2 86	Mat3 90
2	May	3	23	94	34

<tbody>
</tbody>

 

[63falcondude]

SUPER ugly and probably not very robust formulas here...

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Year	Eng 1	Eng 2	Eng 3	Mat1	Mat2	Mat3
2	John	1	34	23	57	97	90	34
3	Sam	1	34	76	87	34	10	90
4	Luke	2	67	98	45	90	34	98
5	Lucy	3	98	67	45	67	86	90
6	May	3	7	54	76	23	94	38
Sheet1

Excel 2010
	A	B	C	D	E
1	Year	Subject
2	1	Eng
3
4	Name	Year	Eng 1	Eng 2	Eng 3
5	John	1	34	23	57
6	Sam	1	34	76	87
Sheet2

Cell Formulas
Range	Formula
B5	=IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(A5,Sheet1!$A$2:$A$6,0)),"")
C5	=IF(SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6))=0,"",SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6)))
C4	{=IFERROR(INDEX(Sheet1!$C1:$H1,SMALL(IF(LEFT(Sheet1!$C1:$H1,3)=$B$2,COLUMN(Sheet1!$C1:$H1)),COLUMN(A$1:A$6))-2),"")}
A5	{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A$2,ROW(Sheet1!$B$2:$B$6)-1),ROW(Sheet1!1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Formulas in column A and B are dragged down. Formula in C4 is dragged to the right and the formula in C5 is dragged down and to the right. Hopefully you can adapt this to your needs.

 

[James006]

Imasad
Im confused. Your formula does not reference the search criteria. The search values are contained in cell A1 and B1 of sheet2. The formula needs to return results that match both criteria. Here is an example of a returned result:

Criteria in A1= 1 Criteria in B1 = Eng

Results:

1	Name	Year	Eng 1	Eng 2	Eng 3
2	John	1	34	23	57
3	Sam	1	34	76	87

<tbody>
</tbody>

Criteria in A1 = 3 Criteria in B1= Mat
Results:

1 2	Name Lucy	Year 3	Mat1 67	Mat2 86	Mat3 90
2	May	3	23	94	34

<tbody>
</tbody>

I am confused by the fact that you are confused ... :wink:

Test the array formula in Sheet 2 ... and you will see it refers to search criteria ... and generates your list of Unique individuals ...

HTH

 

[alombia]

Hi,
Yes, i should not have been confused, as it works fine. Many thanks

 

[alombia]

SUPER ugly and probably not very robust formulas here...

Excel 2010

	A	B	C	D	E	F	G	H
1	Name	Year	Eng 1	Eng 2	Eng 3	Mat1	Mat2	Mat3
2	John	1	34	23	57	97	90	34
3	Sam	1	34	76	87	34	10	90
4	Luke	2	67	98	45	90	34	98
5	Lucy	3	98	67	45	67	86	90
6	May	3	7	54	76	23	94	38

<tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E
1	Year	Subject
2	1	Eng
3
4	Name	Year	Eng 1	Eng 2	Eng 3
5	John	1	34	23	57
6	Sam	1	34	76	87

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B5 =IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(A5,Sheet1!$A$2:$A$6,0)),"") C5 =IF(SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6))=0,"",SUMPRODUCT(($A5=Sheet1!$A$2:$A$6)*(C$4=Sheet1!$C$1:$H$1)*(Sheet1!$C$2:$H$6))) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A5 {=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A$2,ROW(Sheet1!$B$2:$B$6)-1),ROW(Sheet1!1:1))),"")} C4 {=IFERROR(INDEX(Sheet1!$C1:$H1,SMALL(IF(LEFT(Sheet1!$C1:$H1,3)=$B$2,COLUMN(Sheet1!$C1:$H1)),COLUMN(A$1:A$6))-2),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Formulas in column A and B are dragged down. Formula in C4 is dragged to the right and the formula in C5 is dragged down and to the right. Hopefully you can adapt this to your needs.

This works fine for the most part, but where there are duplicate names in sheet 1 column A, it produces incorrect data. For example:

	A	B	C	D	E	F	G	H
1	Name	Year	Eng 1	Eng 2	Eng 3	Mat1	Mat2	Mat3
2	John	1	34	23	57	97	90	34
3	May	1	34	76	87	34	10	90
4	Luke	2	67	98	45	90	34	98
5	Lucy	3	98	67	45	67	86	90
6	May	3	7	54	76	23	94	3

<tbody>
</tbody>

In the above, you can see that A3 and A6 have same names but different Years in B. But your formula retrieves A3, even when '3' is selected in A2 of sheet2. What is happening?

 

[James006]

Hi,
Yes, i should not have been confused, as it works fine. Many thanks

Glad you could fix your problem ...

Thanks ... for your thanks ... :wink:

 

[alombia]

When i say it works, i mean it does generate the unique list. However, i cant seem to tweak to get the rest of the data, thats why i used the formula provided by the second kind reply. But then i have the problem as mentioned above.

 

[James006]

With the same layout for Sheet 2 as above ...the year in cell A1 and the topic Eng 1 in cell A2 ...

in cell B2, you can test following formula : =INDEX(Data!$A$1:$I$6,MATCH(B2,Data!$B$1:$B$6,0),MATCH($A$2,Data!$A$1:$I$1,0))

HTH