Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
Hello and thanks for your time reading this post.
If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
Try this macro:
Sub DeleteDuplicateRows() ' ' This macro deletes duplicate rows in the selection. Duplicates are ' counted in the COLUMN of the active cell. Dim Col As Integer Dim r As Long Dim c As Range Dim n As Long Dim v As Variant Dim rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.count 1 Then Set rng = Selection Else Set rng = ActiveSheet.UsedRange.Rows End If n = 0 For r = rng.Rows.count To 1 Step -1 v = rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(rng.Columns( 1), v) 1 Then rng.Rows(r).EntireRow.Delete n = n + 1 End If Next r EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Regards, Ryan--- -- RyGuy "dab" wrote: Hello and thanks for your time reading this post. If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
If the first macro doesn't give you your desired results, try this one (which
checks multiple columns, not just Column A): Sub DeleteDuplicateRows() Dim lLastRow As Long Dim lLastCol As Long Dim i As Long Dim j As Long Dim k As Long lLastRow = ActiveSheet.UsedRange.Rows.Count - 1 lLastCol = ActiveSheet.UsedRange.Columns.Count - 1 For i = 0 To lLastRow - 1 For j = lLastRow To i + 1 Step -1 For k = 0 To lLastCol If ActiveSheet.Range("A1").Offset(i, k).Value < ActiveSheet.Range("A1").Offset(j, k).Value Then Exit For End If Next k If k lLastCol Then ActiveSheet.Range("A1").Offset(j, 0).EntireRow.Delete End If Next j Next i End Sub 'Remember, you are DELETING data; try these macros on a sample of your data. You definitely don't want to accidentally delete data because of some silly/preventable mistake. Regards, Ryan--- -- RyGuy "ryguy7272" wrote: Try this macro: Sub DeleteDuplicateRows() ' ' This macro deletes duplicate rows in the selection. Duplicates are ' counted in the COLUMN of the active cell. Dim Col As Integer Dim r As Long Dim c As Range Dim n As Long Dim v As Variant Dim rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.count 1 Then Set rng = Selection Else Set rng = ActiveSheet.UsedRange.Rows End If n = 0 For r = rng.Rows.count To 1 Step -1 v = rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(rng.Columns( 1), v) 1 Then rng.Rows(r).EntireRow.Delete n = n + 1 End If Next r EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Regards, Ryan--- -- RyGuy "dab" wrote: Hello and thanks for your time reading this post. If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
Do you really need the unique data in all columns at the same time? You can
get the unique data in in one column by using Advanced filter from Data in the main menu. Use the list from this first operation and get the corresponding data from the othe columns with Vlookup function to retrieve the other values. 1. Get unique data only: Data Filter Advanced Filter 2. Prepare a new table with Vlookup function looking up the rows corresponding to the unique data in the original table P "dab" wrote: Hello and thanks for your time reading this post. If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
Hello Ryan
Thanks but unfortunately I am new to macros and I can't seem to make it work. It said there is a sintax error on this line and it is on red: If ActiveSheet.Range("A1").Offset(i, k).Value < ActiveSheet.Range("A1").Offset(j, k).Value Then I have no clue how to go about this. Sorry and thank again for your help. Daniel "ryguy7272" wrote: If the first macro doesn't give you your desired results, try this one (which checks multiple columns, not just Column A): Sub DeleteDuplicateRows() Dim lLastRow As Long Dim lLastCol As Long Dim i As Long Dim j As Long Dim k As Long lLastRow = ActiveSheet.UsedRange.Rows.Count - 1 lLastCol = ActiveSheet.UsedRange.Columns.Count - 1 For i = 0 To lLastRow - 1 For j = lLastRow To i + 1 Step -1 For k = 0 To lLastCol If ActiveSheet.Range("A1").Offset(i, k).Value < ActiveSheet.Range("A1").Offset(j, k).Value Then Exit For End If Next k If k lLastCol Then ActiveSheet.Range("A1").Offset(j, 0).EntireRow.Delete End If Next j Next i End Sub 'Remember, you are DELETING data; try these macros on a sample of your data. You definitely don't want to accidentally delete data because of some silly/preventable mistake. Regards, Ryan--- -- RyGuy "ryguy7272" wrote: Try this macro: Sub DeleteDuplicateRows() ' ' This macro deletes duplicate rows in the selection. Duplicates are ' counted in the COLUMN of the active cell. Dim Col As Integer Dim r As Long Dim c As Range Dim n As Long Dim v As Variant Dim rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.count 1 Then Set rng = Selection Else Set rng = ActiveSheet.UsedRange.Rows End If n = 0 For r = rng.Rows.count To 1 Step -1 v = rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(rng.Columns( 1), v) 1 Then rng.Rows(r).EntireRow.Delete n = n + 1 End If Next r EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Regards, Ryan--- -- RyGuy "dab" wrote: Hello and thanks for your time reading this post. If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
Hi Petter
Yes, I do need to keep the data as it is. I guess I have to learn a bit more about these functions like Vlookup, etc. I will keep trying Thank you for your time. Daniel "Petter" wrote: Do you really need the unique data in all columns at the same time? You can get the unique data in in one column by using Advanced filter from Data in the main menu. Use the list from this first operation and get the corresponding data from the othe columns with Vlookup function to retrieve the other values. 1. Get unique data only: Data Filter Advanced Filter 2. Prepare a new table with Vlookup function looking up the rows corresponding to the unique data in the original table P "dab" wrote: Hello and thanks for your time reading this post. If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help filtering data
Hello Ryan
This one works like a charm. I will see if I can make the other one to work, most likely I did something wrong when I tried it. Thanks again Daniel "ryguy7272" wrote: Try this macro: Sub DeleteDuplicateRows() ' ' This macro deletes duplicate rows in the selection. Duplicates are ' counted in the COLUMN of the active cell. Dim Col As Integer Dim r As Long Dim c As Range Dim n As Long Dim v As Variant Dim rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Col = ActiveCell.Column If Selection.Rows.count 1 Then Set rng = Selection Else Set rng = ActiveSheet.UsedRange.Rows End If n = 0 For r = rng.Rows.count To 1 Step -1 v = rng.Cells(r, 1).Value If Application.WorksheetFunction.CountIf(rng.Columns( 1), v) 1 Then rng.Rows(r).EntireRow.Delete n = n + 1 End If Next r EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Regards, Ryan--- -- RyGuy "dab" wrote: Hello and thanks for your time reading this post. If this question has been answered before I will be glad to look at it but so far I could not find it. I dont know how to program in Excel so I was wondering if there is a command or formula that will do this: I have a list of numbers like A B C 1 135 200 351 2 135 200 351 3 500 179 165 4 500 179 165 5 702 205 140 6 702 205 140 And after the formula or function the result I need is: A B C 1 135 200 351 2 500 179 165 3 702 205 140 So basically, it will remove any extra line containing the same values and return only one. Thanks in advance for your help. Best regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering data | Excel Discussion (Misc queries) | |||
filtering of data | Excel Discussion (Misc queries) | |||
filtering data | Excel Discussion (Misc queries) | |||
Filtering Data | Excel Discussion (Misc queries) | |||
Filtering out Data | Excel Discussion (Misc queries) |