AllExperts > VB Script 
Search      
VB Script
Volunteer
Answers to thousands of questions
 Home · More VB Script Questions · Answer Library  · Encyclopedia ·
More VB Script Answers
Question Library

Ask a question about VB Script
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Subbu
Expertise
I can answer VB Script questions which are related to Visual Basic / VBA / QTP Scripts.

Experience
8 Years

Education/Credentials
Master of Computer Applications

Awards and Honors
Established Member from QA Forums and Brainbench certification

 
   

You are here:  Experts > Computing/Technology > Basic > VB Script > VB or excel macro: find 2-subsets of an n-set

VB Script - VB or excel macro: find 2-subsets of an n-set


Expert: Subbu - 9/15/2009

Question
I hope you’ll consider taking this on.

I would like to know if there is an excel function or how I could write an
excel macro that will enumerate the unique 2-subsets of an n-set.

Thanks very much,

Tim Dawes



Must haves:

Concretely, what I want is this.

I will have an excel spreadsheet. One column, say column A, will have
information in cells, usually n will be between 10 and 30.

I want a macro to copy the information from the cells in column A into
columns B and C, 2 items per row.

So I want 2-subsets on a 20-set (for instance) and I want only the
unique subsets (13, 14) is the same as (14, 13).

I would like instances of a given data item to appear as infrequently as
possible.

In other words if I have a 10-set, I don’t want to start

1 2

1 3

1 4

1 5

1 6

1 7

1 8

1 9

1 10

where 1 is repeated in each of the first X partitions.

I would rather have

1 2

3 4

5 6

7 8

9 10

2 3

4 5

6 7

8 9

10 1

So that the first instance of 1 is separated from the second instance by 8
partitions and

the first instance of 2 is separated from the second by 5 partitions. As
much space between instances as possible.

Answer
Tim,
Sorry for late reply (I had couple of break fixes in my current SAP application), here you go:

Dim x As Integer
Dim y As Integer
x = 10 'Customize
y = 1
z = 1
While z <= 2 'I did iterate only twice, you can increase this number as many as you want
   While x <= 30 'Customize
       Range("B" & Trim(Str(y))).Select
       ActiveCell.FormulaR1C1 = x
       x = x + 1
       If x > 30 Then x = 10
       Range("C" & Trim(Str(y))).Select
       ActiveCell.FormulaR1C1 = x
       x = x + 1
       y = y + 1
   Wend
z = z + 1
Wend
End Sub

Let me know if you have any questions.

Subbu.

Add to this Answer   Ask a Question


 
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.