Set value of cell to that of duplicate

Hello!

With some help on here I got the below code.
It searched for duplicates and marks the entire row with the colour of the first instance.
I, however, Need to do sth different but I can't figure out how.

Instead of colouring, I Need row P to get a number which is unique for every set of duplicates, and each duplicate Needs to get the number of the first instance in row P.
How can I Change this code do so?
As it is it gets the value of the duplicate and puts it the cell but I Need it to get the num of the first instance and insert it into the new cell.
I'd really appreciate any help whatsoever.

Please Login or Register  to view this content.

Originally Posted by p24leclerc

can you attach your workbook?

I have attached an extract of it as it's way to big. I've manually put in numbers in the last column as example, please note the group marked in red as those are sort of a "special" case. Thanks in advance!

Originally Posted by p24leclerc

your macro looks to a range from K2:N???.
can you attach a workbook with as many columns as your macro uses so we can test the actual macro.
We need your exact same structure you work with.
Also, as your variable myrng covers 4 columns (K,L,M and N) the results will be written to 4 columns (O,P,Q and R). Am I right?

That's correct, i forgot to change that, i removed unnecessary columns. It should look through C to F, the last for columns with telephone numbers and email addresses. The results should be written to the next free column, so everything in one column. What I have isn't doing what it's meant to do, that's where i need help.

Originally Posted by p24leclerc

try this modified macro.
note that I added a title in G1 so the macro knows where to put the numbers.
The macro actually search for the last used column which should be DUP.
I found a problem as if you have a duplicate email address but the phone numbers are different, it will change the reference number of only one of them.
This is happening for the email [email protected] which is in cell F233. This email is used by a precedent duplicate phone number (0151 1965908137) in row 112
which happen to already have the reference number 1. You'll see that in cell H233, the reference number is 4 and that you only have 1 of this reference number.

Please Login or Register  to view this content.

You could put the reference numbers in different columns (one for each of C,D,E and F).
What do you think?

Putting reference numbers in different columns won't really work as it's a way of sorting. I intend on then using the column with the reference numbers as a sorting column, say in ascending order. That's why they need to be in the same column and have the same number. The thing with the email is tricky, but since it's all one group it should ideally have the same reference number.

Or is there a way of writing a macro that gives all entries with the same colour the same reference in a certain column? This way I could use the first macro which works with colours then run the second macro.

Originally Posted by p24leclerc

Test this macro. It should take care of the above problem

Please Login or Register  to view this content.

Thanks for taking your time to help me out!!!

I haven't been able to test the code, as I'm getting a runtime error-type mismatch in the line marked bold.
Trying to find the error myself has proven almost impossible as I am still trying to understand the code.

Could you post what the results look like on your computer?
It won't work on my computer.
Also I undertook the aforementioned changes, it then runs but puts 1 in the first 15 columns of F or so and replaces the email addresses.

Great! If only it'd work on my PC as well.
It's got to run on PC set to German, and from looking around the culture could be the problem.
What is the culture of your PC?
Is there a way to manipulate the code to work on different cultures?

Run time error-type mismatch in
Temp_Num = Cells(cell.Row, Last_col)
I did too just realise that the CountIf function is the same in German as well.

Could the excel version be the problem? I'm using Excel 2010.

After debugging, I see temp_num has the value 0 which can't be right, but I don't know where to fix it.

Originally Posted by infogirl96

After debugging, I see temp_num has the value 0 which can't be right, but I don't know where to fix it.

Temp_Num = Cells(cell.Row, Last_col) is then 87(long), and 6 (integer). They should both be integers, right?

I made that change, but that didn't fix the issue.
I did run it on exactly what I uploaded here, and it isn't working on other workbooks neither.

I have tried on 2 computers, one running windows 7 and the other windows 10, both returning the same error. The latter has Excel 2016 and it isn't working there either.
But strangely enough producing the same error at the same place on both, F87. Both computers are however set to German, that may be the problem, I don't know really..

Originally Posted by p24leclerc

I think I finally found the problem. You are doing your test on your original workbook, aren't you?
On the workbook I attached, You'll see that I added 2 titles in row 1. Column G = ??? and Column H = DUP
When you run my macro on your original workbook, the Last_Col variable is set to 6 as you stated in one of your post. Column 6 being F which contains e-mail addresses.
With my modification of the titles, the Last_Col variable has the value of 8 which is column H where we want to put the duplicate number.
Column G contains alpha data whcih causes the error. Column H contains intergers and should not cause any error.
Either upload my workbook or do the modification to your title row (row 1) and every thing should work just fine.
Sorry about this issue and all the time I took to realize it.

If you want the numbering to start at number 1, then change this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That certainly was the problem, thank so much!!
Out of curiousity, what is Column ??? needed for