Wrox Programmer Forums Combination of data
 |
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

December 3rd, 2008, 10:09 PM
 Nanis76 Registered User Join Date: Dec 2008 Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts
Combination of data

Hi i want to develop test data sheet using macros in the first sheet i have data like this

column1 column2 column3 ........ column8
A Blue India 1
B yellow Australia 2
C Red WI 3
Green
Red

In the first i have a button when i click on the button the in second sheet data should appear with all possible combinations like this 3*5*3*3 =135 combinations

Column1 Column2 Column3 ........ Column8
A Blue India 1
A Blue India 2
. ... ... 3
A Blue India
A

Can any body help me doing this

msuneel

January 2nd, 2009, 11:06 PM
 ExcelExpertUK Registered User Join Date: Jan 2009 Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts
Generating Combinations - using an Excel VBA Macro

Suneel,

You can break down your task into several stages as follows :

1. Create three arrays, one to hold the list of numbers , a second to hold the list of alphabetical letters, a third array to store the list of colours and a fourth array to store the list of countries.

2. Get the values from sheet 1 and place/ save them to their respective arrays.

3. Step through the arrays using nested loops to generate all possible combinations.

The code will look something like this:

Code:
```

for i=0 to UBound (ArrayLetters)for j=0 to UBound (ArrayColours)for k=1 to UBound (ArrayCountries)for L= 1 to UBound(ArrayNumbers) 'create combination and save to an array of combinationsArrayCombinations(m) = ArrayLetters(i) & " " & ArrayColours(j) & " " & ArrayCountries(k) & " " & ArrayNumbers(L) 'increment m by one to hold array index for next combinationm=m+1NextNext
Next
Next```
I hope this sets you back on track to solving your problem.

Regards,

Jake

Courtesy of

http://www.Excel-Expert.co.uk
http://www.MacroExpert.co.uk
http://www.Officesoftworks.co.uk

 Similar Threads Thread Thread Starter Forum Replies Last Post Problem in Checkbox Combination & sorting method.. akshaymehta007 Classic ASP Professional 0 December 28th, 2007 01:32 AM Graph combination Ulli Hoeness BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 October 26th, 2005 05:05 AM Combination of ASP and PHP login system Dimitris Classic ASP Professional 0 June 29th, 2005 05:17 AM how to post a key combination successfully? Greywacke VB How-To 0 October 14th, 2003 01:09 PM Chap 4--using a combination of operators mixtli BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 2 June 9th, 2003 04:55 AM