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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Jan 20, 2015 at 11:14 AM
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.

30 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Oct 20, 2014 at 11:19 AM
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
0
whatsit
Posts
5
Registration date
Sunday October 19, 2014
Status
Member
Last seen
February 15, 2016

Oct 21, 2014 at 12:18 AM
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Oct 21, 2014 at 10:26 AM
Hi Whatsit,

It is definitely helpful to look at your file, but you forgot (didn't know) to post back the download link provided by speedyshare after uploading.

For some reason I still can't figure what you are trying to achieve.

Here is where I'm at:
- Column A is used for team number; so you participate in multiple team setups (you play in more then 8 different teams?).
- Column B = round 1, column C = round 2 and there will be 7 rounds, so up to column H are reserved for rounds, yet column D is used to show if there are duplicates. Columns E and F also have different functions.

- In your latest post you said "hoping to use a formula in column F which will overcome this problem". Is the problem you are referring to that there are no duplicates allowed. Does this mean you want a popup message, letting you know that you entered a duplicate entry for that row? But you say you want a formula for column F.

Hopefully you can find yourself in my confusion. Can you show in your file the situation how it is now and how you want it to be.

Best regards,
Trowa
0
whatsit
Posts
5
Registration date
Sunday October 19, 2014
Status
Member
Last seen
February 15, 2016

Oct 22, 2014 at 12:13 AM
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Oct 23, 2014 at 11:27 AM
Hi Whatsit,

Just wanted to let you know that you used the right link.

Since I'm out of time for today, I will get back to you next week.

Best regards,
Trowa
0
Hello Trowa
Thank you for letting me know. I appreciate your input and look forward to hearing from you next week. All the Best.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Oct 27, 2014 at 01:03 PM
Hi Whatsit,

Ok, I now understand what you are trying to achieve. Now we need to figure out how to solve it.

The easiest way would be to loop through the last round entered (round 2 in your sample), find the duplicates and prompting you which team to replace it with.
So you will be prompted: "Team 9 already played Team 10. Which Team should take the place of Team 10:"
In your example this question would be asked 4 times with different Team numbers.

When you provide the info, which determines the team rankings, it is also possible to replace the duplicates with the first available team.
So Team 9 already played Team 10. The first available Team would be 17.
Team 10 would then play against Team 18.
Team 17 would play against Team 9.
Team 18 would play against Team 10.

Could that be a desired result or did you have something else in mind.

Let me know which route to take.

Best regards,
Trowa
0

Didn't find the answer you are looking for?

Ask a question
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Oct 28, 2014 at 11:38 AM
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
0
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
0
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.
0
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?
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Oct 30, 2014 at 12:30 PM
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
0
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Nov 4, 2014 at 11:50 AM
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
0
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Nov 6, 2014 at 11:32 AM
Hi Whatsit,

1. Sorry I don't understand :(.
Instead of formula, we now use code to enter sheet "PAIR NAMES 2" column O. This shouldn't mess with any formula pulling their info from column O.
You say sheet "ROUND 3" column D isn't updated, but column D isn't in any way linked to sheet "PAIR NAMES 2" column O, right?
If you want to link it type "=" in destination cell and pick your source cell from column O.

2. I have to say Whatsit, I find your query quite challenging. Which is good, but it might take some time before I get back to you. At least I understand the problem :).

Do comment on point 1.

Best regards,
Trowa
0
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.
0
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Nov 10, 2014 at 11:38 AM
Hi Whatsit,

Point 2 is clear to me.

Point 1 is causing confusion:
In the file you sent me, column D of sheet ROUND 3 contains the formula:
='ROUND 2'!BO4
Which means it is linked to the sheet ROUND 2.
To link it with PAIR NAMES 2 I entered the following formula:
='PAIR NAMES 2'!O3

After running the code, column O of sheet PAIRING NAMES 2 is filled and column D of sheet ROUND 3 is updated accordingly.

Hopefully you can see why I can't follow you ;).

Best regards,
Trowa
0
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.
0
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
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Nov 11, 2014 at 12:17 PM
Hi Whatsit,

Apologies accepted, but the confusion still exists!

1.
quote
However the information contained in Column D is also contained in 'Pair names 2'(cells L3 and N3, cells L5 and N5 etc etc).
unquote

The information in column D is the same as column L. What has column N (or O, as you corrected in last post) to do with this. Two different numbers can never be the same as one.
Round 3, D4 = 15
Pair names 2, L3 = 15
Pair names 2, N3 = 8 (or O3 = 2)
And you say the info in D4 is also contained in L3 and N3 (or O3)?

2.
quote
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.
unquote

Column D has the same info as column L, but now you bring column N into the mix as well.

Column D = Team ranking after 2 rounds
Column L = Team ranking after 2 rounds
Column N = Team matchup for round 2

3.
quote
I linked Column D (Round 3) to Column O(Round 2) before I ran the code.
unquote

Column D = Team ranking
Column O = Board score

How can you link two different values?

4.
quote
I then ran the code in Column O and column D (Round 3 ) was not updated
unquote

You link sheet Round 3 with Round 2, run a code on sheet Pair names 2 and expect to see a change in sheet Round 2 or 3?


As this is not confusing enough, you say that when you use your "old" formula every link is working properly again.
This would mean that I am completely misinterpreting you.

Try to look at your file as a new user instead of the creator, who knows all the ins and outs.
For instance, I have no idea what the following means:
- board score N/S
- E/W
- DATUM
- Imp score
- VP score
- or why AB stands next to pair 1, AC next to pair 2 and then BD next to pair 3.

Best regards,
Trowa
0
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Nov 18, 2014 at 11:36 AM
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.
0
Hello Trowa
Thank you. I will test it and get back to you soon.
0