Trying to correct erroneous values being used by =Combinations array

[DonEB]

In Worksheet1 I have a small table (AK28:AZ28) of numbers which have been established via a calculated event (example: the formula in AK28 is =IF(OR(AK25="X",AK24=""),99,AK24)).

AK24 would either equal a number (1-20 or 99) or an X. The "X" represents the number has already been use, the 99 represents that the number is not available. The end results would place either a valid number 1-20 or a 99 in each cell.

AK28:AZ28 is then copied and pasted (transposed) to BE35:BE50.

BE35:BE50 is then sorted low to high and examined to remove all 99's to leave me with just valid player numbers all at the top.

BE35:BE46 is then copied to Worksheet2(A8:A19). I only go to BE46 instead of BE50 because I know 4 players have already been selected. NOTE: It is very possible that several players may not have been available which resulted in 99's or what is now intended to be cleared or emptied cells being copied and brought forward.

The problem I am having is that the Worksheet2 formula {=combinations(A8:A19,4)} used to create all 495 potential combinations using the values in A8:A19 is seeing some kind of value in those cells which appear to be blank. The combination formula is putting a ZERO in where there should be nothing or a blank. And this is not only messing everything up... but it has really stumped me.

I've created a work around that occurs subsequent to this by looping thru and clearing the ZERO's but that adds a minimum of 5 seconds to the process.

Any thoughts on how to resolve this problem would be appreciated.

Thanks,
Don

 

[pgc01]

The problem I am having is that the Worksheet2 formula {=combinations(A8:A19,4)} used to create all 495 potential combinations using the values in A8:A19 is seeing some kind of value in those cells which appear to be blank.

Hi Don

Not sure what you mean by a blank cell, do you mean an empty cell or a cell with a null string?

Please test one of those cells.

To check if a cell is empty you can use in the worksheet ISBLANK() or in vba the funciton IsEmpty().

Second question, does the combinations() function ignores just empty cells or also cells with null strings?

This is just a guess. Maybe the combinations() function ignores just empty cells but not cells with null strings?

 

[DonEB]

Hi Don

Not sure what you mean by a blank cell, do you mean an empty cell or a cell with a null string?

Please test one of those cells.

To check if a cell is empty you can use in the worksheet ISBLANK() or in vba the funciton IsEmpty().

Second question, does the combinations() function ignores just empty cells or also cells with null strings?

This is just a guess. Maybe the combinations() function ignores just empty cells but not cells with null strings?

PGC,
You gave me a couple things to think about and let me first answer you question. I tested one of the cells that is blank using the =Blank() function and the response was TRUE. But the mystery doesn't stop there.

In my program, I use =Combinations() twice. The first time was used successfully, the second time failed. There are only minor differences in how the numbers used to populate the =Combinations() function were determined.

In the first instance (which worked successfully), here is what I did.
1. From a chart (AV5:AV20) located in worksheet ALLWEEKS, numbers were determined to be available or not by using the following code within AV5 =IF(Q43="n","",L43), AV6 = =IF(Q44="n","",L44), and so on.
2. All "values" in AV5:AV20, which seems to include blanks, were copied/pasted to worksheet TEAMSELECTION (AG45:AG60). There was much more copied than that to be used for other purposes but AG45:AG60 is the key component for this discussion.
3. Now copied AG45:AG60 to A8:A23. It is A8:A23 that is used by the =Combinations() function to identify all possible combinations. I later identify those with blanks, sort them to the bottom and bring to the top all VALID combinations.
4. In this example, "blanks" are carried forward to A8:A23. A test using =isblank() results in a "FALSE" response in those cells that appear to be blank.

In the second instance (which DID NOT work successfully), here is what I did. Unfortunately, I believe I had to take this approach because I am trying to remove used numbers and bring forth only unused numbers (unlike what I was able to do in the first instance).
1. From a chart (AK28:AZ28) located in worksheet TEAMSELECTION, numbers were determined to be available or not by using the following code within AK28 =IF(OR(AK25="X",AK24=""),99,AK24), AL28 =IF(OR(AL25="X",AL24=""),99,AL24), and so on.
2. All "values" in AK28:AZ28, which includes numbers 1-20 or a 99, were copied/pasted (transposed) to worksheet TEAMSELECTION (BE35:BE50).
3. BE35:BE50 is then sorted low to high and examined to remove all 99's to leave me with just valid player numbers all at the top using the following code: For c = 35 to 50
If Cells(c,57).value = 99 then
Cells(c,57),Value = ""
End If
Next c
4. I test out a blank cell in the BE35:BE46 range using the isblank() and the response was TRUE.

This is obviously were the problem resides and I really don't understand why I am getting the results I'm getting.

Does this answer your question? Any thoughts?

Thanks,
Don

​

 

[DonEB]

I think I resolved my problem ... primarily because PCG asked a couple simple questions that forced me to re-examine what functions were being used and when.

If anyone was following my explanation in my reply above, in step 1 of the "second instance" I showed the following formula being used:

=IF(OR(AK25="X",AK24=""),99,AK24). That was ultimately followed by my need to replace all 99's with "". Well... I decided to try and eliminate one step and used this formula instead: =IF(AK25="X","",AK24). Once I did this, all other formula's worked as desired.

I'm still not sure why my previous code did not work but I appreciate anyone and everyone who may have looked at this question and gave it some thought.

Thank you,
Don
​