Re: Permutations - 8 columns

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Date: 28 Feb 2005 23:20:38 -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. 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 sheet 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 worksheet. 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 worksheet,
and
> transfers its data to the proper location on the sheet. 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 worksheet. 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

Relevant Pages

  • Re: Permutations - 8 columns
    ... Thanks Markos for your input. ... SQL driver to access the database, I will have to use the long way to ... > Dim TotalAccounts As Long, ... We declare and resize an array variable with the ...
    (microsoft.public.excel.programming)
  • Re: Types in ASP, is it possible?
    ... > I am writing a basic tournament submit site for a poker tournament ... > this is read from the database and store tournament numbers in an array ... > dim Number ...
    (microsoft.public.inetserver.asp.general)
  • Re: Using response.write to display system.drawing.image
    ... You would typically Response.Write the byte array itself. ... SQLServer 2000 database. ... Dim FullSizeImage As System.Drawing.Image ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Duplicates phone numbers and extensions
    ... I read the numbers into an array, sort the array, then compare the current ... Dim x As Integer ... database and work great. ... duplicates in the first place. ...
    (microsoft.public.access.modulesdaovba)
  • Re: KirbyBase
    ... creating objects from the database records was much easier. ... Hal, I don't know if you have had a chance to take a look at the beta yet, but I basically tried to implement a uniform way to specify one-to-one links, one-to-many links, and calculated fields in the ... I suppose it would in effect be embedding an array where all the ... My first couple of attempts at adding more complexity to KirbyBase did not honor this concept. ...
    (comp.lang.ruby)