Copy below code and paste it in a module and run it. I have tested it in
your attached file so please make sure you are also using it in the same.
*Option Explicit*
*
*
*Sub Test_Lalit_Removing_Duplicate()*
*
*
* Dim varArrData() As Variant*
* Dim varArrFinalData() As Variant*
* Dim varArrKeys() As Variant*
* Dim varItemCount() As Variant*
* Dim varArrItem() As Variant*
* Dim varArrTemp As Variant*
* Dim lngLoop As Long*
* Dim lngLoop1 As Long*
* Dim objDic As Object*
* Dim objDicItem As Object*
* *
* Const strDataRange As String = "A2:E493"*
* Const strDataShtName As String = "Sheet2"*
* Const strArrIsBlank As String = "ArrayIsBlankOrHavingNoData"*
* Const strConcatDelima As String = "|"*
* Const strFinalDataCell As String = "J2"*
* *
* With ThisWorkbook.Worksheets(strDataShtName)*
* ReDim varArrData(1, 1)*
* varArrData(1, 1) = strArrIsBlank*
* On Error Resume Next*
* varArrData = .Range(strDataRange).Value*
* On Error GoTo -1: On Error GoTo 0: Err.Clear*
* If varArrData(1, 1) <> strArrIsBlank Then*
* Set objDic = CreateObject("Scripting.Dictionary")*
* objDic.comparemode = 1*
* For lngLoop = LBound(varArrData) To UBound(varArrData)*
* If objDic.exists(varArrData(lngLoop, UBound(varArrData,
2))) Then*
* objDic.Item(varArrData(lngLoop, UBound(varArrData,
2))) = objDic.Item(varArrData(lngLoop, UBound(varArrData, 2))) &
strConcatDelima & lngLoop*
* Else*
* objDic.Item(varArrData(lngLoop, UBound(varArrData,
2))) = lngLoop*
* End If*
* Next lngLoop*
* varArrKeys = objDic.keys*
* ReDim varArrFinalData(1 To objDic.Count, 1 To
UBound(varArrData, 2))*
* Set objDicItem = CreateObject("Scripting.Dictionary")*
* objDicItem.comparemode = 1*
* For lngLoop = LBound(varArrKeys) To UBound(varArrKeys)*
* If Len(objDic.Item(varArrData(lngLoop + 1,
UBound(varArrData, 2)))) > 1 Then*
* varArrTemp = Split(CStr(objDic.Item(varArrData(lngLoop
+ 1, UBound(varArrData, 2)))), strConcatDelima)*
* Else*
* ReDim varArrTemp(0 To 0)*
* varArrTemp(0) = objDic.Item(varArrData(lngLoop + 1,
UBound(varArrData, 2)))*
* End If*
* If Not objDicItem.exists(varArrData(varArrTemp(0), 5)) Then
*
* If LBound(varArrTemp) = UBound(varArrTemp) Then*
* objDicItem.Item(varArrData(varArrTemp(0), 5)) =
Null*
* varArrFinalData(objDicItem.Count, 1) =
varArrData(varArrTemp(0), 1)*
* varArrFinalData(objDicItem.Count, 2) =
varArrData(varArrTemp(0), 2)*
* varArrFinalData(objDicItem.Count, 3) =
varArrData(varArrTemp(0), 3)*
* varArrFinalData(objDicItem.Count, 4) =
varArrData(varArrTemp(0), 4)*
* varArrFinalData(objDicItem.Count, 5) =
varArrData(varArrTemp(0), 5)*
* Else*
* ReDim Preserve varItemCount(0 To
UBound(varArrTemp), 0 To 1)*
* varItemCount(0, 0) = 0*
* For lngLoop1 = LBound(varArrTemp) To
UBound(varArrTemp)*
* varItemCount(lngLoop1, 1) =
CLng(varArrTemp(lngLoop1))*
* If Len(Trim(varArrData(varArrTemp(lngLoop1),
1))) > 0 Then*
* varItemCount(lngLoop1, 0) =
CLng(varItemCount(lngLoop1, 0)) + 1*
* End If*
* If Len(Trim(varArrData(varArrTemp(lngLoop1),
2))) > 0 Then*
* varItemCount(lngLoop1, 0) =
CLng(varItemCount(lngLoop1, 0)) + 1*
* End If*
* If Len(Trim(varArrData(varArrTemp(lngLoop1),
3))) > 0 Then*
* varItemCount(lngLoop1, 0) =
CLng(varItemCount(lngLoop1, 0)) + 1*
* End If*
* If Len(Trim(varArrData(varArrTemp(lngLoop1),
4))) > 0 Then*
* varItemCount(lngLoop1, 0) =
CLng(varItemCount(lngLoop1, 0)) + 1*
* End If*
* If Len(Trim(varArrData(varArrTemp(lngLoop1),
5))) > 0 Then*
* varItemCount(lngLoop1, 0) =
CLng(varItemCount(lngLoop1, 0)) + 1*
* End If*
* Next lngLoop1*
* ReDim varArrItem(0 To 0, 0 To 1)*
* For lngLoop1 = UBound(varItemCount) To
LBound(varItemCount) Step -1*
* If lngLoop1 = UBound(varItemCount) Then*
* varArrItem(0, 0) = varItemCount(lngLoop1,
0)*
* varArrItem(0, 1) = varItemCount(lngLoop1,
1)*
* ElseIf varArrItem(0, 0) <
varItemCount(lngLoop1, 0) Then*
* varArrItem(0, 0) = varItemCount(lngLoop1,
0)*
* varArrItem(0, 1) = varItemCount(lngLoop1,
1)*
* End If*
* Next lngLoop1*
* objDicItem.Item(varArrData(varArrItem(0, 1), 5)) =
Null*
* varArrFinalData(objDicItem.Count, 1) =
varArrData(varArrItem(0, 1), 1)*
* varArrFinalData(objDicItem.Count, 2) =
varArrData(varArrItem(0, 1), 2)*
* varArrFinalData(objDicItem.Count, 3) =
varArrData(varArrItem(0, 1), 3)*
* varArrFinalData(objDicItem.Count, 4) =
varArrData(varArrItem(0, 1), 4)*
* varArrFinalData(objDicItem.Count, 5) =
varArrData(varArrItem(0, 1), 5)*
* Erase varItemCount*
* Erase varArrItem*
* End If*
* End If*
* Erase varArrTemp*
* Next lngLoop*
* .Range(strFinalDataCell).CurrentRegion.ClearContents*
* .Range(strFinalDataCell).Offset(-1).Resize(1,
UBound(varArrFinalData, 2)).Value =
.Range(strDataRange).Resize(1).Offset(-1).Value*
* .Range(strFinalDataCell).Resize(UBound(varArrFinalData),
UBound(varArrFinalData, 2)).Value = varArrFinalData*
* .Range(strFinalDataCell).Resize(UBound(varArrFinalData),
UBound(varArrFinalData, 2)).EntireColumn.AutoFit*
* End If*
* End With*
* *
* Erase varArrData*
* Erase varArrFinalData*
* Erase varArrKeys*
* Erase varItemCount*
* Erase varArrItem*
* varArrTemp = Empty*
* lngLoop = Empty*
* lngLoop1 = Empty*
* Set objDic = Nothing*
* Set objDicItem = Nothing*
*
*
*End Sub*
On Thursday, 4 April 2013 10:18:11 UTC+5:30, chaya moni wrote:
>
> Sir, Might contains data in second row that which might not contains in
> the first row in duplicate.
>
> Ragards,
> Chaya
>
>
> On Thu, Apr 4, 2013 at 10:06 AM, Lalit Mohan Pandey
> <mohan.p...@gmail.com<javascript:>
> > wrote:
>
>> What is the criteria to not delete duplicates.
>>
>>
>> On Thursday, 4 April 2013 09:34:49 UTC+5:30, chaya moni wrote:
>>
>>> Dear Lalit Sir,
>>>
>>> Thanks for the reply. i tried to explain a bit more in the
>>> attached file. please refer sir.
>>>
>>> Regards,
>>> Chaya
>>>
>>>
>>> On Thu, Apr 4, 2013 at 8:58 AM, Lalit Mohan Pandey <mohan.p...@gmail.com
>>> > wrote:
>>>
>>>> I am not able to understand your both conditions :(.
>>>> First select all column then remove duplicate. (Only 29 duplicate value
>>>> found and removed.)
>>>>
>>>> On Wednesday, 3 April 2013 18:37:15 UTC+5:30, chaya moni wrote:
>>>>>
>>>>> Dear Experts,
>>>>>
>>>>> I have another problem arise in excel while removing duplicate.
>>>>> i have attached the sample and explained in the attachment. Please find
>>>>> the
>>>>> attachment.
>>>>>
>>>>> Thank you in advance
>>>>>
>>>>> Regards,
>>>>> Chaya
>>>>>
>>>> --
>>>> 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<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...@**googlegroups.com.
>>>> To post to this group, send email to excel-...@googlegroups.com.
>>>>
>>>> Visit this group at
>>>> http://groups.google.com/**group/excel-macros?hl=en<http://groups.google.com/group/excel-macros?hl=en>
>>>> .
>>>> For more options, visit
>>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>>>> .
>>>>
>>>>
>>>>
>>>
>>> --
>> 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...@googlegroups.com <javascript:>.
>> To post to this group, send email to excel-...@googlegroups.com<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
