RE: $$Excel-Macros$$ Excel - Array Help
Thanks Noorain, I have just seen your email... I understand the formula...only part i am not getting is... this pat ($A$2:$A2=A2)*($B$2:$B2=B2))1...how the result of 2 comparison can be greater than 1? From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 21 January 2012 21:51 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Amit, In this formula we are searching unique value of two columns Red Color part is 1st criteria Green Color part is 2nd Criteria =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) we can also use.. =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1,Duplicate,Unique) -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) amit.de...@merucabs.commailto:amit.de...@merucabs.com wrote: Dear All, Can someone please explain how the below formula will work I mean what logic it will follow. Regards, Amit Desai From: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.commailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 20 January 2012 16:22 To: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Sourabh, Please use this one to find unique value of more than one column without using helping column =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.commailto:rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.commailto:noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.commailto:rhtdmja...@gmail.com wrote: dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.commailto:noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name =SUM(1/COUNTIF(B2:B6,B2:B6)) press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. =IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) see attached sheet.. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.commailto:kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros
Re: $$Excel-Macros$$ Excel - Array Help
Did you copy down the formula? Don Guillett SalesAid Software dguille...@gmail.com From: Amit Desai (MERU) Sent: Saturday, January 28, 2012 2:43 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Excel - Array Help Thanks Noorain, I have just seen your email... I understand the formula...only part i am not getting is... this pat ($A$2:$A2=A2)*($B$2:$B2=B2))1...how the result of 2 comparison can be greater than 1? From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 21 January 2012 21:51 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Amit, In this formula we are searching unique value of two columns Red Color part is 1st criteria Green Color part is 2nd Criteria =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) we can also use.. =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1,Duplicate,Unique) -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) amit.de...@merucabs.com wrote: Dear All, Can someone please explain how the below formula will work I mean what logic it will follow. Regards, Amit Desai From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 20 January 2012 16:22 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Sourabh, Please use this one to find unique value of more than one column without using helping column =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.com wrote: dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name =SUM(1/COUNTIF(B2:B6,B2:B6)) press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. =IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) see attached sheet.. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Excel - Array Help
You don't compare RESULT of two, you check if the count of the number of times your equation is satisfying the criteria is greater than 1 or not. So if you equate $A$2:$A6=A6 you will get some TRUE and some FALSE. Similary for column B also you'll get some TRUE and FALSE. The multiplication of boolean values will either give 0 or 1. 1 only when both the equations give a TRUE. And you can only get 2 or more if there are duplicates. Otherwise it will always be 1. Hope that explains ($A$2:$A2=A2)*($B$2:$B2=B2) Regards, Sam Mathai Chacko On Sat, Jan 28, 2012 at 7:21 PM, dguillett1 dguille...@gmail.com wrote: Did you copy down the formula? Don Guillett SalesAid Software dguille...@gmail.com *From:* Amit Desai (MERU) amit.de...@merucabs.com *Sent:* Saturday, January 28, 2012 2:43 AM *To:* excel-macros@googlegroups.com *Subject:* RE: $$Excel-Macros$$ Excel - Array Help Thanks Noorain, I have just seen your email... I understand the formula...only part i am not getting is... this pat ($A$2:$A2=A2)*($B$2:$B2=B2))1...how the result of 2 comparison can be greater than 1? *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI *Sent:* 21 January 2012 21:51 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Excel - Array Help Dear Amit, In this formula we are searching unique value of two columns Red Color part is 1st criteria Green Color part is 2nd Criteria =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) we can also use.. =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1,Duplicate,Unique) -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) amit.de...@merucabs.com wrote: Dear All, Can someone please explain how the below formula will work I mean what logic it will follow. Regards, *Amit Desai* *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI *Sent:* 20 January 2012 16:22 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Excel - Array Help Dear Sourabh, Please use this one to find unique value of more than one column without using helping column *=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique)* See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.com wrote: * * dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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
Re: $$Excel-Macros$$ Excel - Array Help
Hey Amit, look for SUMPRODUCT formula information and you will get your answer. From: Amit Desai (MERU) Sent: Saturday, January 28, 2012 2:43 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Excel - Array Help Thanks Noorain, I have just seen your email... I understand the formula...only part i am not getting is...this pat ($A$2:$A2=A2)*($B$2:$B2=B2))1...how the result of 2 comparison can be greater than 1? From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 21 January 2012 21:51 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Amit, In this formula we are searching unique value of two columns Red Color part is 1st criteria Green Color part is 2nd Criteria =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) we can also use.. =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1,Duplicate,Unique) -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) amit.de...@merucabs.com wrote: Dear All, Can someone please explain how the below formula will work I mean what logic it will follow. Regards, Amit Desai From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 20 January 2012 16:22 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Sourabh, Please use this one to find unique value of more than one column without using helping column =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.com wrote: dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name =SUM(1/COUNTIF(B2:B6,B2:B6)) press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. =IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) see attached sheet.. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay
Re: $$Excel-Macros$$ Excel - Array Help
Hi Noorain, Thanks for your help. I've used your logic and implemented it accordingly, thanks again. Here is the code. SalesCount = 8 For K3 = 21 To 5000 If Cells(K3, 1).Value = ABC And Cells(K3 + 1, 2) = XYZ Then ColC = C K3 + 2 :C K3 + 1 + SalesCount End If Next K3 Set myrng = Range(ColC) 'MsgBox ColC myrng.Interior.ColorIndex = xlNone For Each cel In myrng clr = 10 If Application.WorksheetFunction.CountIf(myrng, cel) 1 Then Countbr = 1 cel.Interior.ColorIndex = clr clr = clr + 1 End If Next If Countbr = 1 Then MsgBox Brands repeated/ duplicates at sales, vbCritical Thanks, Amar On 21/01/2012, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amit, In this formula we are searching unique value of two columns Red Color part is 1st criteria Green Color part is 2nd Criteria =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) we can also use.. =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1,Duplicate,Unique) -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/* *** On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) amit.de...@merucabs.com wrote: Dear All, ** ** Can someone please explain how the below formula will work I mean what logic it will follow. ** ** Regards, *Amit Desai* ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI *Sent:* 20 January 2012 16:22 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Excel - Array Help ** ** Dear Sourabh, Please use this one to find unique value of more than one column without using helping column *=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique)* See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col ** ** On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ ** ** On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.com wrote: * * dear sir plz tell me that how i can these fourmula on multiple match ** ** like attachment. ** ** On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ ** ** On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help
Re: $$Excel-Macros$$ Excel - Array Help
Dear Amit, In this formula we are searching unique value of two columns Red Color part is 1st criteria Green Color part is 2nd Criteria =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) we can also use.. =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)1,Duplicate,Unique) -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/* *** On Sat, Jan 21, 2012 at 11:27 AM, Amit Desai (MERU) amit.de...@merucabs.com wrote: Dear All, ** ** Can someone please explain how the below formula will work I mean what logic it will follow. ** ** Regards, *Amit Desai* ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI *Sent:* 20 January 2012 16:22 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Excel - Array Help ** ** Dear Sourabh, Please use this one to find unique value of more than one column without using helping column *=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique)* See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col ** ** On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ ** ** On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.com wrote: * * dear sir plz tell me that how i can these fourmula on multiple match ** ** like attachment. ** ** On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ ** ** On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com ** ** -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
Re: $$Excel-Macros$$ Excel - Array Help
Dear Sourabh, Please use this one to find unique value of more than one column without using helping column *=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) * See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.comwrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: * * dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise,
RE: $$Excel-Macros$$ Excel - Array Help
Dear All, Can someone please explain how the below formula will work I mean what logic it will follow. Regards, Amit Desai From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: 20 January 2012 16:22 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel - Array Help Dear Sourabh, Please use this one to find unique value of more than one column without using helping column =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,Duplicate,Unique) See attached sheet. On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.commailto:rhtdmja...@gmail.com wrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.commailto:noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.commailto:rhtdmja...@gmail.com wrote: dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.commailto:noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name =SUM(1/COUNTIF(B2:B6,B2:B6)) press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. =IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) see attached sheet.. -- Thanks regards, Noorain Ansari http://excelmacroworld.blogspot.com/ http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.commailto:kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.commailto:excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202tel:%2B91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook
Re: $$Excel-Macros$$ Excel - Array Help
Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.comwrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Amar(Solution).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel - Array Help
Hi Noorain, Thank you for these formulaes. but I'm actually using VBA macro's to do a task it'll be really great if you could let me know in VBA. Here is my current code, I don't know I'm not able to highlight the duplicates. Sub Test1() For J1 = 10 To 500 If Cells(J1, 1).Value = XYZ And Cells(J1 + 1, 2) = ABC Then J1 = J1 + 1 For k = 1 To 7 If Cells(J1 + k, 2).Value = Key Then For Each i In Range(C:C) If i.Value = Cells(J1, 3).Value Then Count = Count + 1 If Count = 1 Then Cells(J1 + k, 3).Interior.color = 65535 'MsgBox Amar 'Cells(J1 + l + 1, 3).Interior.color = 65535 + 2000 End If Next i End If Next k If Count = 1 Then MsgBox Duplicates are there, please check End If End Sub On 19/01/2012, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.comwrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Duplicates_Identification.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Excel - Array Help
Dear Amar, Please try it.. Sub Find_Duplicate_Entry() Dim cel As Variant Dim myrng As Range Set myrng = Range(C2:C Range(C65536).End(xlUp).Row) myrng.Interior.ColorIndex = xlNone For Each cel In myrng clr = 10 If Application.WorksheetFunction.CountIf(myrng, cel) 1 Then cel.Interior.ColorIndex = 10 + clr clr = clr + 10 End If Next End Sub -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:59 PM, Kasireddy Amarender kassi.re...@gmail.comwrote: Hi Noorain, Thank you for these formulaes. but I'm actually using VBA macro's to do a task it'll be really great if you could let me know in VBA. Here is my current code, I don't know I'm not able to highlight the duplicates. Sub Test1() For J1 = 10 To 500 If Cells(J1, 1).Value = XYZ And Cells(J1 + 1, 2) = ABC Then J1 = J1 + 1 For k = 1 To 7 If Cells(J1 + k, 2).Value = Key Then For Each i In Range(C:C) If i.Value = Cells(J1, 3).Value Then Count = Count + 1 If Count = 1 Then Cells(J1 + k, 3).Interior.color = 65535 'MsgBox Amar 'Cells(J1 + l + 1, 3).Interior.color = 65535 + 2000 End If Next i End If Next k If Count = 1 Then MsgBox Duplicates are there, please check End If End Sub On 19/01/2012, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/* http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.comwrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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
Re: $$Excel-Macros$$ Excel - Array Help
Hi Noorain, Thank you for the code, I think I'm getting some clue for my code, I'll try and let you know, thanks again. Best regards, Amar On 19/01/2012, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please try it.. Sub Find_Duplicate_Entry() Dim cel As Variant Dim myrng As Range Set myrng = Range(C2:C Range(C65536).End(xlUp).Row) myrng.Interior.ColorIndex = xlNone For Each cel In myrng clr = 10 If Application.WorksheetFunction.CountIf(myrng, cel) 1 Then cel.Interior.ColorIndex = 10 + clr clr = clr + 10 End If Next End Sub -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:59 PM, Kasireddy Amarender kassi.re...@gmail.comwrote: Hi Noorain, Thank you for these formulaes. but I'm actually using VBA macro's to do a task it'll be really great if you could let me know in VBA. Here is my current code, I don't know I'm not able to highlight the duplicates. Sub Test1() For J1 = 10 To 500 If Cells(J1, 1).Value = XYZ And Cells(J1 + 1, 2) = ABC Then J1 = J1 + 1 For k = 1 To 7 If Cells(J1 + k, 2).Value = Key Then For Each i In Range(C:C) If i.Value = Cells(J1, 3).Value Then Count = Count + 1 If Count = 1 Then Cells(J1 + k, 3).Interior.color = 65535 'MsgBox Amar 'Cells(J1 + l + 1, 3).Interior.color = 65535 + 2000 End If Next i End If Next k If Count = 1 Then MsgBox Duplicates are there, please check End If End Sub On 19/01/2012, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/* http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.comwrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
Re: $$Excel-Macros$$ Excel - Array Help
* * dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com saurabh.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel - Array Help
Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: * * dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive
Re: $$Excel-Macros$$ Excel - Array Help
Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: * * dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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
Re: $$Excel-Macros$$ Excel - Array Help
and plz explain the sum formula of counting unique records On Fri, Jan 20, 2012 at 6:50 AM, Sourabh Salgotra rhtdmja...@gmail.comwrote: Thank you so much sir, if we want without help. col On Thu, Jan 19, 2012 at 11:35 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Sourabh, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 10:45 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: * * dear sir plz tell me that how i can these fourmula on multiple match like attachment. On Thu, Jan 19, 2012 at 2:42 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amar, Please use below formula to count total unique name *=SUM(1/COUNTIF(B2:B6,B2:B6)) *press ctrl+Shift+Enter or use this one to check unique or Duplicate Entry. *=IF(COUNTIF($B$2:B2,B2)1,Duplicate,Unique) * see attached sheet.. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Thu, Jan 19, 2012 at 2:25 PM, Kasireddy Amarender kassi.re...@gmail.com wrote: Hi Experts, I'm facing a problem in identifying duplicate names in a column. Here is what I'm looking for. If there are duplicates in column A i.e. A1 = Amar A2= Vijay A3 = Suresh A4 = Ravi A5 = Amar Then I would like to interior color A1 and A5 as they are duplicates, I would like to use the cell references rather than Range's as in my sheet Column is fixed but rows are not fixed. It'll be great if you can help me with this. Thanks in advance, Amar -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- mujhay dukh is baat ka nahin kay meri zaat ko muntashir karny walay haath tairy thy mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko samaitnay walay haath tairy na thy Thanks Regards Sourabh Contact Numbers: +91-94630-49202 Website:http://adhurapyaar.co.cc -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
