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.