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.
- Range WHITE!N4:NB18 named as "ScoreSheet"
Look for the Name Manager on the Ribbon's Formulas tab.
- Ranges WHITE!AD4:AE42 and WHITE!F12:I29 formatted to vertical Center.
Home > Format > Cells > Alignment > Vertical
- 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.
- 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.
- 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.
- 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.
- 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.
- Assign the name "Tray" to range WHITE!$D3:J3
I added the letter A to the Tray for testing.
- 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.
- 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.
- 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.
- 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!