Array to bring in a list of unique values

[mattjpep]

I have the following formula that is currently giving me a list of unique names based on a condition in cell E2:

{=IF(COUNTIFS('MASTER ATTEND'!$A$10:$B$242,$E$2)<ROWS($B$10:B10),"",INDEX('MASTER ATTEND'!$O$10:$O$242,SMALL(IF($E$2='MASTER ATTEND'!$A$10:$B$242,ROW('MASTER ATTEND'!$A$10:$B$242)-MIN(ROW('MASTER ATTEND'!$A$10:$B$242))+1,""),ROW(A1))))}

How would I edit this formula to meet a second condition in cell E4 that matches data in cells 'MASTER ATTEND'!$C$10:$C$242? Any help would be greatly appreciated. Thank you!!

 

[Aladin Akyurek]

Care to post a small sample (input) along with the output that goes with that input?

 

[mattjpep]

Here is the input data:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Present
Non-contract/Holiday
Transfer
Last day of enrollment
Best Interest Day
	Unexcused Absence
First Day of enrollment
Excused absence-SF
PMT METHOD	PMT CHANGE	ROOM	Birthddate	First	Last	LAST,FIRST

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]X[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]N[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]T[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]L[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]B[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]U[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]E[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFCC]#FFFFCC[/URL] , align: center"]8/1/17[/TD]
[TD="align: center"]8/1/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFCC]#FFFFCC[/URL] , align: center"]8/31/17[/TD]
[TD="align: center"]8/31/2017[/TD]
[TD="align: center"]8/1/2017[/TD]
[TD="align: center"]8/31/2017[/TD]
[TD="align: center"]mid-month change?[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]A[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]room type[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]mo[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]mo[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Report Age[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]REPORT AGE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]ADD LINE[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]CCAMPIS[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]1121[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]PRESCH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]1/19/2013[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]no[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Billy[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Holmes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Holmes, Billy[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]CCAMPIS[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]125[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]INFANT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]2/20/2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]TODDLERS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]TODDLERS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]no[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Daniel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Bell[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Bell, Daniel[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]CCAMPIS[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]127[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]TODDLER[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]4/9/2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]39[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]no[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Jill[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Ardary[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Ardary, Jill[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]CCAMPIS[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]1127[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]PRESCH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]10/5/2010[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]81[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]82[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]no[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Michelle[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Smith, Michelle[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]CCAMPIS[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]1127[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]PRESCH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]5/30/2012[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]62[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]63[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]no[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Scott[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Reece[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Reece, Scott[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]CCAMPIS[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]1131[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: center"]PRESCH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]10/4/2012[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]57[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]58[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]3-4 YRS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]no[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Omar[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] "]Lopez[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Lopez, Omar[/TD]

</tbody>

MASTER ATTEND​

Here is the output. The formula I need to edit is in cell B10.

	A	B	C	D	E
DAILY ENROLLMENT AND ATTENDANCE REGISTER
CCAMPIS
UCR CHILD DEVELOPMENT CENTER
NAME	DOB	MO FEE	CDE LINE
Holmes, Billy	Three and Four Year Olds
Bell, Daniel	Infants
Ardary, Jill	Toddlers
Smith, Michelle	Limited and Non-English Proficient
Reece, Scott	Limited and Non-English Proficient
Lopez, Omar	Limited and Non-English Proficient

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Agency/Site:[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"]CLASSROOM:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFEB9C]#FFEB9C[/URL] "]1121[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/19/2013[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2/20/2015[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4/9/2014[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/5/2010[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5/30/2012[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/4/2012[/TD]
[TD="align: right"] -[/TD]

</tbody>

REPORTS​

 

[Aladin Akyurek]

Example...

Sheet1 (input)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]Field-1[/td][td]Field-2[/td][td]Field-3[/td][td]Field-4[/td][td]Field-5[/td][td]Field-6[/td][/tr]
[tr][td]

2​

[/td][td]jad[/td][td]london[/td][td]

15​

[/td][td]XAZ[/td][td]

8/1/2017​

[/td][td]

0.1​

[/td][/tr]
[tr][td]

3​

[/td][td]nad[/td][td]oxford[/td][td]

45​

[/td][td]QAZ[/td][td]

8/1/2017​

[/td][td]

0.2​

[/td][/tr]
[tr][td]

4​

[/td][td]lad[/td][td]york[/td][td]

15​

[/td][td]WAZ[/td][td]

8/12/2017​

[/td][td]

0.15​

[/td][/tr]
[tr][td]

5​

[/td][td]vad[/td][td]london[/td][td]

30​

[/td][td]XAZ[/td][td]

8/15/2017​

[/td][td]

0.026​

[/td][/tr]
[/table]

Sheet2 (output)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]london[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]XAZ[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]count[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]idx[/td][td]Field-1[/td][td]Field-3[/td][td]Field-5[/td][/tr]
[tr][td]

6​

[/td][td]

1​

[/td][td]jad[/td][td]

15​

[/td][td]

8/1/2017​

[/td][/tr]
[tr][td]

7​

[/td][td]

4​

[/td][td]vad[/td][td]

30​

[/td][td]

8/15/2017​

[/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Conditions the data must meet...

A1: London

A2: XAZ

A3: count

In A4 just enter:

=COUNTIFS(Sheet1!B2:B5,A1,Sheet1!D2:D5,A2)

In A6 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$6:A6)>$A$4,"",SMALL(IF(Sheet1!$B$2:$B$5=$A$1,IF(Sheet1!$D$2:$D$5=$A$2,ROW(Sheet1!$A$2:$F$5)-ROW(Sheet1!$A$2)+1)),ROWS($A$6:A6)))

In B6 just enter, copy across, and down:

=IF($A6="","",INDEX(Sheet1!$A$2:$F$5,$A6,MATCH(B$5,Sheet1!$A$1:$F$1,0)))

Hope you can copy-and-adapt this set up to you situation, which is robust and efficient for creating a formula-based sublist.