Replacing Duplicates
Solved/Closed
whatsit
Posts
5
Registration date
Sunday October 19, 2014
Status
Member
Last seen
February 15, 2016
-
Oct 19, 2014 at 07:52 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 20, 2015 at 11:14 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 20, 2015 at 11:14 AM
Related:
- Replacing Duplicates
- Replacing batteries in firestick remote - Guide
- Will itunes import duplicates - Guide
- Cmos checksum is invalid hp after replacing battery - Guide
- Monitor no signal after replacing cpu - Guide
- On LinkedIn, AI Is Gradually Replacing Human Influencers (And No One Has Noticed) - Home - Apps & Sites
30 responses
Hello Again Trowa
I have run the code and it worked on "pair names 2" column 'O' and insofar as Column D in "Round 3" cells changed from the original figures but this was the result:-
Original column D New Column D
cell 12 20 cell 12 20
cell 13 17 cell 13 8
cell 14 8 cell 14 8
cell 15 12 cell 15 20
It would appear from this that your second option may be a possibility but the first is more correct I am encouraged by your input to this problem.
I am assuming that this works on each "pair names" sheet.
Question :- If at some later date I wish to move columns L thru S to another location
from "pair names"(to a different sheet) will the code work ? This is not crucial but just a general question.
I look forward to your response.
I have run the code and it worked on "pair names 2" column 'O' and insofar as Column D in "Round 3" cells changed from the original figures but this was the result:-
Original column D New Column D
cell 12 20 cell 12 20
cell 13 17 cell 13 8
cell 14 8 cell 14 8
cell 15 12 cell 15 20
It would appear from this that your second option may be a possibility but the first is more correct I am encouraged by your input to this problem.
I am assuming that this works on each "pair names" sheet.
Question :- If at some later date I wish to move columns L thru S to another location
from "pair names"(to a different sheet) will the code work ? This is not crucial but just a general question.
I look forward to your response.
Hello Trowa
Since I have not heard from you in a while I am wondering whether you have got any further with my problem on Replacing Duplicates.
Since I have not heard from you in a while I am wondering whether you have got any further with my problem on Replacing Duplicates.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 5, 2015 at 11:51 AM
Jan 5, 2015 at 11:51 AM
Hi Whatsit,
Wow, I completely missed your Nov 19 post, so I was still waiting for a reply. My apologies.
Not a lot of time today, I will get back to you.
Best regards,
Trowa
Wow, I completely missed your Nov 19 post, so I was still waiting for a reply. My apologies.
Not a lot of time today, I will get back to you.
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 6, 2015 at 12:13 PM
Jan 6, 2015 at 12:13 PM
Hi Whatsit,
I'm glad the code yields the correct results.
Now you say the results in column D of the "Round 3" sheet is incorrect, which should show the same order as column O of the "Pair names" sheet. Why don't you link the two columns? I followed the link you have now and that is way to many.
Will that work for you?
Then the question about moving columns:
The only things that are hard coded (fixed values) are the first row of data (which is row 3) and the ranking column (which is column L). So if you move the columns, the rows won't change so you only have to change the L column reference in the code, which can be found on code line 13. Also the number 12 refers to the L column, which is used twice in the code; code line 6 and 21.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
I'm glad the code yields the correct results.
Now you say the results in column D of the "Round 3" sheet is incorrect, which should show the same order as column O of the "Pair names" sheet. Why don't you link the two columns? I followed the link you have now and that is way to many.
Will that work for you?
Then the question about moving columns:
The only things that are hard coded (fixed values) are the first row of data (which is row 3) and the ranking column (which is column L). So if you move the columns, the rows won't change so you only have to change the L column reference in the code, which can be found on code line 13. Also the number 12 refers to the L column, which is used twice in the code; code line 6 and 21.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Hello Trowa.
Before running the code I did link Column D in Round 3 to Column O in Pair Names . After running the code the result was as indicated in my post of November 19. It seems that your second option (refer your Nov 18 post) may work although not statistically correct. If it does work then it would be acceptable. Please would you see if your code can be altered to achieve the second alternative if the first one cannot be achieved. It appears that you maybe 99.5% there.I look forward to your response.
Before running the code I did link Column D in Round 3 to Column O in Pair Names . After running the code the result was as indicated in my post of November 19. It seems that your second option (refer your Nov 18 post) may work although not statistically correct. If it does work then it would be acceptable. Please would you see if your code can be altered to achieve the second alternative if the first one cannot be achieved. It appears that you maybe 99.5% there.I look forward to your response.
Didn't find the answer you are looking for?
Ask a question
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 8, 2015 at 11:14 AM
Jan 8, 2015 at 11:14 AM
Hi Whatsit,
Apparently there is something wrong with linking the two columns. Because the result of the code is good, but the linked data isn't.
Can you check in the file below if you can get the same faulty results?
When you open the file, you will start at sheets "ROUND 3" where column D shows a bunch of zero's. Go to sheet "PAIR NAMES 2" run the macro called RunMeV2 and enter an "o" when prompted. Now column D of sheet "ROUND 3" will (should) show the same as column O of sheet "PAIR NAMES 2".
Of course you know what to do, but I want to make sure we do the same thing.
Here is the file:
http://ge.tt/2vH6nB82/v/0
PS. After running the code on column O, I did it again on column P and noticed tiny adjustment was needed. The number 4 on code line 24 should be a 3. This has already been adjusted in the file above.
Best regards,
Trowa
Apparently there is something wrong with linking the two columns. Because the result of the code is good, but the linked data isn't.
Can you check in the file below if you can get the same faulty results?
When you open the file, you will start at sheets "ROUND 3" where column D shows a bunch of zero's. Go to sheet "PAIR NAMES 2" run the macro called RunMeV2 and enter an "o" when prompted. Now column D of sheet "ROUND 3" will (should) show the same as column O of sheet "PAIR NAMES 2".
Of course you know what to do, but I want to make sure we do the same thing.
Here is the file:
http://ge.tt/2vH6nB82/v/0
PS. After running the code on column O, I did it again on column P and noticed tiny adjustment was needed. The number 4 on code line 24 should be a 3. This has already been adjusted in the file above.
Best regards,
Trowa
Hello Trowa,
I ran the code as requested. The following was the result:-
Pair Names 2:- Column L Column O
Cell 3 Pair 15 Playing 2
Cell 4 Pair 2 Playing 15
Cell 5 Pair 4 Playing 9
Cell 6 Pair 9 Playing 4
Cell 7 Pair 10 Playing 5
Cell 8 Pair 5 Playing 10
Cell 9 Pair 19 Playing 14
Cell 10 Pair 14 Playing 19
Cell 11 Pair 20 Playing 8
Cell 12 Pair 17 Playing 12
Cell 13 Pair 8 Playing 20
Cell 14 Pair 12 Playing 17
Cell 15 Pair 13 Playing 18
Cell 16 Pair 18 Playing 13
Cell 17 Pair 3 Playing 7
Cell 18 Pair 7 Playing 3
Cell 19 Pair 16 Playing 11
Cell 20 Pair 11 Playing 16
Cell 21 Pair 6 Playing 1
Cell 22 Pair 1 Playing 6
In Round 3, Column D recorded everything correctly except it shows Pair 20 playing Pair 17 and Pair 8 playing Pair 12. In the above extract from Pair Names 2 , Pair 20 is shown as playing Pair 8 and Pair 17 is shown as playing Pair 12. After running the code Column O in Pair Names 2 seems to record the correct pairs playing against each other but the start of Round 3 (Column D ) is incorrect (the original duplicated pairing).
I look forward to your comments.
I ran the code as requested. The following was the result:-
Pair Names 2:- Column L Column O
Cell 3 Pair 15 Playing 2
Cell 4 Pair 2 Playing 15
Cell 5 Pair 4 Playing 9
Cell 6 Pair 9 Playing 4
Cell 7 Pair 10 Playing 5
Cell 8 Pair 5 Playing 10
Cell 9 Pair 19 Playing 14
Cell 10 Pair 14 Playing 19
Cell 11 Pair 20 Playing 8
Cell 12 Pair 17 Playing 12
Cell 13 Pair 8 Playing 20
Cell 14 Pair 12 Playing 17
Cell 15 Pair 13 Playing 18
Cell 16 Pair 18 Playing 13
Cell 17 Pair 3 Playing 7
Cell 18 Pair 7 Playing 3
Cell 19 Pair 16 Playing 11
Cell 20 Pair 11 Playing 16
Cell 21 Pair 6 Playing 1
Cell 22 Pair 1 Playing 6
In Round 3, Column D recorded everything correctly except it shows Pair 20 playing Pair 17 and Pair 8 playing Pair 12. In the above extract from Pair Names 2 , Pair 20 is shown as playing Pair 8 and Pair 17 is shown as playing Pair 12. After running the code Column O in Pair Names 2 seems to record the correct pairs playing against each other but the start of Round 3 (Column D ) is incorrect (the original duplicated pairing).
I look forward to your comments.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 19, 2015 at 11:51 AM
Jan 19, 2015 at 11:51 AM
Hi Whatsit,
Now I see what the issue is. Column D isn't a replica of column O, but shows the match up's. Guess I didn't think it through.
Try these formula's for column D (ROUND 3):
D4: ='PAIR NAMES 2'!L3
D5: ='PAIR NAMES 2'!O3
D6: =IF(ISERROR(MATCH('PAIR NAMES 2'!L5,$D$4:$D5,0)),'PAIR NAMES 2'!L5,'PAIR NAMES 2'!L6)
D7: =IF(ISERROR(MATCH('PAIR NAMES 2'!L6,$D$4:$D6,0)),'PAIR NAMES 2'!O5,'PAIR NAMES 2'!O6)
Now select both D6 and D7 and drag them down until row 23.
Do try different scenario's to see if the formula's keep showing the right result.
Best regards,
Trowa
Now I see what the issue is. Column D isn't a replica of column O, but shows the match up's. Guess I didn't think it through.
Try these formula's for column D (ROUND 3):
D4: ='PAIR NAMES 2'!L3
D5: ='PAIR NAMES 2'!O3
D6: =IF(ISERROR(MATCH('PAIR NAMES 2'!L5,$D$4:$D5,0)),'PAIR NAMES 2'!L5,'PAIR NAMES 2'!L6)
D7: =IF(ISERROR(MATCH('PAIR NAMES 2'!L6,$D$4:$D6,0)),'PAIR NAMES 2'!O5,'PAIR NAMES 2'!O6)
Now select both D6 and D7 and drag them down until row 23.
Do try different scenario's to see if the formula's keep showing the right result.
Best regards,
Trowa
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jan 20, 2015 at 03:43 AM
Jan 20, 2015 at 03:43 AM
Hi Trowa,
This is good. I've been keeping an eye on this thread and I've been totally amazed at what you've done in working through this one.
Nearly there!
(I think that Whatsit may have to buy you that brewery!)
I'm eagerly awaiting Whatsit's reply!
Cheers,
vcoolio.
This is good. I've been keeping an eye on this thread and I've been totally amazed at what you've done in working through this one.
Nearly there!
(I think that Whatsit may have to buy you that brewery!)
I'm eagerly awaiting Whatsit's reply!
Cheers,
vcoolio.
Hello Trowa
Thank you for your response. Firstly in reply to VCOOLIO the formula worked ! Trowa has done a magnificent job and has been very patient. I will have to give it more testing on other scenarios as suggested by you but I do not doubt that it will work . If I do find a problem please allow me to get back to you. No doubt that you found this a challenging problem !
I do appreciate all the work you have put in but cannot afford to buy you a Brewery!
Please continue your good work for others who may be in need like me. They will (I hope) be appreciative also.
Thank you for your response. Firstly in reply to VCOOLIO the formula worked ! Trowa has done a magnificent job and has been very patient. I will have to give it more testing on other scenarios as suggested by you but I do not doubt that it will work . If I do find a problem please allow me to get back to you. No doubt that you found this a challenging problem !
I do appreciate all the work you have put in but cannot afford to buy you a Brewery!
Please continue your good work for others who may be in need like me. They will (I hope) be appreciative also.
freebrushes
Posts
1
Registration date
Tuesday January 20, 2015
Status
Member
Last seen
January 20, 2015
Jan 20, 2015 at 06:08 AM
Jan 20, 2015 at 06:08 AM
Thanks very much!
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 20, 2015 at 11:14 AM
Jan 20, 2015 at 11:14 AM
Thanks guys, your responses make it all worth while.
I'll assist where I can!
Best regards,
Trowa
I'll assist where I can!
Best regards,
Trowa
