Progression Tables?

L

Looking4help

I am trying to create what has been called a progression table in excel.
For example: I have two columns of data, one starting with letters
the other with a double progression of numbers. Column one has the
letters A,B...J. Column two has the numbers 1,000, 2,000, 4,000,
8,000....512,000. So the letter A corresponds to the number 1,000,
letter B corresponds to 2,000 and so on with J corresponding to
512,000. Now here is the challenge. I want to create a table that
tells me which combination of letters will add up to a particular
value. For example if I wanted a total of 7,000, I would add A, B, and
C together. I realize that not all desired numbers can be created
exactly by adding up the letters but they will all be within 1,000 of
my desired total. I am told that with these variables there are over
1,000 non-repeating numbers that can be created. I have looked at data
tables and pivot tables but am not sure which if either of these
functions are capable making this table. Does anyone have any ideas?

Thank you,

DB
 
T

Tom Ogilvy

I want to create a table that
tells me which combination of letters will add up to a particular
value.
Click to expand...

What are your rules. Can letters be repeated within a single solution

ex: A+A+A+A+A+A+A = 7000

is A + B the same aswer as B + A or are those two separate answers.

You have 10 variables. If order isn't significant, you don't use a single
letter more than once in a single solution, then you can have a maximum of
1023 unique letter combinations.

Number Number of
of letters unique
in solution solutions
1 10
2 45
3 120
4 210
5 252
6 210
7 120
8 45
9 10
10 1
------
Total: 1023


Each solution won't represent a unique sum, so I doubt there are more than
1000 unique numbers represented.
 
K

Kris

If your table is in "sheet1", this will populate a
progression table to "sheet2".... if I understand your
problem that is. If not, then this should at least give
you some idea's with where to go with this next.

Your range is 1000 - 1023000 since 1000 + 2000 + 4000
+.... 512000 = 1023000

It seems logical to loop backwards and count up your
numbers until you hit the number your looking for or
until you go over. There may still be a possible answer
even if you go over, but this was just a quick and dirty
solution, :p

Oh, sorry for the word wrapping, if a line begins in
column 1, then just move it to the previous line.

----------------------------------------------------------
Sub Macro1()

Dim number_answer As Long
Dim sum_total As Long
Dim sum_compare As Long
Dim letter_answer As String
Dim row_num As Integer
Dim output_row As Long

For number_answer = 1023000 To 1000 Step -1000
sum_compare = 0
sum_total = 0
letter_answer = ""
For row_num = 10 To 1 Step -1
Select Case Worksheets("sheet1").Cells(row_num,
2).Value
Case Is = number_answer
output_row = output_row + 1
Worksheets("sheet2").Cells(output_row,
1).Value = number_answer
Worksheets("sheet2").Cells(output_row,
2).Value = Worksheets("sheet1").Cells(row_num, 1).Value
Exit For
Case Is < number_answer
sum_compare = sum_total + Worksheets
("sheet1").Cells(row_num, 2).Value
Select Case sum_compare
Case Is = number_answer
output_row = output_row + 1
Worksheets("sheet2").Cells
(output_row, 1).Value = number_answer
Worksheets("sheet2").Cells
(output_row, 2).Value = letter_answer & Worksheets
("sheet1").Cells(row_num, 1).Value
Exit For
Case Is < number_answer
letter_answer = letter_answer &
Worksheets("sheet1").Cells(row_num, 1).Value & " + "
sum_total = sum_compare
Case Is > number_answer
End Select
Case Is > number_answer
End Select
Next row_num
Next number_answer
End Sub
 
L

Looking4help

Kris:

I think you understand my problem exactly, however being a beginner,
am at a complete loss at how to utilize the solution you provided. I
this something that can be emailed to me in an excel spreadsheet?
Email [email protected].

Thanks for the solution.

Regards,

D
 
Top