5 digit numbers where the sum is equal to 18

MomMel,

Welcome to the Excel Forum.


To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

Welcome to the forum.

You could list the numbers 99 to 99000 (this is an Excel 2007+ exercise) down a column starting in A2 . In the B2 and copy down,

=SUM(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))

... confirmed with Ctrl+Shift+Enter

In C2 and down

=A2=18

AutoFilter col C for FALSE, clear those cells, unAutoFilter, and sort by col A:

       -A- -B- -C--
   1   Num Sum 18? 
   2    99  18 TRUE
   3   189  18 TRUE
   4   198  18 TRUE
   5   279  18 TRUE
   6   288  18 TRUE
   7   297  18 TRUE
   8   369  18 TRUE
   9   378  18 TRUE
  10   387  18 TRUE
  11   396  18 TRUE
  12   459  18 TRUE
  13   468  18 TRUE
  14   477  18 TRUE
  15   486  18 TRUE
  16   495  18 TRUE

There are 3915 numbers left in the list.

Thanks so much! This is perfect!

Actually, errata and improvements.

You can list the numbers 99 to 99000 stepping by 9: 99, 108, 117, ...

Then autofilter, and 4840 numbers survive (I was doing this in Excel 2003, so couldn't list all the numbers stepping by one).

Please don't quote whole posts.

Brute force approach:

Sub List18s()
   Dim a As Long, b As Long, c As Long, d As Long, e As Long
   Dim lngCount As Long
   Dim varOut()
   ReDim varOut(1 To 99000)
   For a = 0 To 9
      For b = 0 To 9
         For c = 0 To 9
            For d = 0 To 9
               For e = 0 To 9
                  If a + b + c + d + e = 18 Then
                     lngCount = lngCount + 1
                     varOut(lngCount) = CLng(a & b & c & d & e)
                  End If
               Next e
            Next d
         Next c
      Next b
   Next a
   ReDim Preserve varOut(1 To lngCount)
   Range("A1").Resize(lngCount).Value = Application.Transpose(varOut)
End Sub

When I figure out how to get my computer to answer questions here, then maybe I'll start doing the work again.