Index is returning a blank cell in the middle of the data set

[Labrat24]

I have a spreadsheet that contains a couple hunded date/times with coresponding sample results. I use several different arrays to combine multiple sets of dates into one column and then use this formula to organize them by date and ignore blank cells, =INDEX($AI$5:$AI$200,MATCH(ROWS($AI$5:AI5),COUNTIF($AI$5:$AI$200,"<="&$AI$5:$AI$200),0)). The data that I get comes from another program that can dump information into excel. This formula has been working great for e up until he beginning of this week. For some reason it wants to skip row 99. It doesnt give an error or a number of anykind, just a blank cell. I had someone else tell me to use a the SMALL formula but this causes several other issues with graphing and my company has blocked the use of macros. If someone could please help explain why this keeps happening or has another formula that wouldwork that would be great. Please let me know if more information is needed.

 

[RasGhul]

Can you post some sample data?

 

[Labrat24]

Date	Date
10/1/18 8:30 AM	`=INDEX($AI$5:$AI$200,MATCH(ROWS($AI$5:AI5),COUNTIF($AI$5:$AI$200,"<="&$AI$5:$AI$200),0))
10/5/18 2:30 PM	10/1/18 8:30 AM
10/9/18 5:00 PM	10/2/18 10:30 AM
10/10/18 6:30 AM	10/5/18 2:30 PM
10/10/18 4:00 PM	10/8/18 8:00 AM
10/11/18 6:30 AM	10/9/18 5:00 PM
10/11/18 5:30 PM	10/10/18 6:30 AM
10/29/18 9:30 AM	10/10/18 4:00 PM
10/29/18 5:30 PM	10/11/18 6:30 AM
11/5/18 8:30 AM	10/11/18 5:30 PM
11/9/18 8:30 AM	10/29/18 9:30 AM
11/14/18 8:30 AM	10/29/18 5:30 PM
11/19/18 9:00 AM	11/1/18 9:30 AM
11/26/18 7:30 AM	11/5/18 8:30 AM
11/30/18 9:00 AM	11/7/18 7:30 AM
12/5/18 10:00 AM	11/9/18 8:30 AM
12/10/18 9:30 AM	11/12/18 9:30 AM
12/14/18 8:30 AM	11/14/18 8:30 AM
12/19/18 8:30 AM	11/16/18 8:30 AM
12/26/18 8:30 AM	11/19/18 9:00 AM
12/31/18 9:00 AM	11/21/18 6:30 AM
1/7/19 8:30 AM	11/26/18 7:30 AM
1/11/19 10:30 AM	11/28/18 7:30 AM
1/16/19 9:00 AM	11/30/18 9:00 AM
1/21/19 7:30 AM	12/3/18 9:30 AM
1/25/19 8:30 AM	12/5/18 10:00 AM
1/30/19 8:30 AM	12/7/18 8:30 AM
1/31/19 7:30 AM	12/10/18 9:30 AM
2/8/19 9:00 AM	12/12/18 11:30 AM
2/13/19 12:30 PM	12/14/18 8:30 AM
2/18/19 8:30 AM	12/17/18 8:30 AM
2/22/19 9:30 AM	12/19/18 8:30 AM
2/27/19 9:30 AM	12/21/18 2:30 PM
3/4/19 8:30 AM	12/26/18 8:30 AM
3/11/19 8:30 AM	12/28/18 7:30 AM
3/15/19 7:30 AM	12/31/18 9:00 AM
3/20/19 4:30 PM	1/3/19 9:30 AM
3/25/19 10:00 AM	1/7/19 8:30 AM
3/29/19 8:30 AM	1/9/19 9:30 AM
4/3/19 8:30 AM	1/11/19 10:30 AM
4/11/19 9:30 AM	1/14/19 9:30 AM
4/17/19 1:30 PM	1/16/19 9:00 AM
4/22/19 12:30 PM	1/18/19 7:30 AM
4/29/19 9:30 AM	1/21/19 7:30 AM
5/6/19 8:30 AM	1/23/19 2:30 PM
5/13/19 8:00 AM	1/25/19 8:30 AM
5/16/19 10:00 AM	1/28/19 9:30 AM
5/18/19 9:00 AM	1/30/19 8:30 AM
5/23/19 1:30 PM	1/31/19 7:30 AM
5/30/19 4:00 PM	2/4/19 7:30 AM
6/5/19 11:30 AM	2/8/19 9:00 AM
6/10/19 9:30 AM	2/11/19 9:00 AM
6/14/19 9:00 AM	2/13/19 12:30 PM
6/19/19 9:00 AM	2/15/19 7:30 AM
	2/18/19 8:30 AM
10/2/18 10:30 AM	2/20/19 10:30 AM
10/8/18 8:00 AM	2/22/19 9:30 AM
11/1/18 9:30 AM	2/25/19 10:30 AM
11/7/18 7:30 AM	2/27/19 9:30 AM
11/12/18 9:30 AM	3/1/19 7:30 AM
11/16/18 8:30 AM	3/4/19 8:30 AM
11/21/18 6:30 AM	3/7/19 10:00 AM
11/28/18 7:30 AM	3/11/19 8:30 AM
12/3/18 9:30 AM	3/13/19 12:30 PM
12/7/18 8:30 AM	3/15/19 7:30 AM
12/12/18 11:30 AM	3/18/19 9:30 AM
12/17/18 8:30 AM	3/20/19 4:30 PM
12/21/18 2:30 PM	3/22/19 3:00 PM
12/28/18 7:30 AM	3/25/19 10:00 AM
1/3/19 9:30 AM	3/27/19 2:30 PM
1/9/19 9:30 AM	3/29/19 8:30 AM
1/14/19 9:30 AM	4/1/19 11:30 AM
1/18/19 7:30 AM	4/3/19 8:30 AM
1/23/19 2:30 PM	4/8/19 8:30 AM
1/28/19 9:30 AM	4/11/19 9:30 AM
2/4/19 7:30 AM	4/15/19 8:30 AM
2/11/19 9:00 AM	4/17/19 1:30 PM
2/15/19 7:30 AM	4/19/19 10:00 AM
2/20/19 10:30 AM	4/22/19 12:30 PM
2/25/19 10:30 AM	4/26/19 7:30 AM
3/1/19 7:30 AM	4/29/19 9:30 AM
3/7/19 10:00 AM	5/1/19 11:30 AM
3/13/19 12:30 PM	5/6/19 8:30 AM
3/18/19 9:30 AM	5/10/19 8:30 AM
3/22/19 3:00 PM	5/13/19 8:00 AM
3/27/19 2:30 PM	5/15/19 8:30 AM
4/1/19 11:30 AM	5/16/19 10:00 AM
4/8/19 8:30 AM	5/17/19 10:00 AM
4/15/19 8:30 AM	5/18/19 9:00 AM
4/19/19 10:00 AM	5/20/19 12:30 PM
4/26/19 7:30 AM	5/23/19 1:30 PM
5/1/19 11:30 AM	5/28/19 9:30 AM
5/10/19 8:30 AM	5/30/19 4:00 PM
5/15/19 8:30 AM	6/3/19 8:30 AM
5/17/19 10:00 AM	6/5/19 11:30 AM
5/20/19 12:30 PM
5/28/19 9:30 AM	6/10/19 9:30 AM
6/3/19 8:30 AM	6/12/19 2:30 PM
6/7/19 7:30 AM	6/14/19 9:00 AM
6/12/19 2:30 PM	6/17/19 10:00 AM
6/17/19 10:00 AM	6/19/19 9:00 AM
6/21/19 9:00 AM	6/21/19 9:00 AM

<tbody>
</tbody>

 

[Labrat24]

Sorry about how messy that works, I only had a couple minutes to put it up and couldnt figure out how to put it in as a table. The column on the right is two lists that are combined and seperated by a blank in the middle. Too get that column I used the formula =IFERROR(INDEX(North,MATCH(0,INDEX(COUNTIF($AI$4:AI4,North),,),)),IFERROR(INDEX(South,MATCH(0,INDEX(COUNTIF($AI$4:AI4,South),,),)),"")) to remove duplicates and combine two lists. Like I said earlier the data comes from an outside source and is dumped into exel. If you have more questions dont hesitate to ask.

 

[RasGhul]

Maybe try;

=IFERROR(LOOKUP(2,1/(COUNTIF($AJ$4:AJ4,$AI$5:$AI$106)=0),$AI$5:$AI$106),IFERROR(LOOKUP(2,1/(COUNTIF($AJ$4:AJ4,$AL$5:$AL$106)=0),$AL$5:$AL$106),""))

This returned unique values for me with no blanks between $AI$5:$AI$106 & $AL$5:$AL$106.

 

[Labrat24]

AJ/AL are empty rows so you may have to elaborate on how to impliment your formula. Thanks