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

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

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

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

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

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

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