Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicate data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicate data
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" wrote in message ... 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 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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicate data
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" wrote in message ... Hi Ed, Thanks for this but I can't see that it will solve my problem. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking For Duplicate Entries in a Userform | Excel Discussion (Misc queries) | |||
Formula for checking duplicate numbers | Excel Discussion (Misc queries) | |||
Checking for duplicate entries | Excel Discussion (Misc queries) | |||
Checking for duplicate items in list | Excel Worksheet Functions | |||
checking for duplicate worksheets | Excel Programming |