I am using an excel spreadsheet to keep track of message traffic information.
Using an excel formula against the start time and end time, I check to determine whether the message traffic is within the designated allowance or is delayed.
When a message is delayed, I would like to collect the message identifiers for the delayed messages in a second worksheet.
Is this possible somehow using excel?
You can use a helper column to determine if a message is delayed. Then use this as a filter for a pivot table build to the data.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
I use the COUNTIFS formula against the start time and end time to determine how many messages are delayed.
But when a delayed message is found, in addition to counting it I would like to move the message identifier, start time, and end time to another worksheet.
Can this be done through the use of a pivot table?
If moving the delayed messages to another sheet is required, then the best way to do this is to make the helper column, filter the range and copy the filtered results to the destination page. Yes, this can be automated in a macro.
If you can attach a sample workbook with non-sensitive data, we can better assist you.
Would you need a sample workbook if you had the details of the spreadsheets?
The Messages worksheet has 8 fields:
Message Id (10 chars),
Relay (3 chars),
Message size (5 chars),
Recipient (8 chars),
Network (1 char),
Precedence (1 char),
Sent - date/time (23 chars),
Acknowledged - date/time (23 chars),
When a message is delayed, I need to copy the following 5 fields to another worksheet called Delayed:
Message Id (10 chars),
Recipient (8 chars),
Precedence (1 char),
Sent - date/time (23 chars),
Acknowledged - date/time (23 chars).
There are 4 different message precedences and each precedence has a different allowance for a delay.
Precedence 1 is 30 minutes.
Precedence 2 is 9 minutes.
Precedence 3 is 3 minutes.
Precedence 4 is 1 minute.
Please let me know if this helps at all.
Thank you very much.
I will admit to the frailty of human nature: I'm basically lazy and don't want to generate the sample data on my own.
If you want some sample data, I can generate a sample spreadsheet.
After I create the sample data, I was hoping that you could explain to me the process of what you would be doing since that would help me much more than having you just do it for me.
Would you be able to explain the process of whatever you would need to do with the sample data that I would create?
Hello again.
I have created a test spreadsheet.
The records in the spreadsheet are sorted by MSG ID and RECIP.
As I mentioned, a message is delayed when the delivery time exceeds the allowance for the specified precedence:
Precedence 1 has allowance of 30 minutes.
Precedence 2 has allowance of 9 minutes.
Precedence 3 has allowance of 3 minutes.
Precedence 4 has allowance of 1 minute.
Based on the specified precedence for each record in the spreadsheet,
records 6, 7, 8, and 10 are the delayed messages and are the ones that would need to be moved to the delayed spreadsheet.
Could you please show me the process of how after the determination is made as to which messages are delayed that the following fields
Msg Id
Recip
Prec
Sent
Acknowledged
are moved to another worksheet called DELAYED?
I have attached a spreadsheet but do not see it.
Please let me know if you can get to it.
Thank you very much.
Here is my take.
First of all, I converted the data into an Excel Table. One of the advantages of an Excel Table is that it “remembers” formulas and copies them down automatically.
You did not tell me how you got the data onto this sheet, but I will assume copy / paste. If you want to append data to the existing table, then you can copy and paste it to the next available row after the table and it will become part of the table.
If you want to start with a fresh (blank) table, I provided a macro to clear the table contents for you. It’s called ClearEmails. In that case, copy / paste the new data into row 2 on the Email Page.
My philosophy is to let Excel do as much of the work as possible and use VB only to tie the pieces together. So I make a lot of use of helper columns and cells.
I have a tab called Parameters. On it is a small table (Table_Precidence) that is used to look up the allowed minutes based on the precedence.
Also on this page are two named ranges:
Cell F1 is called Row_Number and it is the last row in the Delayed Table. I add one to this in the code to get the next available row onto which delayed records will be copied.
Cell F3 is called Mail_Number and it is the last row in the Email Table. In the code I loop up from the bottom to check to see if an email is delayed. The reason to go from bottom to top is that when the row gets deleted, the rows below it get pulled up. If you go top to bottom this messes up the loop. However it doesn’t matter if you do it from bottom to top, because you already processed those rows.
I added two helper columns Minutes (Column I =MINUTE([@ACKNOWLEDGED]-[@SENT])) and Status (Column J =IF([@Minutes]>VLOOKUP([@PREC],Table_Precidence,2,FALSE),"Delayed","On Time")). Status is used to determine if the record should be processed.
You can see another advantage of tables: they use column header names that make more sense than column letters.
The code looks up Column J from bottom to top. If the status is “Delayed”, it copies the appropriate cells to the Delayed Table and deletes the row.
The code also has provisions for clearing out the Delayed Table in case you do this daily. Otherwise it keep a history from the start. See the comments in the code.
I real sorry but I made one mistake when I said "Based on the specified precedence for each record in the spreadsheet,
records 6, 7, 8, and 10 are the delayed messages and are the ones that would need to be moved to the delayed spreadsheet.".
I do not want to move and delete the delayed records - but copy and retain.
I should have said "Based on the specified precedence for each record in the spreadsheet,
records 6, 7, 8, and 10 are the delayed messages and are the ones that would need to be copied to the delayed spreadsheet and left on the original spreadsheet."
How does this change things?
Simple fix; comment out the line of code that deletes the record.
Please Login or Register to view this content.
Thank you.
I have not yet had the time today to thoroughly evaluate and think about the process of what you gave me.
If at all possible, my intention was to not have to modify the original spreadsheet and then just to create a DELAYED spreadsheet and update it with the delayed message records.
Does your process do that?
Hello again.
I did have a curiosity question after reading your first sentence of "First of all, I converted the data into an Excel Table."
The spreadsheet that I start with often has roughly 160,000 entries.
Will that present any problems when converting to an Excel table?
It is possible to do this without the helper columns but it involves using more VBA code. You can always hide the helper columns and sheets.
Our posts crossed, there is absolutely no issue with applying an Excel table to a large set of data.
Here is some more information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.
I don't know how I ever got alone without them
The process that I use starts with importing a database file into the spreadsheet.
After discussing this with you and looking at the spreadsheet that I use, it appears that the spreadsheet is part of a table because there are cells at the far right that have formulas in them that derive values when once the file is imported into the spreadsheet.
So I guess I can append your suggested helper columns an create a Delayed worksheet.
But what else do I need to do to make use of the other parts of your suggested process - like the VB code and anything else that I would need to do?
I don't think any modifications will be necessary. As long as the columns to copy over remain in the same position and delayed is still in Column J, you should be good. If anything moves to another column, you can see in the code where that would have to be changed.
I am confused.
Lets' say that the Backup tab of the spreadsheet that you created is essentially an example of the spreadsheet that I am using.
1. I need to add the Minute and Status helper columns on my spreadsheet.
2. I need to create the Parameters and Delayed worksheets as part of my spreadsheet.
3. There must be some VB code that you created that performs all the moves of delayed messages that I would need to also create but I do not know where that is.
Also, one thing that I noticed in the Delayed worksheet that you created was that there should only be 4 entries since there were only 4 delayed messages but there are 8 entries and I do not understand why.
Please help.
Thank you.
I created the backup sheet because my understanding of the original requirement was that delayed rows were to be deleted and I needed a way to "reset" the data so I could run multiple tests. Yes, it looks like the original data you provided.
Let me answer your questions in order:
0. Make sure all the table names in the workbook are the same as the example. Make sure the named ranges on the parameter page for cells F1 and F3 are the same.
1. Yes, you need to add the helper columns to the table with the same formulas.
2. Yes, you need to create the two additional tabs and put the data in as shown. On the Delayed worksheet, make sure the table is created but make sure the data part is empty.
3. To add the VB Code:
Save your workbook as either *.xlsb or *.xlsm.
Close your workbook.
Open the sample workbook.
Press ALT-F11 to bring up the VB monitor on the example.
Open the module ModClearTable3 and copy and paste this into Notepad.
Then open Module1 and copy and paste it right after the code you just pasted.
Remove the second Option Explicit.
Close the example workbook.
Open your workbook and Press ALT-F11. Select Insert - > Module (The regular module, not Class Module).
Copy and paste the notepad contents in here. This should install the VB code in your workbook.
About the delayed worksheet having 8 entries. That's my fault. I was testing to make sure that new delayed messages would append properly. As I mentioned before, I don't know if you would be starting fresh each time or appending to history.
The macro that does this is called Move_Delayed. You can either look at View->Macros and launch it there, or assign it to a CTRL key - there's also a way to "draw" a button on a worksheet and assign the macro to it so when you click it the macro runs. I won't go there now.
I think I need this to be simpler, I'm sorry.
I have never worked with VB code or macros or tables before.
1. I need the delayed messages not to be deleted from the original worksheet but just written to the Delayed worksheet like you were doing.
2. So I do not need create Backup worksheet?
3. I believe the workbook that I use already has a table created, but how do I know?
4. How do you find the table name if there is already one existing?
5. How do you create a table? And make the data part empty?
6. So I will need to add the Minutes and Status helper columns with the same formulas to my existing worksheet.
7. So I will need to add the Parameter and Delayed worksheets to my workbook with the same data as you have in your example.
8. I believe my current workbook has the .xlsx extension. My current workbook does not have to be changed to .xlxb to use VB code, does it?
9. I am not sure that I understand how to add the VB code.
10. I would be starting fresh each time with the Delayed table - not saving anything.
11. I am not sure that I understand how to create macros.
12. Curiosity question - Is it possible to add to a separate Delayed worksheet like you did here by using formulas instead of using VB code as you have?
I'll answer some of the questions now and get back to you when I have more time.
1. I'll provide exactly the code you need and give you an alternative way of getting it into your workbook.
2. Yes, you do not have to create the backup worksheet.
3 - 5. See this discussion. If it doesn't answer your questions, then ask away: http://www.utteraccess.com/wiki/inde...ables_in_Excel.
6. Correct
7. Correct
8. Your workbook must be saved as a macro-enabled workbook which means it must have a .xlsb or .xlsm extension.
9. I will export the module with the code to a *.bas file. Then you can import it using the instructions in this article: http://www.utteraccess.com/wiki/inde...ction_To_Excel.
10. I will cover removing the old data with the code included in the module
11. See question 9.
12. The best you can do is mirror line-for-line those rows that have a delayed status. However, the lines that are not delayed will show up as blank lines.
Something to consider: If it is important to have a separate list, then VB code create the list is the best way to go. However, if your purpose in doing this is simply to call attention to delayed items, then this can be accomplished without VB code by using conditional formatting. The lines that are delayed will be highlighted and we can even use a different color highlight depending on the value in the prec column.
It is important that we have a separate worksheet with the delayed messages not just call attention to delayed messages.
I want to thank you very much for all your help with this.
I read through the utteraccess article that you provided about tables and I did not find anything in that article that indicated how to determine if there is already an existing table in place, so I am not sure if there is one.
Another way using formulas only.Originally Posted by markkk
This explanation is longer than the solution.
I named the source sheet MSG LOG and the other DELAYED. In MSG LOG column I this formula computes the elapsed times rounded to the nearest minute.There is a small lookup table in N1:O4 that can be moved anywhere. I left it in MSG LOG for ease of explanation and clarity. The Prec and corresponding allowances are there.Formula:
Please Login or Register to view this content.
Row\Col
In column J this formula references the column F Prec to return the allowances listed in the lookup table.In column K is the Status On time / Delayed.Formula:Please Login or Register to view this content.Then with 160,000 entries you’ll need a helper column to assign index numbers to the Delayed messages. The alternative would be an array formula in DELAYED. They are resource heavy and too many of them could slow your workbook down. That helper formula in column L isFormula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Then in DELAYED I copied and pasted the relevant headers from MSG LOG. In A2 filled down and across until you get blanks.Formula:Please Login or Register to view this content.
Dave
Hello FlameRetired.
I am not too familiar with what the INDEX and MATCH formulas are doing.
Is the process of populating the delayed messages into the Delayed worksheet a manual or automated process?
I am not comfortable with an understanding of the small lookup table, the LOOKUP formula, the formula starting with '=IF(K2=', and the INDEX and MATCH usage.
If these formulas that you mention move the delayed messages from the MSG LOG worksheet to the Delayed worksheet, could you please give me a step-by-step process that I can follow to implement that process?
Thank you very much.
Hello FlameRetired.
I played around with what you gave me and I was able to figure it out.
I just have a question.
In the INDEX/SMALL/MATCH formulas, you used the number 21 since the example MSG LOG worksheet had 21 entries.
If the number of entries in my current worksheet varies each day, how could I modify the INDEX/SMALL/MATCH formula to accommodate whatever number of entries there might be?
Thank you very much.
Mark
In the SMALL function, would something like $L:$L work to go through all of the L column?
I don't know what to suggest to use in the INDEX function where 21 is used, or would something like $I:$I work there?
Mark
Hello again.
Could you also please explain in words what is being selected in the INDEX, SMALL, and MATCH functions that you provided?
Thank you very much.
Mark
Another question.
How many rows in the Delayed worksheet should have the INDEX/SMALL/MATCH formula if the amount of delayed messages varies every day?
Thank you very much.
Mark
Hi markkk,
Sorry about the delay. Domestic duties.
For the LOOKUP formula .... if you would like to not have the lookup table then those references can be hard coded into the formula like this.
=LOOKUP(F2,{1;2;3;4},{0.0208333333333333;0.00625;0.00208333333333333;0.000694444444444444})
Those long decimals are the times. They are fractional portions of a day. Excel interprets them as 30, 9, 3 and 1 minute.
Yes you can reference the first argument of INDEX with whole columns with impunity, but you will need to change the 'Status index' formula in MSG LOG to:
=IF(K2="Delayed",ROW()-ROW($2:$2)+2,"")
This will assure the correct offset of rows.
I wouldn't reference whole columns with SMALL however. Try instead referencing 200,000 rows or something more than what you anticipate using. Perhaps something like this.
=IFERROR(INDEX('MSG LOG'!$A:$H,SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),MATCH(A$1,'MSG LOG'!$A$1:$L$1,0)),"")
Hello FlameRetired.
Thank you very much for all your help with this.
1. I am not sure that I understand why you would have to change the =IF(K2= formula to a +2 at the end just because I use whole columns in the first argument of INDEX. Could you please explain that?
2. Could you please explain in words what the =IF(K2= formula is doing in words?
3. In the Delayed worksheet, you have the INDEX/SMALL/MATCH formula in every cell down to row 19. If I do not know how many delayed messages there will be, how many rows in the Delayed worksheet should have the INDEX/SMALL/MATCH formula?
Mark
Hello again.
I forgot a question.
4. Could you please explain what the INDEX/SMALL/MATCH formula is doing in words?
Thank you very much.
Mark
Hello FlameRetired.
I made the change in the =IF(K2= formula from a +1 at the end to a +2 and
made the change in the INDEX formula from $A$2:$I$21 to $A:$H and
made the change in the SMALL formula from $L$21 to $L$200000 and the delayed messages do not show in the Delayed worksheet.
I have not yet had the chance to play with it further.
Mark
Did it work before that?
Did it work before that?
Yes, it did work before my changes.
I was then trying to make the suggested adjustments to accomodate more than just the 21 rows.
Mark
Hello FlameRetired.
If I make all your suggested changes except for the change to the SMALL formula, it all works.
When I change $L$21 to $L$200000 in the SMALL formula, it does not work.
Please help.
Mark
1. Previously we were referencing a fixed range A2:H21. You have a relative row referencing .... row 2 is understood to be row 1 of that range. When referencing whole columns that changes. Row 2 is now where the data starts. If you continued with the +1 the formula in column L would reference row 1 of column K or the header row. From there the results in DELAYED would be off by 1 row. Your first "Delayed" would be 033kg5966h not the correct 034g68gj68. By adding +2 we are adjusting for this offset.
2. Before executing the ROW()-ROW($2:$2)+2 part of that formula IF checks to see if the cell next to it is "Delayed". If it is then and only then does it return the index number. If it isn't "Delayed" it returns a "" .... i.e. a blank cell. We only want "Delayed" indexed and nothing else.
3. All you can do with formula solutions is to fill down until you get blanks. You could fill down beyond what you would expect the maximum number of rows to be, but that might be excessive and unnecessary use of resources. In F1 of the last upload I put a helper cell that counts the number of index numbers in MSG LOG column L. That tells you to fill down that many rows. As an afterthought you might want to add 1 to that to give you the actual row number to copy down to.
4. You'll need to read up on INDEX. The SMALL references non-contiguous index numbers in column L MSG LOG. By incrementing the second argument of SMALL with the ROW()-ROW($2:$2)+1 part it returns the 1st smallest, 2nd smallest, 3rd smallest .... n smallest of the index numbers coercing a contiguous reference to the index numbers. It passes those numbers to INDEX telling INDEX to return those row numbers in MSG LOG ... remember those are now row numbers relative to whole columns. The MATCH part returns a column number by matching the column headers in row 1 of DELAYED to the column headers in row 1 of MSG LOG. It passes that column number to INDEX telling INDEX which of the columns A:H to return. This enables you to skip the non-relevant column headers in MSG LOG and return only the relevant columns.
Does this help?
Last edited by FlameRetired; 06-22-2016 at 10:58 AM.
Hello FlameRetired.
I will read your new entry after I give you this information.
I just wanted to let you know that I found that the SMALL formula does not seem to like it when you specify $L$2:$L$200000 when there are not 200000 rows in the worksheet.
When the worksheet has 21 records, $L$2:$L$21 works in the SMALL formula but $L$2:$L$22 will not work.
I put 150000 rows in the MSG LOG and it worked with $L$2:$L$150000 in the SMALL formula but nothing larger.
So it seems that we need to find out how to specify a range in the SMALL formula that would be larger than the number of rows that would exist.
Mark
Hello FlameRetired.
1. I believe I understand your explanation.
2. In the =IF)K2= formula, I thought the +1 was incrementing. I guess I don't understand the what the 'ROW()-ROW($2:$2)+2' part of that formula does and how it increments form line to line.
3a. When you say 'In F1 of the last upload', I believe you mean in the first cell of the first row in the F column.
I don't know what upload you are referring to and I don't know where that is because I don't recall seeing that helper cell anywhere.
3b. I understand using a helper cell so you would know how many indexed (delayed) rows that you will have to ad to the Delayed worksheet. I thought you added the INDEX/SMALL/MATCH formula to the Delayed worksheet rows and columns.
Did you place the INDEX/SMALL/MATCH formula into each of the 5 columns of the first 19 rows?
4. I believe I understand your explanation here.
Please help.
Thank you very much.
Mark
2. It was incrementing by 1. It started by returning 1 in the first row. But with the whole column references we needed to start by returning row 2. Find an empty area or open a temporary workbook and paste just this part in row 2 of that temporary sheet .... =ROW()-ROW($2:$2) ... and fill down. The initial return will be 0 and progresses 0,1,2,3 .... n. We add whatever we need to that in order to initiate the starting row. In the case of ROW()-ROW($2:$2)+1 it progresses 1,2,3,4,5 .... n. In the case of ROW()-ROW($2:$2)+2 it progresses 2,3,4,5,6 .... n. Those are the relevant row indexing numbers you'll need. Remember we are referencing the whole columns A:H which start in row 1, but we need to access data starting in row 2. ROW()-ROW($2:$2)+2 initiates at 2.
By the way I usually do this row incrementing with ROWS($2:2) and then add what I need to. But you are anticipating as much as 160,000 rows. I have found ROWS($2:2) to be very slow over large ranges. The ROW()-ROW($2:$2) construction is much faster. Try one in the first row, double click the fill handle and it will automatically fill down as far as there is data. Then try the same thing with the other. The difference doesn't start to be noticeable until about 5000 rows. So you won't notice it with the upload.
3a. Apparently I failed to upload. The forum is misbehaving this morning. I don't know if the forum lost the file or I failed in one of the upload steps. Should be attached this time.
3b. "Did you place the INDEX/SMALL/MATCH formula into each of the 5 columns of the first 19 rows?"
No. I entered the formula into A2 of DELAYED, filled down until I got blanks and across to column E.
Last edited by FlameRetired; 06-22-2016 at 01:26 PM. Reason: afterthoughts
Hello FlameRetired.
Thank you very much.
1. I see your uploaded workbook.
I see your helper cell F1 in the Delayed worksheet where you count column L of the MSG LOG worksheet for indexes to determine the delayed messages,
and it has a count of 5 because I recall you said that it might be a good idea to do +1 from how many delayed messages there are.
I'm not sure that I understand what you do with this 5 once you have it.
I know the helper cell of F1 is intended to help you know how many rows you will have in the Delayed worksheet but I don't understand what needs to be done with that information.
2. So when you created the Delayed worksheet, all you did for setup was to create the column headers, physically place the COUNT formula into cell F1,
and physically place the INDEX/SMALL/.MATCH formula into cell A2?
I don't know what you mean by 'filled down until I got blanks and across to column E' because the formula is in all columns A thru E and rows 2 thru 19.
I think you are saying that you physically entered the INDEX/SMALL/MATCH formula into cell A2 , but I don't understand how it got to columns B thru E and rows 2 thru 19.
I think I don't understand what would have moved the formula to all of the other cells for you.
3. And then there is the issue of what number do we put into the SMALL argument since L200000 does not work. I guess maybe the helper cell content can help somehow.
Please help.
Mark
1. The 5 is a visual cue. It tells you how many rows you must fill down / copy the formula put in A2 to exhaust the 'Delayed' data. It has no formula function.
2. Once the formula is in A2 you will see a small green square in the lower RH corner of the cell. Click on that with the mouse and while holding the mouse button down 'drag' the pointer down and to the right. The formula will automatically "fill" or copy itself in its wake.
3. The whole formula segment is
SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1)
I picked a sufficiently large range ('MSG LOG'!$L$2:$L$200000) in the first argument to make room for at least 160,000 rows of data. If your data exceeds 200,000 rows you will need to edit that. I resisted the temptation to use the whole column L. I don't know how well SMALL / LARGE handle that. You also need that second argument. If it still doesn't work I'll need more information describing what about it isn't working. What you posted was vague.
Last edited by FlameRetired; 06-22-2016 at 08:54 PM.
1. Oh, so you manually dragged down into row 19 and over into columns A thru E?
So if I have 150000 delayed messages, I will have to go into the Delayed worksheet and drag the formula down into row 150000?
2. Also I noticed you have L200000 in the SMALL formula and when I had anything less than 200000 rows of delayed messages, it did not work for me.
Did you test it with using L200000 in the SMALL formula and it worked for you?
If so, how did you get it to work?
Mark
I dragged the formulae in "Delayed" (last FlameRetired file posted )down 200000 rows: no problem.
I copied/pasted an existing "Delayed" and count was 6 and 6 rows were displayed,
If you only dragged the formula down 10 rows but you had more than 10 "delayed" then those over 10 would not show.
Hello FlameRetired.
When I work with your MSG LOG and Delayed worksheets that you uploaded, the SMALL formula works with the $L$200000.
But in my current worksheet with same sample 21 rows of data, when I change the $L$21 to $L$200000 or any number larger than 21 or any number smaller than 21 it does not work.
What is different from your uploaded example?
What can I check that would cause mine not to work?
Mark
Is the file format identical?
Did you copy/paste the formulas given?
Post your file (or sufficient to show problem) as it is not possible to determine the problem without seeing the data.
Hello John.
What do you mean - file format?
Where do you find the file format?
I copied the formulas but I was very thorough to make sure I have it correct.
Hello FlameRetired.
Where did you go?
I have imported a few different files of different lengths into my MSG LOG worksheet - 1 of 21, 1 of 150000, 1 of 122000.
I did the one of 21 records and 4 delayed messages using the $L$2:$L$21 in the SMALL formula and it worked fine.
I then did the one with 150000 and 15 delayed messages using the $L$2:$L$150000 in the SMALL formula and it worked.
I then did the one with 122000 and 22 delayed messages using the $L$2:$L$122000 in the SMALL formula and it worked.
But when I did any of the above, if second parameter of $L$2:$L$xxxxx was any number either less than or greater than the number of rows that were in the MSG LOG worksheet, it would not show the delayed messages in the Delayed worksheet. The second parameter had to be the exact number of rows in the MSG LOG worksheet.
Is this normal?
And if so, do you know why this happens?
Mark
Hello FlameRetired.
Where did you go?
I have imported a few different files of different lengths into my MSG LOG worksheet - 1 of 21, 1 of 150000, 1 of 122000.
I did the one of 21 records and 4 delayed messages using the $L$2:$L$21 in the SMALL formula and it worked fine.
I then did the one with 150000 and 15 delayed messages using the $L$2:$L$150000 in the SMALL formula and it worked.
I then did the one with 122000 and 22 delayed messages using the $L$2:$L$122000 in the SMALL formula and it worked.
But when I did any of the above, if second parameter of $L$2:$L$xxxxx was any number either less than or greater than the number of rows that were in the MSG LOG worksheet, it would not show the delayed messages in the Delayed worksheet. The second parameter had to be the exact number of rows in the MSG LOG worksheet.
Is this normal?
And if so, do you know why this happens?
Mark
One point I didn't realise was that the formula in Column L of "MSG LOG" has to be dragged down as more data is entered: it can be dragged down as far as required and beyond.
Did you do this?.
If done formula works perfectly OK.
=IFERROR(INDEX('MSG LOG'!$A:$H,SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),MATCH(E$1,'MSG LOG'!$A$1:$L$1,0)),"")
@ John Topley,
Thanks for the backup.
On a secondary point:
I've been reluctant to suggest a whole column reference L:L.
Now I am wondering if my concerns are warranted.
Any thoughts?
Try it and see!!!
Thank you.
Actually I had.
It didn't seem to make a difference, but I wondered if I failed to factor something unrecognized. Whole columns are usually advised against, and I do not recall seeing it done as a practice, but I learn something new every day.
I have the formula in column L of the MSG LOG worksheet dragged down to 200000.
The delayed messages do not show in the Delayed worksheet unless the xxxxx of $L$2:$L$xxxxx in the SMALL formula is exactly how many rows exist in the MSG LOG worksheet - no more and no less.
If you all do not have any ideas as to why this works differently in the uploaded example that you gave me than it does in my current workbook that I have, at least it works.
I have added an additional helper cell called 'MGSLOG rows' in the Delayed worksheet that provides the number of rows in the MSG LOG worksheet that I can use to fill in the SMALL formula so that it works for my workbook.
Mark
Hello FlameRetired.
Thank you very much for all of your help with this.
1. When using my Messages workbook today, I found that I had 6035 delayed messages.
When I updated the SMALL formula in the Delayed worksheet, it took a very long time to populate all of the delayed messages down into the Delayed worksheet.
Is that normal?
2. If you happen to figure out why your uploaded MSG LOG and Delayed worksheets work differently than mine with regards to the setup of the SMALL formula, I would really like to know since it would be much more convenient if I could setup the SMALL formula with a $L$ range that can be set for the 200000 and not have to be changed every time I use it.
Mark
Try setting that to $L:$L and see what happens. It didn't seem to affect negatively at my end, but I don't have that much data in my copy.
I am surprised that it should take so long. I need to see the actual formulas you are using and not a verbal report that they are the same as I posted. The only thing I can suggest is this:
- Go to cell L2 of MSG LOG.
- Select and copy the whole formula from the formula bar. Make no changes to it.
- Paste that formula in your next post.
- Do the same with the formula in A2 of DELAYED. Make no changes to it.
- I need to see exactly what is in those formulas.
Perhaps they need some 'tweaks'.
Edit Something else escaped my attention. Only for clarity I included 3 conditional formats in MSG LOG.
They are likely extending down with new data and definitely are with the formulas in MSG LOG.
They are resource hungry and super volatile. That slows things down.
Click Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet. Save the workbook, close the workbook then reopen it. See if that makes any difference.
My apologies for that oversight.
Last edited by FlameRetired; 06-24-2016 at 11:54 AM.
Hello FlameRetired.
I will try the $L:$L at some point later when I get the chance.
I can assure you that I am using everything that you recommend exactly as you specified it.
I can never do a cut and paste to give you what I have because I work with an isolated system that is not connected to the computer
where I am doing this forum.
To implement things that you recommend, I have to print out what you recommend and go to my system and re-type everything.
I check it all 2 and 3 times to ensure that I have exactly as you say it.
It is a bit of a pain but it is what it is.
Mark
Have you tried removing the conditional formats?
Try this:
in "DELAYED"
in F2
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
copy down as far as required: lists all rows of "Delayed" TXs
in A2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,1),""),"")
in B2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,4),""),"")
in C2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,6),""),"")
in D2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,7),""),"")
in E2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,8),""),"")
Copy all formulas down as far as required (max of "Delayed" count)
This removes all the array processing.
I like it.
One calculation of the rows and then a local reference. That should speed things up a ton.
Good one. Thanks for the backup.
@ John Topley
An afterthought.
Try changing =IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
to
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),"")
I keep a workbook for figuring out helper column formulas (unique items) .... on about 50,000 rows. There is a noticeable difference in speed when I do this. It saves having to recount all the ROWS.
Hello FlameRetired.
Sorry for the delay as I have been off on something else that had priority and I did not have the time to work with this.
I hope to get back this tomorrow.
I did have a question about removing the conditional formatting that you mentioned.
Is the conditional formatting something that comes standard with Excel and needs to be removed when you don't need it?
I ask because if I have to enter everything myself because of my isolated system situation and did copy anything from you, would there still be conditional formatting that has to be removed?
Mark
No. It must be purposely applied. The default is No Conditional Formatting.
If you ask because performance is still lagging there might be other residual effects from the CF. They've been know to happen. Let us know.
Hello FlameRetired.
I ask because you had asked me to remove the 3 conditional formatting that you said you had included in the MSG LOG worksheet.
But since I cannot copy what you gave me and apply it to my system because my system is an isolated system where I must re-type everything over there, I was wondering why you were telling me to remove the conditional formatting.
Therefore, the removing of conditional formatting does not apply to me, right?
Mark
I don't know enough about closed systems to say. If you have access to the ribbon menu:
- With MSG LOG the active sheet.
- Click Home.
- Click Conditional Formatting
- Click Clear Rules
- Click Clear Rules from Entire Sheet
- All the text and background color should disappear.
- Save the file.
If nothing happens perhaps the close system filtered out the CFs to start with. In either event doing the above won't hurt.
Is the file still slow to calculate?
Hello FlameRetired.
1. So if I did not copy anything from you into my system, or create any conditional formatting myself, and the default is no conditional formatting, there should be no conditional formatting to remove, right?
It is still slow.
2. Do I need to change my SMALL formula somewhere?
Thank you very much.
Mark
Since I do not have a current copy of what you are looking at I cannot say.It is still slow.
2. Do I need to change my SMALL formula somewhere?
And yes Conditional Formatting should not be an issue in your case.
Did you see this (post #62) from FlameRetired ?
If not, try it in "Delayed" F2.
An afterthought.
Try changing =IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
to
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),"")
Hello FlameRetired.
I saw some exchanges between you and John T. where John T.
suggested:
Try changing =IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
to
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),"")
:
[start text]
Try this:
in "DELAYED"
in F2
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
copy down as far as required: lists all rows of "Delayed" TXs
in A2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,1),""),"")
in B2
..................
Copy all formulas down as far as required (max of "Delayed" count)
This removes all the array processing.
[end text]
and you commented that you liked it and you said "One calculation of the rows and then a local reference. That should speed things up a ton."
I was wondering if I needed to do that.
Mark
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
to
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),"")
The first formula was mine and FlameRetired suggested changing it to the second as it is much faster. So do it!!!!!
OK. I trust you have read what John Topley is telling you? Have you tried just that? If you have and there are still issues please consider:
- This solution has been through several versions.
- I can't tell in what context you are using specific formulas.
- They can mutually impact one another's performance. They are context sensitive.
- A new upload of all you are currently using will tell me more and sooner than multiple posts will at this point.
- Without a new upload of what you are currently using I can't be assured that I am answering the right questions.
Hello FlameRetired.
My solution is essentially exactly what you gave as follows:
In MSG LOG worksheet:
Allowance Time column has formula - =LOOKUP(F2,$N$1:$O$4)
Status column has formula - =IF(I2>J2,"Delayed","On Time")
Status Index column ha formula - =IF(K2="Delayed",ROW()-ROW($2:$2)+2,"")
In Delayed worksheet:
MSG ID column has formula -
=IFERROR(INDEX(MSG LOG!$A$:$I,SMALL(MSG LOG!$L$2:$L$136030,ROW()-ROW($2:$2)+1),MATCH(A$1,MSG LOG!$A$1:$L$1,0)),"")
RECIP column has formula -
=IFERROR(INDEX(MSG LOG!$A$:$I,SMALL(MSG LOG!$L$2:$L$136030,ROW()-ROW($2:$2)+1),MATCH(B$1,MSG LOG!$A$1:$L$1,0)),"")
PREC column has formula -
=IFERROR(INDEX(MSG LOG!$A$:$I,SMALL(MSG LOG!$L$2:$L$136030,ROW()-ROW($2:$2)+1),MATCH(C$1,MSG LOG!$A$1:$L$1,0)),"")
SENT column has formula -
=IFERROR(INDEX(MSG LOG!$A$:$I,SMALL(MSG LOG!$L$2:$L$136030,ROW()-ROW($2:$2)+1),MATCH(D$1,MSG LOG!$A$1:$L$1,0)),"")
ACKNOWLEDGED column has formula -
=IFERROR(INDEX(MSG LOG!$A$:$I,SMALL(MSG LOG!$L$2:$L$136030,ROW()-ROW($2:$2)+1),MATCH(E$1,MSG LOG!$A$1:$L$1,0)),"")
I have a helper column in the Delayed worksheet used to give me the number of records in MSG LOG and I change the 136030
in the SMALL formula to the number of records that the helper column gives me.
I also have a helper column in the Delayed worksheet used to give me the number of delayed records in MSG LOG
so I know how many rows down in the Delayed worksheet to copy the formulas in columns A thru E.
I have not yet tried the suggested change of
SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
to
SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),"")
Please let me know what I should try next.
Thank you very much.
Mark
You have TOTALLY ignored the following (from a much earlier post #60) which was designed to remove the need for the (slow) array formulas
in "DELAYED"
in F2
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
with the above being replaced by:
=IfERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),""),"")
copy down as far as required: lists all rows of "Delayed" TXs
in A2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,1),""),"")
in B2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,4),""),"")
in C2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,6),""),"")
in D2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,7),""),"")
in E2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,8),""),"")
As suggested by FlameRetired: post a NEW file with all your current formulae.
Last edited by JohnTopley; 07-06-2016 at 08:53 AM.
Upload your most recent copy of the workbook.
John T, I am still deciding whether I like your choice of words and the tone that goes with it.
I did not totally ignore anything.
Mark
Mark,
.....then why, 15 posts on, have you still not implemented the changes, particularly as they are designed to try and overcome the performance problems you are experiencing?
We are trying to help you but it is frustrating when solutions are given, assumed to be implemented, then discover they have not: we are chasing our tails.
Look at your response to a previous posting: why are you wondering???
Hello FlameRetired.
I saw some exchanges between you and John T. where John T.
suggested:
Try changing =IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
to
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROW()-ROW($2:$2)+1),"")
:
[start text]
Try this:
in "DELAYED"
in F2
=IFERROR(SMALL('MSG LOG'!$L$2:$L$200000,ROWS($1:1)),"")
copy down as far as required: lists all rows of "Delayed" TXs
in A2
=IFERROR(IF(F2>0,INDEX('MSG LOG'!$A:$H,F2,1),""),"")
in B2
..................
Copy all formulas down as far as required (max of "Delayed" count)
This removes all the array processing.
[end text]
and you commented that you liked it and you said "One calculation of the rows and then a local reference. That should speed things up a ton."
I was wondering if I needed to do that.
Mark
Last edited by JohnTopley; 07-07-2016 at 03:46 AM.
Hello FlameRetired.
In a recent post, I have given you all that I am currently using.
All the crap and lecture that I am getting from this other person is not helpful at all.
Please help me with what I still should apply.
Mark
OK markkk. I've uploaded a new copy of the workbook.
I know that it contains all of the formulas with the changes John Topley and I propose exactly.
I know that they work.
Test this and tell us how it works at your end.
If you are hand typing these formulas and they are still not working I have no way of knowing what the difficulties might be. May I suggest an alternative? Are you able to email this file to yourself at the system you are working on?
Last edited by FlameRetired; 07-08-2016 at 02:20 AM.
Hello FlameRetired.
Thank you very much for your help here and all of your help to this point.
I cannot email the file to me on my isolated system.
The only possibility that does exist is to load it to a thumb drive and move it over that way.
But there is so much red tape involved in doing that it is not worth it which is why I just re-type everything.
It is a quite pain in the butt, but that is the world in which I work.
I will check out what you gave me, re-type it over there, and let you know how it goes.
Again, I want to thank you so very much for all the help that you give me.
It is a real pleasure working with you.
Mark
Hope all goes well.
Hello FlameRetired.
It all did work with one unexpected result.
Thank you very much.
On the Delayed worksheet, I use the helper column with the number of delayed messages to determine
how far down to drag the formulas so that all the delayed messages are listed.
The formula that you have in the MSG ID column is [=IF($F2<>"",INDEX(MSG LOG!$A:$H,$F2,1),"")]
The formula that you have in the RECIP column is [=IF($F2<>"",INDEX(MSG LOG!$A:$H,$F2,4),"")]
The formula that you have in the PREC column is [=IF($F2<>"",INDEX(MSG LOG!$A:$H,$F2,6),"")]
The formula that you have in the SENT column is [=IF($F2<>"",INDEX(MSG LOG!$A:$H,$F2,7),"")]
The formula that you have in the ACK column is [=IF($F2<>"",INDEX(MSG LOG!$A:$H,$F2,8),"")]
Could you please explain in words what that formula is doing?
I also need to better understand the use of the ROW statement.
Mark
My mistake - there was no unexpected result.
Mark
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks