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 a column with a Macro or Formula

0

Hi Guys and Girls,

I am having a problem sorting data and I'm wasting hours looking for a solution which, to me, should be a simple task to perform. I need a macro or formula to sort column C1:C? (variable). Each cell has one character only. Imagine a game of Scrabble, every time a letter is used it is deleted. I have used =IF(C1<1,"0","a"), which is no better than =IF(C1<1,"","a"). I suspect that when I want to sort the list from time to time, Excel sees the formula and tries to sort it amongst the letters I do want sorted.

What I would ideally like is for the C column to keep removing the blank/zero cells so I always have a contigous column of letters. Alternatively, a formula in D1 could cycle down the C column and when it finds a letter it takes it and D2 continues to loop to the next instance and so on until the last C cell is found.

Please note that I have Goole'd this question several ways and many go down the F5/ goto special route. Simple, but lacking cigars!

I hope someone out there can help me. Either way, thanks for reading this'

Answer
Discuss

Discussion

Please describe the workflow. Say C1:C100 contain 100 characters, implying there must be duplicates(?). How does the character in C15 get deleted? Now there is another column, day D, in which all undeleted characters are listed. So, initially D1:D100 = C1:C100. But then C15 gets deleted. You then want D1:D99 to show the remaining characters, right? The sequence of characters stays the same but the deleted ones are removed in column D, right?
Variatus (rep: 4889) Jun 10, '19 at 1:30 am
Hi. Thanks for responding. You're right, letters are repeated in the C column. The D column is only needed if I cannot sort the C Column. I don't know how Scrabble distributes letters, but my spreadsheets has C1:C7 = A, C8:C10 = B, C11:C12 = C and so on. So in my final analysis the C column will end up 7 characters randomly distributed in C1:C100. I hoped that a sort would place those 7 characters in C1:C7. In honesty, once 93 letters are used up I can see what's left. I just thought it would be nicer if my spreadsheet displayed them automatically for me to see. I hope that makes it clearer for you. It might help if I could attach a 100kb file, but I'm new to forums and I can't see a way to do it???
MrEMann (rep: 20) Jun 10, '19 at 5:36 am
Press the Edit button under your post to return to posting mode. Under the panel containing your question you will see a button AddFiles to the post (or something like that). After clicking that button you can browse for a file from your system and attach it. Please bear in mind that only Excel workbooks are permitted.
Variatus (rep: 4889) Jun 10, '19 at 7:16 am
Add to Discussion

Answers

0
Selected Answer

I made lots of changes to your workbook, in fact more than I should have. I made them while trying to understand how your workbook works. You may undo them while trying to figure out how my solution works :-) but some of them are necessary for the latter.

  1. Range WHITE!N4:NB18 named as "ScoreSheet"
    Look for the Name Manager on the Ribbon's Formulas tab.
  2. Ranges WHITE!AD4:AE42 and WHITE!F12:I29 formatted to vertical Center.
    Home > Format > Cells > Alignment > Vertical
  3. WHITE!AK4:AK41 assigned values 7,2,3,4,11,3,2,6,7,1,1,4,2,6,6,2,1,6,6,8,3,1,2,1,2,1,2
    This will cause cells in AE to turn red, a condition rectified in the next step.
    It's simply unacceptable to have essential program parameters introduced as part of a formula!  You want them out of sight. In column AK they are both safe, accessible and hidden. Note that I would hide them on the DATAW sheet.
  4. Formula for WHITE!AE4 
    =$AK4-COUNTIF(ScoreSheet,$AD4)
    Note that I disagree, on principle, with having the letters listed in column AD because they are also listed in DATAW!B:B. There should be a single source for them in the workbook, logically in the DATW sheet.
    The formula must be copied down to AE41. Unfortunatley this is problematic because from row 19 down each line consists of 2 rows (f.i. AE19:AE20 are merged). You may prepare one such merged cell and then copy down the cell pair.
    The formula may not pick up questionmarks in the ScoreSheet range. If this problem exists you probably know how to cure it. Look at how I solved it elsewhere in addition to your own knowledge, if needed.
  5. In cell WHITE!AM2 remove the formula and replace it with =AE41.
    Apply the custom cell format  
    0 "left"
    Format Cells > Number > Custom > enter the format formula in the Type field. This enables the number in AM2 to be used for calculations while the display remains as you want it.
  6. Introduce a caption row in DATAW. This is needed in column C. The other columns just match. The caption used in column C may not occur in column B and I prefer the cell not to be blank.
  7. Assign the name "Alphabet" to range DATAW!$B$2:$B$28
    Note that you can quickly assign a name to a range by selecting it and typing the name in the Name Bar (to the left of the Formula Bar) but you need the Name Manager to modify or delete it.
  8. Assign the name "Tray" to range WHITE!$D3:J3
    I added the letter A to the Tray for testing.
  9. Change the formula in DATAW!F2 to 
    =INDEX(Tray,ROW()-1)
    and copy down to F8.
    I applied the cell format 0;0;;@ this this cell (before copying) and don't remember why - probably because you had the cell formatted as Text which doesn't permit the entry of a formula.
  10. DATAW!G2 now has the formula 
    =COUNTIF(Tray,IF($B2="?","~?",$B2))
    copied down. This change is irrelevant, however, because I incorporated the formula in the formula in column A.
  11. DATAW!A2 gets this formula: 
    =INDIRECT(ADDRESS((ROW(AK$4)+IF(ROW()>17,2*ROW()-19,ROW()-2)),COLUMN($AK$4),1,1,"WHITE"))-COUNTIF(Tray,IF($B2="?","~?",$B2))
    copied down. The formula takes the awkward merged rows from WHITE!19:42 into account but the coding is hard and therefore not flexible.
  12. And now the crowning achievement - your original question resolved - the formula in C2.
    =IFERROR(INDEX(Alphabet, MATCH(0, COUNTIF($C$1:$C1, SUBSTITUTE(Alphabet,"?","~?") & "") + IF(OFFSET(Alphabet, 0,-1),0,1), 0)), "")
    (a) Confirm it with Shift+Ctl+Enter because it's an array formula.
    Best, paste it to the Formula Bar (not the cell) and exit the Formula Bar with Shift+Ctl+Enter in place of the singular Enter you would use for normal formulas.
    (b) Copy down to C28
    Now, if you delete any of the numbers in column A the affected letter will be removed from the list in column C. There will be no blank rows in the list.

Good luck with your project!

Discuss

Discussion

Thank you so much for your extensive work. I didn't expect you to put so much time and effort into my project. Unfortunatley this in now perfect except for one thing. Only the letters in the "tray" are subtracted at A2 in DATA!W. It may be my error, but it seems to me that in item 11 "Scoresheet" is not referenced but "tray" is. Sadly I lack your brilliance, so have no idea if, where and how to modify your code. If the error is not in your item 11 code there must be something else I have done wrong.  

I hate to put you to more trouble, especially if it is my fault. I am secretly hoping you'll say, well done for spotting your deliberate error!

On the upside, you've taught a lot of things I didn't know and my workbook feels a lot more fluid in the way it flows.Thank you once again.
MrEMann (rep: 20) Jun 12, '19 at 2:09 pm
I'm afraid none of my errors are deliberate :-)  I just don't understand how the Tray affects the stock of letters. Letters used in the ScoreSheet are counted in AE4 (COUNTIF(ScoreSheet,$AD4)). The same number could be deducted from DATW!A2. But that would raise the question why to use AK4 there instead of AE4. This formula (in A2, to copy down) would do so.  =INDIRECT(ADDRESS((ROW(AE$4)+IF(ROW()>17,2*ROW()-19,ROW()-2)),COLUMN($AE$4),1,1,"WHITE"))-COUNTIF(Tray,IF($B2="?","~?",$B2))
IMHO, the management of the character stock quantities is sub-optimal. You might take this opportunity to review it. As I already pointed out, the initial quantities should be in the DATAW sheet (if that sheet is a permanent fixture). If so, all other quantities are derivatives of that original which should change by various rules. This principle isn't implemented, therefore requiring some acrobatics to fill all cells with the correct numbers.  
Variatus (rep: 4889) Jun 13, '19 at 12:05 am
Frankly, I would exert some effort to do away with the half-row at WHITE!19. A full row 19 would permit a caption explaining the purpose of the list N12:Z42. But then you would need to reduce that list from 12 rows to 11. I don't know what that list is for but sense that you would prefer it to have 13 rows and 12 is already a compromise. Anyway, the half-rows 19:42 are a serious flaw which should be designed away. The other thing that isn't clear is the use of column DATAW!$C, and with that, in fact, the purpose of the entire DATAW sheet. I recommend that you now implement whatever new things you got from this thread and then resume design and progamming keeping full control of the project as you did until now. Don't permit some unknown elements I may have introduced to wrest control from you. If my answer was useful to you please mark it as "Selected" and thereby close this thread. If you want for any of the formulas to be explained in detail please post your question in a separate, new thread.
Variatus (rep: 4889) Jun 13, '19 at 12:05 am
Add to Discussion
0

Hi. I hope this is ok. I've just sent the "white" sheet to make the file smaller. It's the "dataw" sheet that is troubling me.

Many thanks....

Discuss

Discussion

This isn't an answer. I suggest you delete it. You can amend your question or add to its "Discussion". You can also attach up to 3 files to it or replace an already attached file with another one in Edit mode.
Variatus (rep: 4889) Jun 11, '19 at 4:09 am
I'm sorry, this is not working. Possibly I havn't understood your instructions. I called the range sort and MS said they couldn't tell which row contains the label. I pressed enter to continue and go to advanced. I would expect it to give me the list A,E,E,M,T,T,Y but all I get is A,empty cell,M,T,Y. It seems there's a problem with double E and T. You mention a radio button. To my mind that should be a black and silver upside down triangle. I guess that is what MS is not finding? Have I done something wrong, or missed out something? Lastly, I have tried 7 different letters. 8 cells are found, with C2 being empty. Finally, as the cells keep their row number, this is no help to me. Using the D column works and suggests we are almost there. Thank you again for your time, but I now think Excel cannot help me.
MrEMann (rep: 20) Jun 11, '19 at 11:02 pm
I know my earlier answer couldn't satify you. I didn't understand your question prior to seeing your workbook. That's why I deleted and replaced it. Now we are using the discussion section of an answer which isn't an answer to discuss another answer which doesn't exist. Please kindly delete this "Answer" which will do away with this dialog. I hope my new solution will prove workable. If there are issues with it please add a discussion item under that Answer, not another "Answer". Thank you.
Variatus (rep: 4889) Jun 12, '19 at 3:57 am
Add to Discussion


Answer the Question

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