Position-Dependent Combinations 2

[rocksandbroncs]

OK, I give up!! What I'm trying to do is determine all the valid combinations I can get based on the table below (Excel cells). Position matters in this case. Then I want to load them into a range of cells.


Digit 100S 10s 1S
Cnt Cnt Cnt
0 2 2 3
1 4 2 5
2 4 2 1
3 5 4 1
4 4 2 6
5 6 1 5
6 0 4 1
7 0 4 0
8 0 4 2
9 0 1 1
Cnt 3 3 4
4 ND D ND


I've got the part where there is more than one digit that is tied for highest number of occurances (the D for Dups or ND for NoDups) i.e. 3,6,7 and 8 of the tens digit has occurred the same (4), highest number of times. I can even figure out how many total combinations are going to be generated (the 4 in this case: 1x4x1) The valid combinations would be: 534, 564, 574 & 584. Any ideas?

I have a feeling this will have to be done in VBA and I'm a rookie in OOP. Any help would be greatly appreciated and thanks in advance .

 

[xlbo]

Sorry but I don't see how you get your answer from what you have posted so far. either I am being thick or you need to add some moreinformation on what exactly you are trying to achieve here, what the rules are and what you are stuck on....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[rocksandbroncs]

Sorry, Geoff, for not making myself clear and thanks for your interest in assisting me.

This table: (Please note the deletion of the CNT row - my bad)

Digit 100S 10s 1S
Cnt Cnt Cnt
0 2 2 3
1 4 2 5
2 4 2 1
3 5 4 1
4 4 2 6
5 6 1 5
6 0 4 1
7 0 4 0
8 0 4 2
9 0 1 1
4 ND D ND

is basically an analysis of a column of raw data containing random 3-digit numbers. It's updated automatically whenever a new digit is entered in a cell in the column of raw data. For instance, if I entered the number 205 in the next empty cell in the column of raw data, the table would become:

Digit 100S 10s 1S
Cnt Cnt Cnt
0 2 3 3
1 4 2 5
2 5 2 1
3 5 4 1
4 4 2 6
5 6 1 6
6 0 4 1
7 0 4 0
8 0 4 2
9 0 1 1
16 ND D ND

The total number of possible combinations is now 16 (2X4X2): 2 digits (2&3) have occurred most frequently (5 times) in the hundreds column X 4 digits (3,6,7&8) have occurred most frequently in the tens column X 2 digits (4&5) have occurred most frequently in the ones column = 16 total combinations.

Again, after entering the new number, this table is updated automatically.

What I need to be able to do, either after entering a new number or changing an existing one, is fill a range of cells with the valid possible combinations. i.e.

Any
Column
Row 1 First Valid Combo (234 in this case)
Row 2 2nd Valid Combo (235)
.
.
.
Row N Last Valid Combo (385)

Ideally, this will happen dynamically, whenever the column of raw data is changed. But if I have to have a "Generate Combos" button or something like that, that would be acceptable.

Where I'm stuck is that I don't know if it can even be done, or if it can, whether I need a button, or if it can be done dynamically, or if it needs VBA code or if there's a way to do it with Excel funtions.

Any help would be greatly appreciated and feedback of any kind is always welcome.

Thanks, Larry

Never doubt that a small, group of thoughtful, committed citizens can change the world. Indeed, it is the only thing that ever has. --- Margaret Mead

 

[SkipVought]

Why wouldn't the FIRST valid combo be 100, assuming only 3-digit numbers?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi rocksandbroncs,

It took me a while to get it, but surely there are only 8 possibilities in your example because the most frequently occurring digit in the hundreds is 5 (6 times).

Let's assume your data is in Columns A to D, Rows 1 to 12 (so 0 is in Cell A3, 2 is in Cell B3, etc). I've written better code but this should list your values in Column F ...

[blue]
Dim Ro As Long
Ro = 0

Columns(6).ClearContents

For Each C100 In Range("B3:B12")
  If C100.Value = WorkSheetFunction.Max(Range("B3:B12")) Then
    For Each C10 In Range("C3:C12")
      If C10.Value = WorkSheetFunction.Max(Range("C3:C12")) Then
        For Each C1 In Range("D3:D12")
          If C1.Value = WorkSheetFunction.Max(Range("D3:D12")) Then

            Ro = Ro + 1
            Cells(Ro, 6) = Cells(C100.Row, 1) & Cells(C10.Row, 1) & Cells(C1.Row, 1)

          End If
        Next
      End If
    Next
  End If
Next
[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[rocksandbroncs]

Huzzah Tony!!! You da man!!! A star to you for sure.

Of course, you're right about the mistake in my example. And apologies to you, Skip for giving you an erroneous 'for instance'. I sure am glad I'm gonna have a 'puter do this instead of me doing it manually!

I guess my real problem is that -as I said in my first post - although I know top-down programming, I don't know a method from an object, from a constant, ad nauseum.

This will surely get me on my way and maybe do someone else some good in the future. Again, many thanks, Tony.

Never doubt that a small, group of thoughtful, committed citizens can change the world. Indeed, it is the only thing that ever has. --- Margaret Mead