Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
Thankyou again, Mr Paul, for the detailed explanation. Regards Eugene On Tue, Apr 15, 2014 at 5:10 PM, Paul Schreiner schreiner_p...@att.netwrote: I use it for a wide variety of purposes. Sometimes I use it like a pseudo-index. Say I have a large number of records (I routinely have lists of 90,000 rows) and I'm attempting to report on a series of entries. looping through these rows multiple times is very time consuming. I used to load the records into an array and search the array (which is much faster, but still time consuming). If I load the dictionary with a keyword, the value can be an array of row numbers. Let's say the records are transaction records in which the invoice number appears several times. (when it is created, each update and when closed) I loop through the records, and the first time an invoice is found, add it (and the row) to the dictionary: if it already has been loaded, append the Row Number: If (not dict_inv.exists(invNo)) then dict_inv.add invNo, RowNo else dict_inv.item(invNo) = dict_inv.item(InvNo) | RowNo end if once I've loaded the data into my dictionary, then I need to find the rows for the specific invoice, they are found quickly. -- Another application: I have machine data (names, location, etc) (16,000 records) and manufacturing part numbers (22,000) in an Oracle database. during some reporting functions, the database is queried multiple times for information from these tables. Some reports may include loops that may result in 10-20,000 queries to these tables. This doesn't necessarily pose a problem with hard network connections, but wireless connections sometimes are very slow. If I load these tables into a Dictionary once, then the repeated queries do not depend on the network connection to the database. -- For the most part, just about any case in which I may have used an array, I prefer to use a Dictionary Object. unless, duplicates are allowed. hope this helps. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* Eugene Bernard eugene.bern...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Monday, April 14, 2014 12:39 PM *Subject:* Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date Thank you paul, for exposing us to the scripting.dictionary object. Can you please share, some more details and uses of the dictionary Object. Regards Eugene On Tue, Apr 8, 2014 at 7:33 PM, Paul Schreiner schreiner_p...@att.netwrote: I did it two different ways. You IMPLIED that you wanted a macro (hence, the button) So, I used a technique in which I loaded a Dictionary object with all empid's for the date specified, then looked through the list of all empid's and checked for those that did not exist in the Dictionary object. Next, I simply used a Countifs() function and had it check for the empid and date and place a X in the column adjacent to the missing id's. Using a Filter, you can display the missing id's. If you wanted, you could create a change event to monitor the date cell. When the date is changed, the countifs update, and the filter is re-applied. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley * - *From:* Ganesh N ganeshg...@gmail.com *To:* excel-macros@googlegroups.com excel-macros@googlegroups.com *Sent:* Tuesday, April 8, 2014 9:14 AM *Subject:* Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive,
Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
I use it for a wide variety of purposes. Sometimes I use it like a pseudo-index. Say I have a large number of records (I routinely have lists of 90,000 rows) and I'm attempting to report on a series of entries. looping through these rows multiple times is very time consuming. I used to load the records into an array and search the array (which is much faster, but still time consuming). If I load the dictionary with a keyword, the value can be an array of row numbers. Let's say the records are transaction records in which the invoice number appears several times. (when it is created, each update and when closed) I loop through the records, and the first time an invoice is found, add it (and the row) to the dictionary: if it already has been loaded, append the Row Number: If (not dict_inv.exists(invNo)) then dict_inv.add invNo, RowNo else dict_inv.item(invNo) = dict_inv.item(InvNo) | RowNo end if once I've loaded the data into my dictionary, then I need to find the rows for the specific invoice, they are found quickly. -- Another application: I have machine data (names, location, etc) (16,000 records) and manufacturing part numbers (22,000) in an Oracle database. during some reporting functions, the database is queried multiple times for information from these tables. Some reports may include loops that may result in 10-20,000 queries to these tables. This doesn't necessarily pose a problem with hard network connections, but wireless connections sometimes are very slow. If I load these tables into a Dictionary once, then the repeated queries do not depend on the network connection to the database. -- For the most part, just about any case in which I may have used an array, I prefer to use a Dictionary Object. unless, duplicates are allowed. hope this helps. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Eugene Bernard eugene.bern...@gmail.com To: excel-macros@googlegroups.com Sent: Monday, April 14, 2014 12:39 PM Subject: Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date Thank you paul, for exposing us to the scripting.dictionary object. Can you please share, some more details and uses of the dictionary Object. Regards Eugene On Tue, Apr 8, 2014 at 7:33 PM, Paul Schreiner schreiner_p...@att.net wrote: I did it two different ways. You IMPLIED that you wanted a macro (hence, the button) So, I used a technique in which I loaded a Dictionary object with all empid's for the date specified, then looked through the list of all empid's and checked for those that did not exist in the Dictionary object. Next, I simply used a Countifs() function and had it check for the empid and date and place a X in the column adjacent to the missing id's. Using a Filter, you can display the missing id's. If you wanted, you could create a change event to monitor the date cell. When the date is changed, the countifs update, and the filter is re-applied. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Ganesh N ganeshg...@gmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Tuesday, April 8, 2014 9:14 AM Subject: Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and
Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
Thank you paul, for exposing us to the scripting.dictionary object. Can you please share, some more details and uses of the dictionary Object. Regards Eugene On Tue, Apr 8, 2014 at 7:33 PM, Paul Schreiner schreiner_p...@att.netwrote: I did it two different ways. You IMPLIED that you wanted a macro (hence, the button) So, I used a technique in which I loaded a Dictionary object with all empid's for the date specified, then looked through the list of all empid's and checked for those that did not exist in the Dictionary object. Next, I simply used a Countifs() function and had it check for the empid and date and place a X in the column adjacent to the missing id's. Using a Filter, you can display the missing id's. If you wanted, you could create a change event to monitor the date cell. When the date is changed, the countifs update, and the filter is re-applied. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* Ganesh N ganeshg...@gmail.com *To:* excel-macros@googlegroups.com excel-macros@googlegroups.com *Sent:* Tuesday, April 8, 2014 9:14 AM *Subject:* Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing
$$Excel-Macros$$ Need help to find the missing emp id on particular date
Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Missing emp id.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
I did it two different ways. You IMPLIED that you wanted a macro (hence, the button) So, I used a technique in which I loaded a Dictionary object with all empid's for the date specified, then looked through the list of all empid's and checked for those that did not exist in the Dictionary object. Next, I simply used a Countifs() function and had it check for the empid and date and place a X in the column adjacent to the missing id's. Using a Filter, you can display the missing id's. If you wanted, you could create a change event to monitor the date cell. When the date is changed, the countifs update, and the filter is re-applied. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Ganesh N ganeshg...@gmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Tuesday, April 8, 2014 9:14 AM Subject: Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND
Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
Sub sample() Dim i As Long Dim rng As Range Dim strng As Long Dim endrng As Long Sheets(Sheet1).Range(a1:b Sheets(Sheet1).Range(a1).End(xlDown).Row).Sort key1:=Sheets(Sheet1).Range(b:b), order1:=xlDescending, Header:=xlYes On Error Resume Next i = Application.WorksheetFunction.Match(Sheets(Sheet2).Range(i3), Sheets(Sheet1).Range(b:b), 0) If i = 0 Then Exit Sub endrng = i + Application.WorksheetFunction.CountIf(Sheets(Sheet1).Range(b:b), Sheets(Sheet2).Range(i3)) - 1 Set rng = Sheets(Sheet1).Range(a i :a endrng) Sheets(Sheet2).Range(i6:i5000).Clear j = 6 For i = 2 To Sheets(Sheet2).Range(a1).End(xlDown).Row If Application.WorksheetFunction.CountIf(rng, Sheets(Sheet2).Range(a i)) = 0 Then Sheets(Sheet2).Range(i j) = Sheets(Sheet2).Range(a i) j = j + 1 End If Next End Sub On Tue, Apr 8, 2014 at 6:44 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- *Regards* *Ashish Koul* *Visit* http://www.excelvbamacros.in Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To
Re: $$Excel-Macros$$ Need help to find the missing emp id on particular date
Thanks Paul Ashish Regards, Ganesh N On Wed, Apr 9, 2014 at 12:14 AM, ashish koul koul.ash...@gmail.com wrote: Sub sample() Dim i As Long Dim rng As Range Dim strng As Long Dim endrng As Long Sheets(Sheet1).Range(a1:b Sheets(Sheet1).Range(a1).End(xlDown).Row).Sort key1:=Sheets(Sheet1).Range(b:b), order1:=xlDescending, Header:=xlYes On Error Resume Next i = Application.WorksheetFunction.Match(Sheets(Sheet2).Range(i3), Sheets(Sheet1).Range(b:b), 0) If i = 0 Then Exit Sub endrng = i + Application.WorksheetFunction.CountIf(Sheets(Sheet1).Range(b:b), Sheets(Sheet2).Range(i3)) - 1 Set rng = Sheets(Sheet1).Range(a i :a endrng) Sheets(Sheet2).Range(i6:i5000).Clear j = 6 For i = 2 To Sheets(Sheet2).Range(a1).End(xlDown).Row If Application.WorksheetFunction.CountIf(rng, Sheets(Sheet2).Range(a i)) = 0 Then Sheets(Sheet2).Range(i j) = Sheets(Sheet2).Range(a i) j = j + 1 End If Next End Sub On Tue, Apr 8, 2014 at 6:44 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Any updates on the request ?? Thanks Regards, Ganesh On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N ganeshg...@gmail.com wrote: Hi Team, Need a help in find the missed emp id list. Sheet1 have the actual employee ids and date. In sheet2 have overall employee ids in that i need to find the employee missed on particular date. Please find the attachment for your reference. Thanks Regards, Ganesh N -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- *Regards* *Ashish Koul* *Visit* http://www.excelvbamacros.in Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum
