# Thread: Determining the items selected from a collection.

1. I recall where one could identify a group of items from a list by adding the numerical value of each selected item and providing the total as an argument. The individual items were enumerated with the value 2^n where n was unique to each item in the collection. e.g. with item numbers: 0; 1; 2; 3; and 4. If one selected items 1 and 3, the argument would be 10.

My problem is that I cannot recall the technique for extracting the individual values of n which made up the total. How do I determine whether item no 1 with a value of 2 contributed to the argument value of 10?

2. Since each position is 1 more than the sum all previous positions, you would need to start from the right. Since your total of 10 is less than 2^4 but more than 2^3, you know that the fourth position, or 8, is one of the values. 10 - 8 leaves you with 2 or 2^1 or second position.

here's a macro I made in Excel that will show the positions for numbers up to 255. The column of numbers must reside in the A column and be selected before running the macro.

Code:
```Sub FindPosition()
Dim cCell As Range
Dim cCol As Integer, iFac As Integer
Dim i As Integer, lsub As Long

For Each cCell In Selection
lsub = 0
cCol = 0
For i = 8 To 1 Step -1
If 2 ^ (i - 1) <= cCell - lsub Then
Cells(cCell.Row, cCol + 2) = i
cCol = cCol + 1
lsub = lsub + 2 ^ (i - 1)
End If
Next
Next

End Sub```
Edited to fix limit to 255 and correct the loop to go to 1 instead of 0

3. Say the total value is v.
If you want to know whether item n is part of the selection, you can use

Code:
```If v And 2 ^ n = 0 Then
' not selected
Else
' selected
End If```
In your example, 10 And 2 ^ 1 = 2, so item 1 is selected.
Also: 10 And 2 ^ 2 = 0 so item 2 is not selected.

This works because "And" in VBA performs a bitwise And.

4. [quote name='HansV' post='784450' date='14-Jul-2009 01:46']Say the total value is v.
If you want to know whether item n is part of the selection, you can use ...[/quote]
Thank you Hans that code strikes a memory bell. However on implementing it I encountered a result which I cannot explain. The attached file contains the following two UDFs.
Code:
``` Public Function HansSel(Posn As Long, Sum As Long) As Boolean
If 2 ^ Posn And Sum = 0 Then
HansSel = False
Else
HansSel = True
End If
End Function```
Code:
```	   Public Function Selected(Posn As Long, Sum As Long) As Boolean
Dim Result As Long
Result = 2 ^ Posn And Sum
If Result = 0 Then
Selected = False
Else
Selected = True
End If
End Function```
The HansSel function as called in column F returns TRUE in each case, while the Selection function as called in column B works as expected.

Any suggestions on why?

5. If 2 ^ Posn And Sum = 0 Then

is evaluated as

If 2 ^ Posn And (Sum = 0) Then

Since Sum doesn't equal 0, Sum = 0 is False or 0 and hence 2 ^ Posn And (Sum = 0) is False (or 0)

If you insert parentheses

If (2 ^ Posn And Sum) = 0 Then

the HansSel function will have the same result as Selected, where 2 ^ Posn And Sum is evaluated first.

6. [quote name='HansV' post='784502' date='14-Jul-2009 09:49']If 2 ^ Posn And Sum = 0 Then

is evaluated as

If 2 ^ Posn And (Sum = 0) Then

Since Sum doesn't equal 0, Sum = 0 is False or 0 and hence 2 ^ Posn And (Sum = 0) is False (or 0)

If you insert parentheses

If (2 ^ Posn And Sum) = 0 Then

the HansSel function will have the same result as Selected, where 2 ^ Posn And Sum is evaluated first.[/quote]

Thank you Hans

7. [quote name='mbarron' post='784438' date='13-Jul-2009 23:26']Since each position is 1 more than the sum all previous positions, you would need to start from the right. Since your total of 10 is less than 2^4 but more than 2^3, you know that the fourth position, or 8, is one of the values. 10 - 8 leaves you with 2 or 2^1 or second position.

here's a macro I made in Excel that will show the positions for numbers up to 255. The column of numbers must reside in the A column and be selected before running the macro.

Code:
```Sub FindPosition()
Dim cCell As Range
Dim cCol As Integer, iFac As Integer
Dim i As Integer, lsub As Long

For Each cCell In Selection
lsub = 0
cCol = 0
For i = 8 To 1 Step -1
If 2 ^ (i - 1) <= cCell - lsub Then
Cells(cCell.Row, cCol + 2) = i
cCol = cCol + 1
lsub = lsub + 2 ^ (i - 1)
End If
Next
Next

End Sub```
Edited to fix limit to 255 and correct the loop to go to 1 instead of 0[/quote]
Thank you Mike.

#### Posting Permissions

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