Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Summarizing Data (XL-XP)

    I am attempting to build a lookup table where the data for the table is derived from another source. The table will consists of 511 rows and 2 columns. The first column is numbered sequentially 1...511. The second comes as a result of...

    Meanwhile: Elsewhere on the sheet, I have a range as follows:
    <pre>001 Red
    002 Blue
    004 Gold
    008 Brown
    016 Pink
    032 Green
    064 Orange
    128 Tan
    256 Silver </pre>


    ...Now, back to the 2nd column of the lookup table. it is the result of the sum of the values for the colors noted above. Example: 1=Red. 2=Blue. 3=Red & Blue. (because the sum of the values assigned to red and blue = 3). Just as 9=Brown & Red. And, 52= Gold, Pink & Green.


    So, I'm looking for the best way to completely fill in the second column of the lookup table. Remember, there's 511 different color combinations. I'm not particular about the seperators (",","&", etc). It could just as well be "-". Any ideas? This was an over-simplification of the actual project. I've attached a worksheet that better shows what I'm trying to do. I'm also open to suggestions for a better method to the end result. Thanks
    Attached Files Attached Files
    - Ricky

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing Data (XL-XP)

    The VBA routine below should build the table in A1:B511 on worksheet Sheet1:

    <pre>Public Sub BuildTable()
    Dim I As Integer
    Dim strNam As String
    For I = 1 To 511
    strNam = ""
    If (I And 1) <> 0 Then strNam = strNam & ", Red"
    If (I And 2) <> 0 Then strNam = strNam & ", Blue"
    If (I And 4) <> 0 Then strNam = strNam & ", Gold"
    If (I And 8) <> 0 Then strNam = strNam & ", Brown"
    If (I And 16) <> 0 Then strNam = strNam & ", Pink"
    If (I And 32) <> 0 Then strNam = strNam & ", Green"
    If (I And 64) <> 0 Then strNam = strNam & ", Orange"
    If (I And 128) <> 0 Then strNam = strNam & ", Tan"
    If (I And 256) <> 0 Then strNam = strNam & ", Silver"
    strNam = Right(strNam, Len(strNam) - 2)
    Worksheets("Sheet1").Range("A1").Offset(I - 1, 0) = I
    Worksheets("Sheet1").Range("A1").Offset(I - 1, 1) = strNam
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summarizing Data (XL-XP)

    Thank you, just awesome!
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •