Re: Permutations - 8 columns

From: Sandip (sanshah01_at_yahoo.com)
Date: 03/01/05 

Date: 28 Feb 2005 23:23:55 -0800

Hi,

Thanks Markos for your input. I was told not to put the email address
which I regularly use since spamsters pick the emails from here. Hence
did not read the email send. I tried both yours and Tom's VB code and
they work fine.

Appreciate your help.

Tim, I agree that the method suggested by you is the right and the best
solution, however until I get the necessary permissions to use excel
SQL driver to access the database, I will have to use the long way to
getting a list by permutation of various columns.

Thanks a lot.
Bye
Sandip.

Markos wrote:
> sanshah01@yahoo.com wrote:
> > Hi,
> >
> > I have gone through various posts in this group for the solution of
a
> > permutation I require but could not find one.
> >
> > The data I have is as follows in eight columns
> >
> > Col A Col B Col C Col D Col E ............ etc
> > Period Account Currency Code Country ....... etc
> > Nov-04 99700 USD 1234 USA
> > Dec-04 51000 2270 EUR
> > 61000 2568 IND
> > 38000
> >
> > I need to have a macro which would give me a combination of data in
> > various columns into a database format. There can be additions to
the
> > information in some of the columns for eg. incase a new account is
> > created.
> >
> > The output on another *** needs to be as follows
> >
> > for eg.
> > Col A Col B Col C Col D Col E ............ etc
> > Period Account Currency Code Country ....... etc
> > Nov-04 99700 USD 1234 USA
> > Nov-04 99700 USD 1234 EUR
> > Nov-04 99700 USD 1234 IND
> > Nov-04 99700 USD 2270 USA
> >
> > Let me provide a background on why I am following this process
since if
> > someone has a better suggestion for arrive at the end result, I am
open
> > for it.
> >
> > We have a database where all the financial information is stored.
We
> > are not allowed to extract the information directly from the
database
> > in a tabular form. However using Excel retrieve I am allowed to
extract
> > the information based on various paramaters and unique variables of
> > each parameter is being listed by me as shown in the first table.
> >
> > After having a permutation of various cols and parameters, I will
be
> > adding an amount column which would provide the information
retrieved
> > based on the unique combination of 8 columns / 8 parameters.
> >
> > Anyone's help is highly appreciated.
> >
> > Regards
> > Sandip.
> >
> Hello, Sandip.
>
> This is probably going to seem lame to all the brilliant folks out
there
> (and I tried to send this to you privately to save myself some
> embarrassment), but here it is:
>
> I would do this using a procedure that filled an array, and then
copied
> the results to your target work***. I'll restrict it to 3 columns
to
> make it a bit easier to read.
>
> First, here is the listing:
> *****************************************
>
> Option Base 1
>
> Function GetLastRow(ColumnNumber) As Long
>
> Cells(65536, ColumnNumber).Select
> Selection.End(xlUp).Select
> GetLastRow = Selection.Row
> End Function
>
> Sub CreatePermutations()
> Const OFFSET_ROW = 1
> Const NUMBER_OF_COLUMNS = 3
> Const PERIOD_COL = 1
> Const ACCOUNT_COL = 2
> Const CURRENCY_COL = 3
>
> Dim TotalPeriods As Long, iPeriod As Long
> Dim TotalAccounts As Long, iAccount As Long
> Dim TotalCurrencies As Long, iCurrency As Long
> Dim arrTemp(), arrPointer As Long
>
> TotalPeriods = GetLastRow(PERIOD_COL) - OFFSET_ROW
> TotalAccounts = GetLastRow(ACCOUNT_COL) - OFFSET_ROW
> TotalCurrencies = GetLastRow(CURRENCY_COL) - OFFSET_ROW
>
> ReDim arrTemp(TotalPeriods * TotalAccounts * TotalCurrencies,
> NUMBER_OF_COLUMNS)
> arrPointer = 1
>
> For iPeriod = 1 To TotalPeriods
> For iAccount = 1 To TotalAccounts
> For iCurrency = 1 To TotalCurrencies
> arrTemp(arrPointer, PERIOD_COL) = _
> Cells(iPeriod + OFFSET_ROW, PERIOD_COL)
> arrTemp(arrPointer, ACCOUNT_COL) = _
> Cells(iAccount + OFFSET_ROW, ACCOUNT_COL)
> arrTemp(arrPointer, CURRENCY_COL) = _
> Cells(iCurrency + OFFSET_ROW, CURRENCY_COL)
> arrPointer = arrPointer + 1
> Next
> Next iAccount
> Next iPeriod
>
> Sheets("TargetSheet").Activate
> Range(Cells(OFFSET_ROW + 1, 1), _
> Cells(UBound(arrTemp, 1) + 1, _
> NUMBER_OF_COLUMNS)).Value = arrTemp
> End Sub
>
> Okay, here's the explanation:
>
> At the top of the code module, type "Option Base 1", which tells
Excel
> to use "1" as the first item in an array instead of zero (0). This
isn't
> stricty required, but it makes the code a little cleaner later on.
Then,
> the GetLastRow function is created because we don't like to write the

> same code more than once and we are going to find out how many rows
> are in each column.
>
> Then, to make things easier to keep track of, we use names for the
> columns, although it's fine to use the column number, if you want.
>
> The number of total possible combinations (as you probably already
know)
> is obtained by multiplying the number of each of the choices in each
> column. We declare and resize (REDIM) an array variable with the
number
> of rows (first dimension) the same as the number of possible
> combinations, and the number of columns (second dimension) the same
as
> the number of columns of data.
>
> We start the arrPointer variable to point to the first record (row)
in
> the now blank array, and then create nested loops that fill each row
of
> the array with all the possible combinations. Look carefully at the
> code. I've used very descriptive names, so that you can follow what's
> happening.
>
> When the array is full, the routine shifts to your target work***,
and
> transfers its data to the proper location on the ***. Take note
that
> if you specify the total "receiving area" as I've done by using
>
> Range(Cells(OFFSET_ROW + 1, 1), Cells(UBound(arrTemp, 1) + 1, _
> NUMBER_OF_COLUMNS)).Value = arrTemp
>
> then you don't have to create another set of nested loops to put the
> data back into the work***. Specifying a single target cell for the
> upper-left-hand corner doesn't work, unfortunately, you have to do it
> the way I've shown. Also, this routine doesn't copy the column
headers,
> but I assume that you can handle that part on your own.
>
> If you have any questions, please feel free to email me. Again, I can

> already hear people laughing out there, but I have to do this type of

> chore on a very regular basis, and I know it works this way.
>
> Good Luck!
> Mark
> ______________________________
> Mark S Menikos
> mark@brothers3.us

Loading