Permutations - 8 columns

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.

Hi Sandip

John Walkenbach has a permutations generator at
http://j-walk.com/ss/excel/tips/tip46.htm
see if that get you started.

HTH. best wishes Harald

<[email protected]> skrev i melding
news:[email protected]...
> 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.
>

Hi Harald,

I had a look at J-Walk's website. Unfortunately it would not help in
getting the output required.

Thanks
Sandip

Harald Staff wrote:
> Hi Sandip
>
> John Walkenbach has a permutations generator at
> http://j-walk.com/ss/excel/tips/tip46.htm
> see if that get you started.
>
> HTH. best wishes Harald
>
> <[email protected]> skrev i melding
> news:[email protected]...
> > 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.
> >

Possibly

..Value
Sub AAA()
Dim rngA as Range, rngB as Range, rngC as Range
Dim rngD as Range, rngE as Range, cellA as Range
Dim cellB as Range, cellC as Range, cellD as Range
Dim cellE as Range, i as Long, rw as Long
rw = 2
i = 0
set rngA = Range(cells(2,1),Cells(2,1).End(xldown))
set rngB = Range(cells(2,2),Cells(2,2).End(xldown))
set rngC = Range(cells(2,3),Cells(2,3).End(xldown))
set rngD = Range(cells(2,4),Cells(2,4).End(xldown))
set rngE = Range(cells(2,5),Cells(2,5).End(xldown))
for each cellA in rngA
for each cellB in rngB
for each cellC in rngC
for each cellD in rngD
for each cellE in rngE
With Worksheets("NewData")
.cells(rw,i +1).Value = cellA.Value
.cells(rw,i +2).Value = cellB.Value
.cells(rw,i + 3).Value = cellC.Value
.cells(rw,i + 4).Value = cellD.Value
.cells(rw,i + 5).Value = cellE.Value
rw = rw + 1
if rw > 65536 then
rw = 2
i = i + 6
end if
End with
next CellE
next CellD
next CellC
next CellB
next CellA

End Sub

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Hi Harald,
>
> I had a look at J-Walk's website. Unfortunately it would not help in
> getting the output required.
>
> Thanks
> Sandip
>
> Harald Staff wrote:
> > Hi Sandip
> >
> > John Walkenbach has a permutations generator at
> > http://j-walk.com/ss/excel/tips/tip46.htm
> > see if that get you started.
> >
> > HTH. best wishes Harald
> >
> > <[email protected]> skrev i melding
> > news:[email protected]...
> > > 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.
> > >
>

Since you're using a database, why not just let SQL do the work ?

select t.Period, t.account, t.currency, t.code, t.country,[other
columns here], sum(t.amount),average(t.amount)
from DataTable t
group by
t.Period, t.account, t.currency, t.code, t.country,[other column here]

Unless I misunderstand your explanation that should give you the
desired result.

Tim.


<[email protected]> wrote in message
news:[email protected]...
> 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.
>

>We are not allowed to extract the information directly from the database
>in a tabular form.

I suspect if he could query the database, he wouldn't need the combinations.

Sounds like he is blindly querying the database with all possible
combinations to get back any possible results that may exists.

Just my guess of course.

--
Regards,
Tom Ogilvy

"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
news:[email protected]...
> Since you're using a database, why not just let SQL do the work ?
>
> select t.Period, t.account, t.currency, t.code, t.country,[other
> columns here], sum(t.amount),average(t.amount)
> from DataTable t
> group by
> t.Period, t.account, t.currency, t.code, t.country,[other column here]
>
> Unless I misunderstand your explanation that should give you the
> desired result.
>
> Tim.
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>

I took the OP's explanation to mean that owing to the "financial"
(sensitive?) nature of the data they were not permitted to make
complete "copies" of the data by extracting it whole...

Tim.


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> >We are not allowed to extract the information directly from the
> >database
>>in a tabular form.
>
> I suspect if he could query the database, he wouldn't need the
> combinations.
>
> Sounds like he is blindly querying the database with all possible
> combinations to get back any possible results that may exists.
>
> Just my guess of course.
>
> --
> Regards,
> Tom Ogilvy
>
> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
> news:[email protected]...
>> Since you're using a database, why not just let SQL do the work ?
>>
>> select t.Period, t.account, t.currency, t.code, t.country,[other
>> columns here], sum(t.amount),average(t.amount)
>> from DataTable t
>> group by
>> t.Period, t.account, t.currency, t.code, t.country,[other column
>> here]
>>
>> Unless I misunderstand your explanation that should give you the
>> desired result.
>>
>> Tim.
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>
>

Tom,

You are 100% correct in your assumption. We are not allowed to query
the database directly and exact the information in a tabular form.

However there are standardised reports which we can use to extract the
data. The only problem is that the output is in a report format
(predefined presentation) and multiple reports need to be run to see
the whole picture.

The irony is that though we are allowed to see the information(whatever
we want) in a report format, we are not allowed to exact the info
through SQL in a database format inorder to analyse it in the way we
want and not in the rigid standarised reports already available.

Regards
Sandip.

Tim Williams wrote:
> I took the OP's explanation to mean that owing to the "financial"
> (sensitive?) nature of the data they were not permitted to make
> complete "copies" of the data by extracting it whole...
>
> Tim.
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > >We are not allowed to extract the information directly from the
> > >database
> >>in a tabular form.
> >
> > I suspect if he could query the database, he wouldn't need the
> > combinations.
> >
> > Sounds like he is blindly querying the database with all possible
> > combinations to get back any possible results that may exists.
> >
> > Just my guess of course.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
> > news:[email protected]...
> >> Since you're using a database, why not just let SQL do the work ?
> >>
> >> select t.Period, t.account, t.currency, t.code, t.country,[other
> >> columns here], sum(t.amount),average(t.amount)
> >> from DataTable t
> >> group by
> >> t.Period, t.account, t.currency, t.code, t.country,[other column
> >> here]
> >>
> >> Unless I misunderstand your explanation that should give you the
> >> desired result.
> >>
> >> Tim.
> >>
> >>
> >> <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >> >
> >>
> >>
> >
> >

In that case I might still suggest SQL, but in your case use the Excel
SQL driver and run the query on your extracted data.
If your data is in a straight tabular format with a header row you
could try modifying the code below.

You'll need to add a reference to ADO in your project. Replace "col1"
etc in sSQL with your column headings.

Tim.

Sub Tester()
'run a SQL query against the selected range
Dim rs As ADODB.Recordset
Dim iRow As Integer
Dim sSQL As String

sSQL = "select col1, col2 as v from @ group by col1, col2"
Set rs = GetRecords(Selection, sSQL)



If Not rs Is Nothing Then
If Not rs.EOF And Not rs.BOF Then
ActiveSheet.Range("A20").CopyFromRecordset rs
Else
MsgBox "No records found"
End If
End If

End Sub



Function GetRecords(rng As Range, sSQL As String) As ADODB.Recordset
Const S_TEMP_TABLENAME As String = "SQLtempTable"
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath

'name the selected range
On Error Resume Next
ActiveWorkbook.Names.Item(S_TEMP_TABLENAME).Delete
If Err.Number <> 0 Then Err.Clear

On Error GoTo haveError
ActiveWorkbook.Names.Add Name:=S_TEMP_TABLENAME, RefersToLocal:=rng

sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties=""Excel 8.0;HDR=Yes"""


oRS.Open Replace(sSQL, "@", S_TEMP_TABLENAME), oConn
Set GetRecords = oRS
Exit Function

haveError:
MsgBox Err.Description
Set GetRecords = Nothing

End Function


<[email protected]> wrote in message
news:[email protected]...
> Tom,
>
> You are 100% correct in your assumption. We are not allowed to query
> the database directly and exact the information in a tabular form.
>
> However there are standardised reports which we can use to extract
> the
> data. The only problem is that the output is in a report format
> (predefined presentation) and multiple reports need to be run to see
> the whole picture.
>
> The irony is that though we are allowed to see the
> information(whatever
> we want) in a report format, we are not allowed to exact the info
> through SQL in a database format inorder to analyse it in the way we
> want and not in the rigid standarised reports already available.
>
> Regards
> Sandip.
>
> Tim Williams wrote:
>> I took the OP's explanation to mean that owing to the "financial"
>> (sensitive?) nature of the data they were not permitted to make
>> complete "copies" of the data by extracting it whole...
>>
>> Tim.
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > >We are not allowed to extract the information directly from the
>> > >database
>> >>in a tabular form.
>> >
>> > I suspect if he could query the database, he wouldn't need the
>> > combinations.
>> >
>> > Sounds like he is blindly querying the database with all possible
>> > combinations to get back any possible results that may exists.
>> >
>> > Just my guess of course.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
>> > news:[email protected]...
>> >> Since you're using a database, why not just let SQL do the work
>> >> ?
>> >>
>> >> select t.Period, t.account, t.currency, t.code, t.country,[other
>> >> columns here], sum(t.amount),average(t.amount)
>> >> from DataTable t
>> >> group by
>> >> t.Period, t.account, t.currency, t.code, t.country,[other column
>> >> here]
>> >>
>> >> Unless I misunderstand your explanation that should give you the
>> >> desired result.
>> >>
>> >> Tim.
>> >>
>> >>

Tom,

I have understood your VBA coding but facing one problem which is not
resulting in correct output.

The problem is that even after data in a particular column is over, and
the permutation continues with that column assuming the information to
be blank.

Thus results in blank cells in the output and the macro continues until
the whole spreadsheet is full.

How do I avert this so that the macro proceeds to the preceding column
after encountering a blank cell and stops after reaching Col A and
again encounters a blank.

Incase what I have mentioned above is not clear, let me know.

Regards
Sandip.


[email protected] wrote:
> Tom,
>
> You are 100% correct in your assumption. We are not allowed to query
> the database directly and exact the information in a tabular form.
>
> However there are standardised reports which we can use to extract
the
> data. The only problem is that the output is in a report format
> (predefined presentation) and multiple reports need to be run to see
> the whole picture.
>
> The irony is that though we are allowed to see the
information(whatever
> we want) in a report format, we are not allowed to exact the info
> through SQL in a database format inorder to analyse it in the way we
> want and not in the rigid standarised reports already available.
>
> Regards
> Sandip.
>
> Tim Williams wrote:
> > I took the OP's explanation to mean that owing to the "financial"
> > (sensitive?) nature of the data they were not permitted to make
> > complete "copies" of the data by extracting it whole...
> >
> > Tim.
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > >We are not allowed to extract the information directly from the
> > > >database
> > >>in a tabular form.
> > >
> > > I suspect if he could query the database, he wouldn't need the
> > > combinations.
> > >
> > > Sounds like he is blindly querying the database with all possible
> > > combinations to get back any possible results that may exists.
> > >
> > > Just my guess of course.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
> > > news:[email protected]...
> > >> Since you're using a database, why not just let SQL do the work
?
> > >>
> > >> select t.Period, t.account, t.currency, t.code, t.country,[other
> > >> columns here], sum(t.amount),average(t.amount)
> > >> from DataTable t
> > >> group by
> > >> t.Period, t.account, t.currency, t.code, t.country,[other column
> > >> here]
> > >>
> > >> Unless I misunderstand your explanation that should give you the
> > >> desired result.
> > >>
> > >> Tim.
> > >>
> > >>
> > >> <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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.
> > >> >
> > >>
> > >>
> > >
> > >

[email protected] 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
[email protected]

This code:

set rngA = Range(cells(2,1),Cells(2,1).End(xldown))
set rngB = Range(cells(2,2),Cells(2,2).End(xldown))
set rngC = Range(cells(2,3),Cells(2,3).End(xldown))
set rngD = Range(cells(2,4),Cells(2,4).End(xldown))
set rngE = Range(cells(2,5),Cells(2,5).End(xldown))


should restrict the ranges to only look at empty cells.

If you go to the second cell in a column and do End and hit the down arrow -
this is how it determines where the last cell is. If you go beyond what you
expect, then possibly someone has cleared cells by using the spacebar (so
the cell is not empty, it contains a space) or perhaps you copied the data
from somewhere and pasted it into excel.

You could put in a bunch of If statements:
Sub AAA()
Dim rngA as Range, rngB as Range, rngC as Range
Dim rngD as Range, rngE as Range, cellA as Range
Dim cellB as Range, cellC as Range, cellD as Range
Dim cellE as Range, i as Long, rw as Long
rw = 2
i = 0
set rngA = Range(cells(2,1),Cells(2,1).End(xldown))
set rngB = Range(cells(2,2),Cells(2,2).End(xldown))
set rngC = Range(cells(2,3),Cells(2,3).End(xldown))
set rngD = Range(cells(2,4),Cells(2,4).End(xldown))
set rngE = Range(cells(2,5),Cells(2,5).End(xldown))
for each cellA in rngA
if len(trim(cellA)) <> 0 then
for each cellB in rngB
if len(trim(cellB)) <> 0 then
for each cellC in rngC
if len(trim(cellC)) <> 0 then
for each cellD in rngD
if len(trim(cellD)) <> 0 then
for each cellE in rngE
if len(trim(cellE)) <> 0 then
With Worksheets("NewData")
.cells(rw,i +1).Value = cellA.Value
.cells(rw,i +2).Value = cellB.Value
.cells(rw,i + 3).Value = cellC.Value
.cells(rw,i + 4).Value = cellD.Value
.cells(rw,i + 5).Value = cellE.Value
rw = rw + 1
if rw > 65536 then
rw = 2
i = i + 6
end if
End with
End if
next CellE
End If
next CellD
End If
next CellC
End If
next CellB
End if
next CellA

End Sub

--
Regards,
Tom Ogilvy

"Sandip" <[email protected]> wrote in message
news:[email protected]...
> Tom,
>
> I have understood your VBA coding but facing one problem which is not
> resulting in correct output.
>
> The problem is that even after data in a particular column is over, and
> the permutation continues with that column assuming the information to
> be blank.
>
> Thus results in blank cells in the output and the macro continues until
> the whole spreadsheet is full.
>
> How do I avert this so that the macro proceeds to the preceding column
> after encountering a blank cell and stops after reaching Col A and
> again encounters a blank.
>
> Incase what I have mentioned above is not clear, let me know.
>
> Regards
> Sandip.
>
>
> [email protected] wrote:
> > Tom,
> >
> > You are 100% correct in your assumption. We are not allowed to query
> > the database directly and exact the information in a tabular form.
> >
> > However there are standardised reports which we can use to extract
> the
> > data. The only problem is that the output is in a report format
> > (predefined presentation) and multiple reports need to be run to see
> > the whole picture.
> >
> > The irony is that though we are allowed to see the
> information(whatever
> > we want) in a report format, we are not allowed to exact the info
> > through SQL in a database format inorder to analyse it in the way we
> > want and not in the rigid standarised reports already available.
> >
> > Regards
> > Sandip.
> >
> > Tim Williams wrote:
> > > I took the OP's explanation to mean that owing to the "financial"
> > > (sensitive?) nature of the data they were not permitted to make
> > > complete "copies" of the data by extracting it whole...
> > >
> > > Tim.
> > >
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > >We are not allowed to extract the information directly from the
> > > > >database
> > > >>in a tabular form.
> > > >
> > > > I suspect if he could query the database, he wouldn't need the
> > > > combinations.
> > > >
> > > > Sounds like he is blindly querying the database with all possible
> > > > combinations to get back any possible results that may exists.
> > > >
> > > > Just my guess of course.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
> > > > news:[email protected]...
> > > >> Since you're using a database, why not just let SQL do the work
> ?
> > > >>
> > > >> select t.Period, t.account, t.currency, t.code, t.country,[other
> > > >> columns here], sum(t.amount),average(t.amount)
> > > >> from DataTable t
> > > >> group by
> > > >> t.Period, t.account, t.currency, t.code, t.country,[other column
> > > >> here]
> > > >>
> > > >> Unless I misunderstand your explanation that should give you the
> > > >> desired result.
> > > >>
> > > >> Tim.
> > > >>
> > > >>
> > > >> <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> > 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.
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
>

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:
> [email protected] 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
> [email protected]

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:
> [email protected] 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
> [email protected]