Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Duplicate Rows (Macro)
Hi,
I have a table with several rows and columns of information. I will continue to add new information new rows and I want to make sure that I never have duplicated a row. So here is a simple example: A B C 1 i 1 k 2 r 3 n 3 a 2 f 4 a 1 f I want to be able to add another row of information and if the array in that row from columns a to c matches an array I already have I want to be notified and it would also be nice to know which array I am duplicating. I would like to accomplish the task above, but using the VBA tool (macros). Ideally I would get the Item Number (located in the first column) of the array Im duplicating in a message box. Any help would be greatly appreciated! Regards, -- Edwin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Duplicate Rows (Macro)
Here's a simplistic macro that might get you started. After inputting your
data, and having selected a cell in the row you want to check, you could run this. Sub FindDuplicate() xEnd = ActiveCell.Row For i = 1 To xEnd - 1 If Cells(i, 1).Value = Cells(xEnd, 1) Then If Cells(i, 2).Value = Cells(xEnd, 2) Then If Cells(i, 3).Value = Cells(xEnd, 3) Then MsgBox "Row " & i & " contains duplicate entry.", vbOKOnly, _ "Duplicate Found" Exit Sub End If End If End If Next MsgBox "No duplicate found", vbOKOnly, "No Duplicates" End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Edwin" wrote: Hi, I have a table with several rows and columns of information. I will continue to add new information new rows and I want to make sure that I never have duplicated a row. So here is a simple example: A B C 1 i 1 k 2 r 3 n 3 a 2 f 4 a 1 f I want to be able to add another row of information and if the array in that row from columns a to c matches an array I already have I want to be notified and it would also be nice to know which array I am duplicating. I would like to accomplish the task above, but using the VBA tool (macros). Ideally I would get the Item Number (located in the first column) of the array Im duplicating in a message box. Any help would be greatly appreciated! Regards, -- Edwin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Duplicate Rows (Macro)
Thanks Luke!! This is a great start!
Do you think it is possible to obtain the location of more than one duplicated row in the table? Maybe using a loop or something like that?? -- Edwin "Luke M" wrote: Here's a simplistic macro that might get you started. After inputting your data, and having selected a cell in the row you want to check, you could run this. Sub FindDuplicate() xEnd = ActiveCell.Row For i = 1 To xEnd - 1 If Cells(i, 1).Value = Cells(xEnd, 1) Then If Cells(i, 2).Value = Cells(xEnd, 2) Then If Cells(i, 3).Value = Cells(xEnd, 3) Then MsgBox "Row " & i & " contains duplicate entry.", vbOKOnly, _ "Duplicate Found" Exit Sub End If End If End If Next MsgBox "No duplicate found", vbOKOnly, "No Duplicates" End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Edwin" wrote: Hi, I have a table with several rows and columns of information. I will continue to add new information new rows and I want to make sure that I never have duplicated a row. So here is a simple example: A B C 1 i 1 k 2 r 3 n 3 a 2 f 4 a 1 f I want to be able to add another row of information and if the array in that row from columns a to c matches an array I already have I want to be notified and it would also be nice to know which array I am duplicating. I would like to accomplish the task above, but using the VBA tool (macros). Ideally I would get the Item Number (located in the first column) of the array Im duplicating in a message box. Any help would be greatly appreciated! Regards, -- Edwin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicate Rows | Excel Discussion (Misc queries) | |||
Delete Duplicate Rows Macro Question | Excel Discussion (Misc queries) | |||
finding duplicate rows | Excel Worksheet Functions | |||
Macro - to copy duplicate rows to another sheet | Excel Worksheet Functions | |||
finding duplicate then copying macro.. | Excel Discussion (Misc queries) |