Replacing DuplicatesSolved/Closed

Question

My  Excel spreadsheet is set out as follows:

A  B  C  D  E   F
1  2  14 0 14 14

2  1  13 0 13 13

3  4   4  1  4   4
 
4  3   3  1  3   17

5  6  17  0  17  3

6  5  12  0  12  8

7  8   8   1   8   12 

8  7   7   1   7    7
ETC
This is for a "king of the hill" game where no duplicate matches are allowed. Column A is the team number, Column B is Round 1 and Column C is Round 2 .Up to 7 Rounds will be played. Column D highlights duplicates by "1". Column E is the draw for Round 2(same as column C) and the draw I wish to achieve is in Column F. The resulting formula must cater for any situation highlighted by"1" in column D.
Please help with the required formula.
Thank you.

TrowaD · Answer

Hi Whatsit,

It is unclear to me what you are trying to achieve.

Are you looking for the formula in column D?

You say "no duplicate matches are allowed", what do you want to happen to those duplicate matches aside from a "1" in column D?

Best regards,
Trowa

whatsit · Answer

Hello Trowa
Thank you for responding. I am sending my spreadsheet via "speedyshare.com". I hope this is acceptable.
In answer to your question I hoping to use a formula in column F which will overcome this problem by issuing a new draw and linking to the start of the next round. So in effect Column C is null and void and is replaced by Column F. The Formula used in Column D is merely used as an aid in finding the duplicates.
I would value and appreciate your help as I have working on this for quite some time. I hope this is clearer than what I previously sent.
Thank you.

whatsit · Answer

http://speedy.sh/r74gx/duplicate-problem.xls

Hello Trowa
Thank you for your response. I was not aware of the link but hopefully this link (above) is what you need. I have made further explanation on the worksheet and I suggest you ignore the previous columns and figures that I gave you and. The figures on the worksheet are the actual ones.
If you require anything else I will do my best to provide it.

whatsit · Answer

Hello Trowa
Thank you for letting me know. I appreciate your input and look forward to hearing from you next week. All the Best.

whatsit · Answer

Hello Trowa
Thank you for responding. I am trying to achieve a formula which will require no manual input to produce Column "N" which will then automatically link to the start of the next Round. The only manual input in the whole workbook should be entering the Team Names and their score. If this is not possible then your suggestion is acceptable. But I am concerned it may create a Circular Reference?
The proposed method would need to take into account any situation where potential duplicates exist and also in any position in column "J". What happens when cells "J 22 and J 23"show up a duplication ? In later Rounds It would also need to look at previous Rounds ( say 5 thru 1) and highlight that there is a potential conflict. Does your suggestion make column "J" redundant?
It might be useful to you if I sent you the whole workbook so that you can see the whole picture. I will send it if you wish.
Thank you for your help.

TrowaD · Answer

Hi Whatsit,

In the following scenario column J is indeed redundant.

Try this code:
Sub RunMe()
Dim lRow, lCol, x, rValue As Integer

lRow = Range("A3").End(xlDown).Row
lCol = Range("A4").End(xlToRight).Column

For Each cell In Range(Cells(4, lCol), Cells(lRow, lCol))
    x = 0
    Do
        x = x + 1
        If cell.Value = cell.Offset(0, -x).Value Then
            rValue = InputBox("Team " & Cells(cell.Row, "A") & " already played against Team " & cell.Value & " in " & Cells(2, lCol - x) & ". Please enter replacement Team number:", "Enter replacement Team")
            cell.Value = rValue
        End If
    Loop Until lCol - x = 2
Next cell

End Sub

The code will loop through the last entered round and will find any duplicates in any of the previous rounds.

- Entering a 4 in cell D4 and then run the code, the following message will pop up:
Team 5 already played Team 4 in ROUND 2. Please enter replacement Team number:
- Entering a 6 in cell D4 and then run the code, the following message will pop up:
Team 5 already played Team 4 in ROUND 1. Please enter replacement Team number:

Does this approach your vision of a solved query?

In case you don't know what to do with codes, consider this piece of information:
How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens.

Best regards,
Trowa

whatsit · Answer

Hello Trowa
Thank you for this. I will try it and get back to you with news of success or otherwise. I am very grateful for your help

whatsit · Answer

Hello Trowa
I tried your suggested code and it worked on the spreadsheet that I sent to you. But when I applied it to my workbook it kept coming up with "Run-Time error 13"after the last duplicate was changed . Column "A" in the spreadsheet that I sent you is the equivalent Column "L" in my workbook. I tried changing the "A" in your code to "L" but the same message came. Columns "B and C" in the spreadsheet are actually Vlookup Formulae in the workbook . Is this anything to do with it?
Hope you can help. Thank you.

whatsit · Answer

Hello Trowa
Sorry I forgot to tell you that in addition to pairs changing the related score for that pair also needs to move with it ( because of "King of the Hill")! Is there any way of linking this?

TrowaD · Answer

Hi Whatsit,

@Oct29
Simply changing A to L won't work since it is used to find the last round entered, which is to the right of column A but to the left of column L.
Formula's are being bypassed because the cell references in the code have ".Value" behind them, so the code won't look at the formula but at the value they produce.

@Oct30
You lost me here. Doesn't the team order change after the round is played?

Could you re-upload your sample file with the same format as your actual file. If there is an empty column in your sample file, which is used in your actual file it could mess up the result or create errors.

Also provide an example to clarify your Oct30 post.

Best regards,
Trowa

whatsit · Answer

http://speedy.sh/yfdxZ/king-of-the-Hill-scoring-4-BOARDS.xlsx
Hello Trowa
I think it more helpful to you if I sent my workbook as it will give you the overall picture.
1. I am trying to make Note 7 on the "read me" sheet redundant.
2. Please go to "Pair Names 2". Column "U" cells 11 and 12 highlight this as a duplicate (to be avoided).
3. If cell "O 11" is changed with say cell "O 13" then you will see the score in Column "I" relating to cell "O 11" also needs to move with it . This is because the essential element of the game (program) is based upon "King of the Hill" (highest score). The Pair (Team) numbers follow this but their position in the ranking is determined by the score they achieve in each round . 
4. However Column "I" comes from "Round 2" Column "BP". So it is this latter sheet that needs attention (I think!) based upon the knowledge gained by "Pair Names " Column "U".
5. I hope that is clearer for you. Please feel free to comment on the structure if you think it can be improved. Thank you for your continuing help . It is really appreciated.

TrowaD · Answer

Hi Whatsit,

I'm kind of losing sight of what needs to be done.

So let us start with the code below and go from there.

Issue:
On sheet "PAIR NAMES 2" you want to enter the next opponent for round 3, with the condition that round 3 opponents can't be the same as the previous round.

What to do:
- Put the code below in a module.
- Go to sheet "PAIR NAMES 2". (No fixed sheet is used so you can use the code on the other PAIR NAMES sheets as well.)
- Run the code.
- An input box will appear asking you to enter the column letter corresponding to the round number you wish to pair up. (So you can use the code on any round you wish to pair up.) Type an O, since round 3 is located in column O, and hit enter.
- Now check the result and test the code under different scenario's.

Let me know which conditions haven't been accounted for and if you found any scenario's which are handled incorrectly by the code..

Here is the code:
Sub RunMe()
Dim x, Rank As Integer
Dim MyCol As String

MyCol = InputBox("Enter column letter related to the round you wish to pair up:")
x = 3
Rank = 2

Do
    Cells(x, MyCol) = Range("L2").Offset(Int(Rank), 0)
    Rank = Rank - 1
    Cells(x + 1, MyCol) = Range("L2").Offset(Int(Rank), 0)
    Rank = Rank + 3
    x = x + 2
Loop Until x = 23

For x = 3 To 21 Step 2
    If Cells(x, MyCol) = Cells(x, MyCol).Offset(0, -1) Then
        Range(Cells(x, MyCol), Cells(x + 1, MyCol)).Cut
        Cells(x + 4, MyCol).Insert
    End If
Next x

End Sub

Best regards,
Trowa

whatsit · Answer

Hello Trowa
Thank you for this code. I found it an easy thing to operate but 2 problems arose.
1. On "pair names 2" I ran the code and it changed "Column O" but that column is linked to the start of "Round 3" in "column D". The link did not update "column D" as I think it seemed to assume the link had a "$" sign against the cell number. I hope you can understand what I am trying to say !!
2. When I ran the code in "pair names 3" in column "P" it shows Pairs 3 and 4, 9 and 10, and 17 and 18 playing again in "Round 4" but they have already played against each other in "Round 1". This has to be avoided.
I hope that these 2 points can be solved as then you have given me a solution which I am very grateful for.
Thank you.

whatsit · Answer

Hello Trowa
Thank you for your response. 
1. Before I ran the code I linked Pair names 2 column "O" (which is the revised draw for round 3 after I run the code) with column "D" in Round 3(which is the start of Round3). But after I ran the code In Pair names 2 column "O" it did not update Column "D" in Round 3 To achieve the revised draw.
2. Also It did not recognise that teams had met in previous rounds and should not therefore meet again. It only looked at the immediate preceding round hence my comment 2 in my previous message. 
I hope that I am not frustrating you too much with my insufficient explanation of what I have found to be a tricky problem .I look forward to your further advice.

whatsit · Answer

Hello Trowa
further to my previous message I have ascertained the following:- 
Further investigation revealed that when I run the code on the "pair names" sheet the link to column "D" (which is the start of the next Round) does not work. BUT if after I run the code and then switch back in "Pair Names" to the original VLookup formula in that particular column then the link to Column "D" works. Thus the "duplication" is avoided except that "Pair Names" sheet still l shows the Duplication!!
Hope you can understand this.

whatsit · Answer

Hello Trowa
Thank you for your response. I apologise for confusing you. Yes you are correct that Column D (Round 3) is linked to BO4 ( Round 2). However the information contained in Column D is also contained in 'Pair names 2'(cells L3 and N3, cells L5 and N5 etc etc). So instead of going from Column BO I thought a more direct and (better ?) route was if column D 4 was linked to cell L 3 in 'Pair names  2' and D5 was linked to N 3 in 'Pair names 2' and so on. So in order to link the start of round 3 to the revised draw (after running the code in Column O) I linked Column D (Round 3) to Column O(Round 2) before I ran the code. I then ran the code in Column O and column D (Round 3 ) was not updated until I reversed Column O to the VLookup formula that it contained before I ran the code. Column O then showed the duplicate problem that it started out with but Column D showed the revised draw correctly .
 Hence my comments in Point 1 in my previous message . 
When I ran the code without changing any link the revised draw was not reflected in Column D of round 3.
I hope my explanation is clear .
I am sorry to put you to all this trouble.

whatsit · Answer

Hello Trowa
I made a mistake in the message that I just sent. For "N 3" and "N 5" please read " O 3" and " O 5".
Apologies

whatsit · Answer

Hello Trowa
Thank you for your response . First of all let me answer the last part.
1. N/S= North South
    E/W= East West
    Teams sit around a table in either the N/S or E/W axis and play against each other. During the course of the game they may play in either the N/S or E/W positions depending on their score in the previous Round. The higher scorer in the draw sits N/S.
2. DATUM= the average score for that particular Board when played by all the teams . It is a moving feast depending upon the number of tables in play and each team score depends on what they achieve against the "DATUM" score. See cell C2 in Round 1.
3. IMP score = International Match Points. This is a predetermined scale.
4. VP score= Victory Points which are achieved by converting IMP score on a predetermined scale.
5. AB,AC,BD etc are dummy team (pair) names that I used to test the program links( on the same sheet Column F "Pair Names). When the program is up and running (with your help) proper names will be used.
Now the other points:-
Round 1. Before the round starts Teams are allocated a number. Team 1 plays Team 2, Team 3 plays Team 4 etc. This is reflected in Column D Round 1. Scores are manually input and results calculated (Column AX) and resorted (Column BG) to achieve the draw for the next Round (Column BF). But "duplicates" may occur and have to be avoided. That is why Column N in "Pair Names 1" exists (to point this out in Column U was my original intention). Column L in "Pair Names 1" is the draw for the next Round 2 (from Column BF in Round 1 based upon score achieved). You will see that cell L3 in "Pair Names 1" is the same as N4 and cell L4 is the same as N3 and so on throughout the Rows. So the start of Round 2 (Column D) can be achieved from L3 and N3,L5 and N5 in "Pair Names 1"At the moment the information comes from the Round 1 sheet rather than "Pair Names 1".
So, if the code is run in "Pair Names 1" Column N it should reflect any changes in Column D in Round 2. Column D cell 4 is N/S playing Column D cell D5 (Team 5 against team 4). You will this in "Pair Names 1" cell 3 (5) and cell N3 (4).
Round 2and 3 etc onwards are mirror images of what should happen in those rounds.
All a user of the program should do is input the scores Round  by Round manually and input the Team Names in "Pair Names 1". Everything else should be automatic .It is - except for this "Duplicate" problem.
At the moment we run an event like this but it is all scored manually.
If you require further clarification please let me know. I will be happy to provide it.

TrowaD · Answer

Hi Whatsit,

I think I got it now :).

For now I want you to test the code below.
I tested it on column O on sheet PAIR NAMES 2 and now I am wondering if the result is OK.

The code will loop through the column to be entered (in this case you type O for round 3). It will enter the highest pair, then it will check if that pair is already present in both row and column. If it is, the pair entry is deleted and the second highest pair is entered etc..

The problem area is (are?) rows 11:14.
The result should look like:







Which is statistically more correct then:






Are you satisfied with the result the code provides?

Here is the code:
Sub RunMeV2()
Dim x, y, MyColNr As Integer
Dim MyCol As String

MyCol = InputBox("Enter column letter related to the round you wish to pair up:")
MyColNr = Right(Cells(1, MyCol), 1) + 12

For Each cell In Range(Cells(3, MyCol), Cells(22, MyCol))
    x = 3
    
NextRank:
    Cells(cell.Row, MyColNr) = Cells(x, "L")
    y = 0
    
    Do
        y = y + 1
        If Cells(cell.Row, MyColNr) = Cells(cell.Row, MyColNr - y) Then
            Cells(cell.Row, MyColNr) = vbNullString
        End If
    Loop Until MyColNr - y = 12
    
    y = 1
    If cell.Row > 4 Then
        Do
            If Cells(cell.Row, MyColNr) = Cells(cell.Row - y, MyColNr) Then
                Cells(cell.Row, MyColNr) = vbNullString
            End If
            y = y + 1
        Loop Until cell.Row - y = 2
    End If
    
    If Cells(cell.Row, MyColNr) = vbNullString Then
        x = x + 1
        GoTo NextRank
    End If
    
Next cell
    
End Sub

Best regards,
Trowa
 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

whatsit · Answer

Hello Trowa
Thank you. I will test it and get back to you soon.

whatsit · Answer

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.

whatsit · Answer

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.

TrowaD · Answer

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.

whatsit · Answer

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.

TrowaD · Answer

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

whatsit · Answer

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.

TrowaD · Answer

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

whatsit · Answer

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.

freebrushes · Answer

Thanks very much!

TrowaD · Answer

Thanks guys, your responses make it all worth while.

I'll assist where I can!

Best regards,
Trowa

Replacing Duplicates

30 responses

Similar discussions

Subscribe To Our Newsletter!