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

skrev i melding
oups.com...
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

skrev i melding
oups.com...
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


wrote in message
oups.com...
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

skrev i melding
oups.com...
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.


wrote in message
oups.com...
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
...
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.


wrote in message
oups.com...
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" wrote in message
...
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
...
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.


wrote in message
oups.com...
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" wrote in message
...
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
...
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.


wrote in message
oups.com...
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


wrote in message
oups.com...
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" wrote in message
...
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
...
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.


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" wrote in message
...
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
...
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.


wrote in message
oups.com...
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.