Checking for duplicate data

J

Jeremy

Hi, I have already posted a question on this but am still struggling with it

I have a spreadsheet which has the cells in a row populated by code from from in a source sheet using the following

With worksheets("Deals"

Set rng = .cells(Rows,Count,1).End(x1up) (2
End wit

rng.Offset(0,0).Value= Worksheets("Sheet1").Range ("Customer"
rng.Offset(0,1).Value= Worksheets("Sheet1").Range ("Date"

etc for the rest of the cells in the row

I need the code to check the target sheet to ensure that the content from the source sheet is not already present in a previous row. i.e avoid duplicating information in the target. If so an eroor message is to be displayed and the operation cancelled
 
E

Ed

Hi, Jeremy. Have a look at Chip Pearson's website
(http://www.cpearson.com/excel/topic.htm) and Debra Dalgleish
(http://www.contextures.com).



Ed



Jeremy said:
Hi, I have already posted a question on this but am still struggling with it.

I have a spreadsheet which has the cells in a row populated by code from
Click to expand...
from in a source sheet using the following:
With worksheets("Deals")

Set rng = .cells(Rows,Count,1).End(x1up) (2)
End with

rng.Offset(0,0).Value= Worksheets("Sheet1").Range ("Customer")
rng.Offset(0,1).Value= Worksheets("Sheet1").Range ("Date")


etc for the rest of the cells in the row.


I need the code to check the target sheet to ensure that the content from
Click to expand...
the source sheet is not already present in a previous row. i.e avoid
duplicating information in the target. If so an eroor message is to be
displayed and the operation cancelled.
 
E

Ed

Well, you're in over my head, too! 8>\ I've not tried anything like that,
but I think if I were going to try it, I might do something like the
following:
Enter a row of data.
Use a MATCH formula to see if the data entered in the first column
exists in the column above it.
If Yes, grab the row number. Offset one cell in the new data row and in
the MATCHed row. Compare values; if equal, Offset and compare again until
the values don't match or the end of the data in that row is reached. If
all Yes, MsgBox "Houston, we have problems!" If not, rinse and repeat.

Then again, rather than do this dynamically as you enter a new row of data,
would it be possible and easier to enter all your data first, and then check
things?

Sorry, you've gotten all I've got! Wish I knew more and could help more.
The other thing I would recommend is Ron DeBruin's Google NG search add-in
(http://www.rondebruin.nl/Google.htm). You can find all the times other
people have asked similar questions.

Ed

jEREMY said:
Hi Ed, Thanks for this but I can't see that it will solve my problem.
Click to expand...
Under preventing Duplicate sit says that this will not work on data entered
via VBA. Also the data in the cells could be duplicated in individual
columns, it is the combination of the data in all the cells in the row that
I need to check for duplication against rows above the new entry
 
Top