Smaller file

First off, I am using excel 2013 on a work computer. I can not install Ktools, nor am I able to use VBA or macros.

I have a file that is only 3 pages.
Sheet 1) copy and pasted data from the intranet. This data is 13 columns wide and can be 200,000 rows long.
sheet 2) takes data from sheet one and converts it to usable info. This sheet is set to go to 200,000 rows so no data is missed. This sheet is TABLE formatted.
Sheet 3) In this sheet where the user enters info to search through all this data. There is a cell to enter a name, a cell to enter start search date, start search time, end search date, end search time
Sheet 3) also has a TABLE format that shows only the rows with data from sheet 2 that matches the search criteria. The table right now, is formatted also to 200,000 rows. If the data from sheet 2 in say row 2 does not meet the search criteria from sheet 3, The row in sheet 3 is left blank. So I have to have this table also set to 200,000 rows.

In sheet 2 there is a column M. In this column there is a type that will need to be searched. For argument sake, we will name these possibilities one, two, three, four, five, six, seven, eight, and blank, as in ""

SHEET 3 TABLE FORMULAS
In sheet 3 below the search criteria there is a row that looks at each individual possible of column M
I.E. In sheet 3, starting at b8 that has these options hard coded in it, b8=ONE, c8=two,d8=THREE, ETC...

Column 1 of table in sheet 3 uses this following format
iferror(vlookup($B$2,'SHEET2"!a1,1),""). This formula checks to see if the name entered in b2 matches in column1 row1 of table in sheet 2,

Column2 of table in sheet 3
if(@column1="","", if(@column7<>"","",b1 of sheet 2)).

Column 3 of table in sheet 3, is set to the same except c1 of sheet 2
Columns 4-6 uses the same pattern above with only the column of table in sheet 2 increasing.
Column 7 looks at the date and time supplied by user from above. if(column3&column4 < $d$1&$t$1,"BEFORE",if(column3&column4>$d$2&$t$2,"AFTER","") column3 is date column, column 4 is time column. This is explained later on.
Yes, you are all right. As you can see there is circular references.

The method above works.

However, this also requires that on table in sheet three to be formatted to 200,000 rows as weill. This makes the spreadsheet quite large and very slow to open. 3 sheets, 3 tables, each set to 200,000 rows.

Can anybody tell me if there is a way to make this work with having the table in sheet 3 to only 1,000 rows and still capture everything?

Time and date columns exlained.
In the copied data that is pasted in sheet 1, the date and time are not in any way standard format. Date is formatted as yyymmdd. Time is formated as hhmmss. However the info is entered from right to left, and does not show leading 0's and without any :. the time is also already formatted in 24hr. This all means that if the time is 00:00:00 you will see nothing, 00:00:01 shows 1, 00:01:01 shows 101. I fixed this in sheet 2 by first =sheet1time,TEXT("000000"). This forces all leading 0's to show and will always show as hhmmss. It will always have six digits.The time is not pre-formatted with semicolons :. i tried to use TEXT(sheet1time("00:00:00") but all I got was "VALUE" error.

I fixed this issue as well. In sheet 2, I have columns that take that time and converts into actual military format, It takes me an additional 5 cloumns to convert. Its not elegant but it works. This however does increase the file size. Five additional columns at 200,000 rows.

My main goal here is to make sheet 3 table smaller and still get the info I need. Please look at the formulas under SHEET 3 TABLE FORMULAS and see if that can be modified to work better. I do know that I need the table in sheet 3 to be formatted to 1,000 but 200,000.

Thanks for reading and any advice is much appreciated.

In a situation like this I would use a helper column to identify which records match the criteria chosen and allocates a sequential number to each match record. This could be in sheet2, say in cell X2, and could be something like:

=IF(AND(date>=start_date,date<=end_date),MAX(X$1:X1)+1,"-")

In this particular case it would look at the date column (date would be, say, D2 in the same sheet) to see if the date fell between the start and end dates (the criteria specified on Sheet3), and if so it would allocate the next number in sequence. This would need to be copied down to the bottom of your table (200,000 rows).

In sheet3 you could then have another helper column (say in A2) with a formula like this:

=IF(ROWS($1:1)>MAX(Sheet2!X:X),"",MATCH(ROWS($1:1),Sheet2!X:X,0))

this will return the row numbers in sheet2 where the matching records occur. You could then extract data from the table in Sheet2 using this formula in B2 of Sheet3:

=IF($A2="","",INDEX(Sheet2!A:A,$A2)&"")

which can be copied across to pick up data from other columns, and then copied down as far as you need it (but only as far as there are matching records, e.g. to row 1000 instead of 200,000).

Hope this helps.

Pete

Using only 1 sheet won't work. the data that is pasted in sheet one can only be pre-sorted by beginning date. I need this spreadsheet to be as user friendly as possible because I won't be the only one using this. And also the time format that is copied in prevents me from using only 1 sheet. i have to change the time format to something usable. The pre-sorted sheet is created by a different program each time that program is ran. When this program creates the file it creates it using the same file name every time.

Pete, very well might work. I will try this and let you know how this works.

Originally Posted by Pete_UK

=IF(AND(date>=start_date,date<=end_date),MAX(X$1:X1)+1,"-")


=IF(ROWS($1:1)>MAX(Sheet2!X:X),"",MATCH(ROWS($1:1),Sheet2!X:X,0))


=IF($A2="","",INDEX(Sheet2!A:A,$A2)&"")



Pete

Pete
I changed your first formula to =IF(AND(date>=start_date,date<=end_date),Row(date))

Doing this I was able to bypass the second formula.
However, because I am working with 200,000 Excel crashes. Excel gives me the error of Unable to complete, ran out of resources, or something similar. I even tried to drop that to 150,000, but this was even more problematic. Sometimes Excel would crash, other times it would try to complete the formula, I say try because it pretty much froze the whole computer for over 30 minutes. At this point I ended up hard rebooting the pc to get control back.
My next thought is to see if I can split the 200,000 rows on one sheet to two sheets at 100,000 rows, or 4 sheets at 50,000 rows. I do need 200,000 total rows. However, will splitting those 200,000 rows into multiple sheets make it easier for excel to handle?

I am sure that I can manage to split these up pretty easily, sheet 1 raw data, sheet 2 equals row 1: row50,000, sheet to equals row 50,001:100,000. ETC
What do you all think?
.Would splitting the 1 sheet of 200,000 rows into multiple sheets make it easier for excel to handle?

It would be easier to handle a smaller number of records, but before you do that you could try copying the first formula down a smaller number of rows at a time, e.g. from row 2 to row 10,000, then to row 20,000, then to row 30,000 and so on until you get to the bottom of your data.

I'm not sure why you changed the formula to return the row number rather than MAX(...) +1. With my method, you will get sequential numbers for the matching records, i.e. 1, 2, 3, 4 ... whereas you could have 100, 359, 562, 731 ... if you return the row number, and then that is more difficult to extract to the other sheet.

An alternative to using MAX with a variable range is to use COUNTIF, and that might be quicker to execute. The formula would be:

=IF(AND(date>=start_date,date<=end_date),COUNTIF(X$1:X1,">0")+1,"-")

Hope this helps.

Pete

Pete
I changed the first formula from MAX to ROWS because I was getting odd results from MAX, The first cell would sometimes return 100 other times 79, yet other times some random number. The rest of the rows returned as expected by adding 1 to the previous if date was within the limits. However the first one was troublesome. The first never returned 1, it was always something different. I once saw this number start at 80 and then without doing anything would change to 2179. I did have iterative calculations on due to the circular references. I was able to redo the formulas to get rid of this and now I have iterative turned off.

ROW won't work because now I have to condense this and get rid all the blank cells. This formula takes too long to run even at 49,998 vs 200,000.
I will try MAX and COUNTIF when I go to work and I will post my results tomorrow. I say 49,998 because the first sheet is programmed rows 2 : 49,999. The data sheet has a header row. sheet 2, 50,000: 99,999, sheet 3, 100,000 : 149,999. Sheet 4 finishes it off with 150,000: 200,000 of the data sheet.

Your first formula didn't crash excel. It was the odd results that forced me to use ROW. The formula that crashes excel is the one i use to condense the results of changing MAX to ROW. As you stated ROW is not sequential. Since I got rid of the circular references I will try MAX and COUNTIF to see if either one works now and which one works better. Hopefully with this change I can put the 4 sheets back into 1. 1 sheet will make building the report much easier.

Thanks

Ozzie