1. 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

2. 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>

3. Re: Summarizing Data (XL-XP)

Thank you, just awesome!

Posting Permissions

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