Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Generating Possible Combinations and Assigning ID Number

I'm not even sure if this is possible, but if anyone has advice I would
greatly appreciate it.

I'm transcribing information from my study into an Excel Spreadsheet. In my
study, "mental health disorder" is part of my baseline demographics. When
entering the data into the spreadsheet, I would like to assign a number to
each "mental health disorder". However, many patients have various
combinations of these disorders, and I would like to assign one number for
each possible combination, to simplify the data entering process.

From the information I have collected, I was able to categorize the
disorders into 9 categories. Would it be possible to have Excel generate a
list of all possible combinations of these disorders, and then assign a
unique number to each combination?

These are the 9 categories I'm working with:
Anxiety Disorder
Bipolar Disorder
Cognitive Disorder
Depressive Disorder
Mood Disorder
Panic Disorder
Personality Disorder
PTSD
Schizophrenia

Even if this is possible, I'm not sure if it's the most efficient process.
I know I won't have a ridiculous amount of combinations, but having Excel
create the list would save me from manually determining which combinations
are currently present in my study.

Any feed back would be greatly appreciated. Thanks!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 181
Default Generating Possible Combinations and Assigning ID Number

I think you are right that this is not the most efficient way. I don't know
whether a certain number of the symptoms has to be present. The number of
combinations, I think , is in excess of 300 million but how would you benefit
from a program that tells you that.

I may be totally wrong of course but the way I see it is that you have a
patient that can display up to nine of nine symptoms as you have listed.

If you create a list of your symptoms 1-9 as described.

Then columns as follows.

A1=Patient name, B1=symptom1, C1=symptom3, etc , etc

Using a lookup table you can indicate in each column what symptoms they are
displaying.

In your demographic study you can introduce other columns and thereby break
down groups as required.

Many assumptions here, so ask again if I can rethink this.




--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Joe Kluck" wrote:

I'm not even sure if this is possible, but if anyone has advice I would
greatly appreciate it.

I'm transcribing information from my study into an Excel Spreadsheet. In my
study, "mental health disorder" is part of my baseline demographics. When
entering the data into the spreadsheet, I would like to assign a number to
each "mental health disorder". However, many patients have various
combinations of these disorders, and I would like to assign one number for
each possible combination, to simplify the data entering process.

From the information I have collected, I was able to categorize the
disorders into 9 categories. Would it be possible to have Excel generate a
list of all possible combinations of these disorders, and then assign a
unique number to each combination?

These are the 9 categories I'm working with:
Anxiety Disorder
Bipolar Disorder
Cognitive Disorder
Depressive Disorder
Mood Disorder
Panic Disorder
Personality Disorder
PTSD
Schizophrenia

Even if this is possible, I'm not sure if it's the most efficient process.
I know I won't have a ridiculous amount of combinations, but having Excel
create the list would save me from manually determining which combinations
are currently present in my study.

Any feed back would be greatly appreciated. Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Generating Possible Combinations and Assigning ID Number

First run the small macro TestThis:

Function ListSubsets(Items As Variant) As String
Dim CodeVector() As Integer
Dim i As Integer
Dim lower As Integer, upper As Integer
Dim SubList As String
Dim NewSub As String
Dim done As Boolean
Dim OddStep As Boolean
SubList = ""
OddStep = True
lower = LBound(Items)
upper = UBound(Items)
Dim kk As Long
kk = 1
ReDim CodeVector(lower To upper) 'it starts all 0
Do Until done
'Add a new subset according to current contents
'of CodeVector

NewSub = ""
For i = lower To upper
If CodeVector(i) = 1 Then
If NewSub = "" Then
NewSub = Items(i)
Else
NewSub = NewSub & ", " & Items(i)
End If
End If
Next i
If NewSub = "" Then NewSub = "{}" 'empty set
Cells(kk, "B").Value = NewSub
kk = kk + 1
'now update code vector
If OddStep Then
'just flip first bit
CodeVector(lower) = 1 - CodeVector(lower)
Else
'first locate first 1
i = lower
Do While CodeVector(i) < 1
i = i + 1
Loop
'done if i = upper:
If i = upper Then
done = True
Else
'if not done then flip the *next* bit:
i = i + 1
CodeVector(i) = 1 - CodeVector(i)
End If
End If
OddStep = Not OddStep 'toggles between even and odd steps
Loop
ListSubsets = SubList
End Function

Sub TestThis()
Dim B(1 To 9) As Variant
Dim f As String
B(1) = "Anxiety Disorder"
B(2) = "Bipolar Disorder"
B(3) = "Cognitive Disorder"
B(4) = "Depressive Disorder"
B(5) = "Mood Disorder"
B(6) = "Panic Disorder"
B(7) = "Personality Disorder"
B(8) = "PTSD"
B(9) = "Schizophrenia"

f = ListSubsets(B)

End Sub


Then in A2 thru A512 enter: =row()

This should display all 512 combinations. the first four records look like:

{}
2 Anxiety Disorder
3 Anxiety Disorder, Bipolar Disorder
4 Bipolar Disorder
5 Bipolar Disorder, Cognitive Disorder
etc.

This is an adaptaion of code posted by John Coleman:

http://www.microsoft.com/communities...2-b6a19c6fc282
--
Gary''s Student - gsnu201001


"Joe Kluck" wrote:

I'm not even sure if this is possible, but if anyone has advice I would
greatly appreciate it.

I'm transcribing information from my study into an Excel Spreadsheet. In my
study, "mental health disorder" is part of my baseline demographics. When
entering the data into the spreadsheet, I would like to assign a number to
each "mental health disorder". However, many patients have various
combinations of these disorders, and I would like to assign one number for
each possible combination, to simplify the data entering process.

From the information I have collected, I was able to categorize the
disorders into 9 categories. Would it be possible to have Excel generate a
list of all possible combinations of these disorders, and then assign a
unique number to each combination?

These are the 9 categories I'm working with:
Anxiety Disorder
Bipolar Disorder
Cognitive Disorder
Depressive Disorder
Mood Disorder
Panic Disorder
Personality Disorder
PTSD
Schizophrenia

Even if this is possible, I'm not sure if it's the most efficient process.
I know I won't have a ridiculous amount of combinations, but having Excel
create the list would save me from manually determining which combinations
are currently present in my study.

Any feed back would be greatly appreciated. Thanks!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
number combinations. The Deacon Excel Worksheet Functions 2 March 20th 09 08:24 AM
All Possible Number Combinations Kate Excel Discussion (Misc queries) 4 September 30th 08 07:21 PM
Generating (in a random order)each number once from a given number Neil Goldwasser Excel Worksheet Functions 2 December 3rd 05 12:27 AM
Generating excel combinations mark4006 Excel Discussion (Misc queries) 2 March 6th 05 05:40 PM
Function generating all possible combinations of set of numbers Lucia Excel Worksheet Functions 1 February 7th 05 11:41 PM


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"