Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sorting columns to include cells with duplicate contents.


Thank you for your previous excellent help with sorting a column. If all cells are unique your solution is brilliant. You will see from my sample that the final result should appear automatically in the vertical column to the left of the playing area. In this instance the second E and T or missing. I can enter the missing data manually, but I feel I shouldn't have to do that.

Also, I couldn't find a way to assing the range label names to both WHITE and BLACK sheets. It seems to me that for neatness and clarity, having diffent labels for multiple sheets is bad practice?

Thank you.



Selected Answer

There are two questions here.

  1. If you want duplicate letters listed in WHITE!L4:L10 you can't use the unique list in DATAW!C:C as a source. Instead you might create a list including duplicates. That can be done with a variation of the formula there. I would start with removing COUNTIF($C$1:$C1, SUBSTITUTE(Alphabet,"?","~?") & "") + from that formula. I haven't tested but think it will work. You might place this formula in column D and refer to that column in WHITE!L4:L10.
  2. Range names, like range addresses (or sheet names) must be unique. However, you can define a name to be visible only within a specific worksheet. That would allow you to have the same name on two or more sheets. However, a range name with a scope of tab WHITE can't be accessed from any other sheet. Excel creates names with a workbook-wide scope by default. They can be accessed from anywhere in the workbook but must be unique within that scope.
    You differentiate between DATAW and DATAB by adding an identifying letter to the name (I would merge these data on one sheet!). You might do the same with ScoreSheetW and ScoreSheetB. You might then write formulas which take the root name "ScoreSheet" and add the first letter of the sheet's name to it. In this way you could use the same formulas on both sheets. I wonder if the extra volume in the formulas and the associated reduction in simplicity is worth the benefit.

Edit of 18 Jun 2019 ==============================

In the attached workbook I removed the twin lines that offended me, and I joined the two Data sheets into one. I moved all data to the new DATA tab and based all referencing on it. I created a lot of new named ranges. You may like to take a look at them in the Name Manager.

Then I ran into trouble. I don't know the game. I don't know the rules. I don't know what rules you are trying to implement. I improvised with regard to column AG:AJ and AM but couldn't figure out how the Tray is supposed to work or the range L4:L10. Of course, that is precisely what you were working on - I destroyed everything and created nothing new.

The new DATA tab is designed to be hidden in such a way that the user can't see or access it. Use the procedures ThisWorkbook.HideData and .ShowData to hide and show this sheet.

In the same location you will find the subs DisableBoardControl and EnableBoardControl. They refer to the control I established (via VBA) of what can be entered on the Boards. Most cell there are off limits to the user. Changes made to thme will be undone, mostly without notice, for now.

In the code module of the White sheet you will find the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 18 Jun 2019

    Dim Approve As Boolean

    ' less frequently used ranges are listed last
    If Not Application.Intersect(Target, Range("WhiteBoard")) Is Nothing Then
        Approve = ApproveLetter(Target)
    ElseIf Not Application.Intersect(Target, Range("WhiteTray")) Is Nothing Then
        Approve = True
    ElseIf Not Application.Intersect(Target, Range("WhiteNotes")) Is Nothing Then
        If Target.Column <> Columns("V").Column Then
            Approve = True
        End If
    ElseIf Not Application.Intersect(Target, Range("WhiteRems")) Is Nothing Then
        If Target.Column <> Columns("H").Column Then
            Approve = True
        End If
    ElseIf Not Application.Intersect(Target, Range("WhiteAnagrams")) Is Nothing Then
        Approve = True
    ElseIf Not Application.Intersect(Target, Range("WhiteName")) Is Nothing Then
        Approve = True
    ElseIf Not Application.Intersect(Target, Range("WhitePlayer")) Is Nothing Then
        Approve = True
    ElseIf Not Application.Intersect(Target, Range("StartDate")) Is Nothing Then
        Approve = True
    End If

    If Not Approve Then
        With Application
            .EnableEvents = False
            .EnableEvents = True
        End With
    End If
End Sub

In essence, this code determines where the user made a change, in the WhiteBoard range or the ranges WhiteTray, WhiteRems, WhiteAnagrams, WhiteName etc. For each of such changes there should be rules which, if broken, results in the rejection of the change. I created such a test for the entries in the WhiteBoard range.

If Not Application.Intersect(Target, Range("WhiteBoard")) Is Nothing Then
        Approve = ApproveLetter(Target)

It says, If the change occurred in the WhiteBoard range then take the value of the variable Approve from the ApproveLetter function. That function is located in the Scrabble module where it is also available to be called from the event procedure running when a change occurs on the BlackBoard. I think you will be able to read the code and determine if it does what you want. I think the change should be denied if the letter entered isn't available anymore.

Rules for changes to the other ranges would have to be stated by you before I can program them. I don't for example, know how the Tray is filled or emptied. I would like to hide the tiles in AG:AJ when the letter isn't available anymore.

The point of my work was to change your project from being one that deploys worksheet functions only to one that relies on VBA. As you have already found yourself, the problems you have been facing are easier to solve when VBA is available. My problem therefore isn't to create any of the lists you have been describing but to make them work. In order to solve your problem, please describe the list, like "a list of all letters available to a player in which each letter is listed as many times as it is available". I got that pat, in fact, but I didn't create such a list (although I made space for it on the DATA tab) because I don't see how more than the first 8 letters in that list can be displayed in L4:L10, and the information that would provide would be identical to what is already shown on the right of the Board.

It would be helpful to know if the player, in fact, "draws" tiles and places them in his Tray. Perhaps L4:L10 should be scrollable. Perhaps the player can select tiles or they are dealt to him. Perhaps ...



Thank you for your suggestion. I did not understand what you said and I got various results when I tried tinkering. I've decided to leave the sheet as was suggested previously and assume that what I am trying to do is beyond the capability of Excel. I had hoped there'd be a formula to cycle down the A column looking for numbers greater than 0 and placing the contents of A* into D1 etc.

That would require the contents of "Tray" to be taken into consideration to achieve the result I was looking for. When I had a column of all the letters (100) and replaced them with "" or "0" I found a VBA routine that highlighted all the cells that were filled. Unfortunately it did nothing else and.I wasn't able to figure out how to copy those highlited cells into D1:D7.

I have decided not to waste any more time, yours and mine.
MrEMann (rep: 20) Jun 17, '19 at 6:23 am
That's a pity. A lot of effort went into what you have created, and it looks nice. I feel that you will probably come back to your project once you rebuild your courage :-) I hope you will.
For your future adventures please note that Excel makes a difference between "", "0", "O" and 0. Under some circumstances all of them will evaluate to zero but prior to considering the circumstances only the last one is a number. The other three are strings - text, not suitable for calculations until converted.
Variatus (rep: 4889) Jun 17, '19 at 6:46 am
Sub Sort_data() '
' Stiles_sort Macro
' This one sorts the stiles.
' Keyboard Shortcut: Ctrl+z
'This line finds the last occupied row in column H
'And you can use that LR variable in all the following Range Statements.
LR = Cells(Rows.Count, "H").End(xlUp).Row Range("H2:H" & LR).Select
ActiveWorkbook.Worksheets("dataw").Sort.SortFields.Add Key:=Range("H2:I" & LR) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("dataw").Sort
    .SetRange Range("I2:I" & LR)
    .Header = xlYes
    .MatchCase = True
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
End With
End Sub

Promising, but fails @ .Apply and I don't know why?
MrEMann (rep: 20) Jun 17, '19 at 10:44 am
The Key should be a single column (a single cell is enough - usually the one which wll have the Filter arrow). The SetRange is the range to be sorted and may include more columns than the one to sort by.
I'm putting in some serious work on your project. Watch this space.
Variatus (rep: 4889) Jun 17, '19 at 8:46 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login