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]

[TABLE="width: 184"]
<tbody>[TR]
[TD="width: 122, bgcolor: transparent"]Date
[/TD]
[TD="width: 122, bgcolor: transparent"]Date
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/1/18 8:30 AM
[/TD]
[TD="bgcolor: transparent"]`=INDEX($AI$5:$AI$200,MATCH(ROWS($AI$5:AI5),COUNTIF($AI$5:$AI$200,"<="&$AI$5:$AI$200),0))
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/5/18 2:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]10/1/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/9/18 5:00 PM
[/TD]
[TD="bgcolor: transparent, align: right"]10/2/18 10:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/10/18 6:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]10/5/18 2:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/10/18 4:00 PM
[/TD]
[TD="bgcolor: transparent, align: right"]10/8/18 8:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/11/18 6:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]10/9/18 5:00 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/11/18 5:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]10/10/18 6:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/29/18 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]10/10/18 4:00 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/29/18 5:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]10/11/18 6:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/5/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]10/11/18 5:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/9/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]10/29/18 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/14/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]10/29/18 5:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/19/18 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/1/18 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/26/18 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/5/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/30/18 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/7/18 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/5/18 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/9/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/10/18 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/12/18 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/14/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/14/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/19/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/16/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/26/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/19/18 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/31/18 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/21/18 6:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/7/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/26/18 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/11/19 10:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/28/18 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/16/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]11/30/18 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/21/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/3/18 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/25/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/5/18 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/30/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/7/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/31/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/10/18 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/8/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/12/18 11:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/13/19 12:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]12/14/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/18/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/17/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/22/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/19/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/27/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/21/18 2:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/4/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/26/18 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/11/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/28/18 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/15/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12/31/18 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/20/19 4:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]1/3/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/25/19 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/7/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/29/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/9/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/3/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/11/19 10:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/11/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/14/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/17/19 1:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]1/16/19 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/22/19 12:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]1/18/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/29/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/21/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/6/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/23/19 2:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/13/19 8:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/25/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/16/19 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/28/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/18/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1/30/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/23/19 1:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]1/31/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/30/19 4:00 PM
[/TD]
[TD="bgcolor: transparent, align: right"]2/4/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/5/19 11:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/8/19 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/10/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/11/19 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/14/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/13/19 12:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/19/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/15/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2/18/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/2/18 10:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/20/19 10:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/8/18 8:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/22/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/1/18 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/25/19 10:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/7/18 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]2/27/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/12/18 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/1/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/16/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/4/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/21/18 6:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/7/19 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/28/18 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/11/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/3/18 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/13/19 12:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/7/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/15/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/12/18 11:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/18/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/17/18 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/20/19 4:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/21/18 2:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]3/22/19 3:00 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12/28/18 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/25/19 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/3/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/27/19 2:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/9/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]3/29/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/14/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/1/19 11:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/18/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/3/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/23/19 2:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]4/8/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/28/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/11/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/4/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/15/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/11/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/17/19 1:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/15/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/19/19 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/20/19 10:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/22/19 12:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/25/19 10:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/26/19 7:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/1/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]4/29/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/7/19 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/1/19 11:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/13/19 12:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]5/6/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/18/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/10/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/22/19 3:00 PM
[/TD]
[TD="bgcolor: transparent, align: right"]5/13/19 8:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/27/19 2:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]5/15/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/1/19 11:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/16/19 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/8/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/17/19 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/15/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/18/19 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/19/19 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/20/19 12:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/26/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/23/19 1:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/1/19 11:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/28/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/10/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]5/30/19 4:00 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/15/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/3/19 8:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/17/19 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/5/19 11:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/20/19 12:30 PM
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/28/19 9:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/10/19 9:30 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/3/19 8:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/12/19 2:30 PM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/7/19 7:30 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/14/19 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/19 2:30 PM
[/TD]
[TD="bgcolor: transparent, align: right"]6/17/19 10:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/17/19 10:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/19/19 9:00 AM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/21/19 9:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]6/21/19 9:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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