Here is a simplified version of my problem:I have a column of about 1000
numbers, lets say all the numbers are in the column A. I want to take the
first 16 numbers andconcatenate them so that they are arranged like this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of the
column and do the same thing. I want to repeat this for the numbers that I
have in that column.

Please help!

HIMU

Not sure if I got it totally correct - but try this (untested)

Dim x as long, lrw as long, z ' not sure of variable type for z

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = 16 To 1 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Himu" wrote in message
...
Here is a simplified version of my problem:I have a column of about 1000
numbers, lets say all the numbers are in the column A. I want to take the
first 16 numbers andconcatenate them so that they are arranged like this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of the
column and do the same thing. I want to repeat this for the numbers that I
have in that column.

Please help!

HIMU

if you are open to a macro, something like

assuming data starts in row 1, column a

Sub Macro1()
Dim groupcount As Integer
Dim grouping As Integer
Dim group(1000)
lastrow = Cells(1, 1).End(xlDown).Row
'set grouping to 16 cells
grouping = 16
groupcount = lastrow / grouping
groupcount = Application.WorksheetFunction.RoundUp(groupcount, 0)
For i = 1 To groupcount
For j = 1 To grouping
group(i) = Cells(i * grouping - j + 1, 1) & group(i)
Next j
Next i
' this leaves the groups in group(i)
' write them out anywhere you like by the following
' this puts them in colum E (column 5) rows 1 and up
For i = 1 To groupcount
Cells(i, 5) = group(i)
Next i
End Sub
"Himu" wrote:

Here is a simplified version of my problem:I have a column of about 1000
numbers, lets say all the numbers are in the column A. I want to take the
first 16 numbers andconcatenate them so that they are arranged like this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of the
column and do the same thing. I want to repeat this for the numbers that I
have in that column.

Please help!

HIMU

almost got this to work, until i got to the last 5 rows and got an error


Dim x As Long, x2 As Long, lrw As Long, z ' not sure of variable type for z
Dim y As Long
Sub test22()
y = 1
x = 16 ' set first row to start
x2 = lrw - 16
z = "" ' set start value of concatination

lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For x = lrw To lrw - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
lrw = lrw - 15 ' set next loop
z = ""
Loop

End Sub


--


Gary


"STEVE BELL" wrote in message
news:HZgEe.303$9y3.249@trnddc06...
Not sure if I got it totally correct - but try this (untested)

Dim x as long, lrw as long, z ' not sure of variable type for z

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = 16 To 1 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Himu" wrote in message
...
Here is a simplified version of my problem:I have a column of about 1000
numbers, lets say all the numbers are in the column A. I want to take the
first 16 numbers andconcatenate them so that they are arranged like this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of the
column and do the same thing. I want to repeat this for the numbers that
I
have in that column.

Please help!

HIMU

Not sure why you used
For x = lrw To lrw - 15
and not sure why you are starting with the last row and stepping backwards.
(perhaps you are doing this to try and capture all the rows - see below)

The problem is most likely occuring because lrw - 15 goes negative at row
14.

The idea of the first loop is to start at row 16 and concatenate rows 16 to
rows 1
go to row 32 and repeat, and so on. When x + 16 is greater than lrw the
loop stops.

There is only one problem with this - if the total number of rows is not
equal to an
exact multiple of 16, than the last few rows will not be included in the
concatination.
The is a way around that, but it would entail concatination less than 16
rows at the
end.

keep me posted... let me know how you want to handle any extra rows and
we'll
build a work around...
--
steveB

Remove "AYN" from email to respond
"Gary Keramidas" wrote in message
...
almost got this to work, until i got to the last 5 rows and got an error


Dim x As Long, x2 As Long, lrw As Long, z ' not sure of variable type for
z
Dim y As Long
Sub test22()
y = 1
x = 16 ' set first row to start
x2 = lrw - 16
z = "" ' set start value of concatination

lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For x = lrw To lrw - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
lrw = lrw - 15 ' set next loop
z = ""
Loop

End Sub


--


Gary


"STEVE BELL" wrote in message
news:HZgEe.303$9y3.249@trnddc06...
Not sure if I got it totally correct - but try this (untested)

Dim x as long, lrw as long, z ' not sure of variable type for z

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = 16 To 1 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Himu" wrote in message
...
Here is a simplified version of my problem:I have a column of about
1000
numbers, lets say all the numbers are in the column A. I want to take
the
first 16 numbers andconcatenate them so that they are arranged like
this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of the
column and do the same thing. I want to repeat this for the numbers that
I
have in that column.

Please help!

HIMU

your solution didn't seem to work for me, i was just trying to adapt it. i
am nowhere near as proficient as you in excel code, i was just trying to see
if i could get it to work.

when i ran yours, it seemed to only give me one cell, not 16 concatenated. i
was probably doing something wrong.

--


Gary


"STEVE BELL" wrote in message
news:N5tEe.776$vY2.145@trnddc09...
Not sure why you used
For x = lrw To lrw - 15
and not sure why you are starting with the last row and stepping
backwards.
(perhaps you are doing this to try and capture all the rows - see below)

The problem is most likely occuring because lrw - 15 goes negative at row
14.

The idea of the first loop is to start at row 16 and concatenate rows 16
to rows 1
go to row 32 and repeat, and so on. When x + 16 is greater than lrw the
loop stops.

There is only one problem with this - if the total number of rows is not
equal to an
exact multiple of 16, than the last few rows will not be included in the
concatination.
The is a way around that, but it would entail concatination less than 16
rows at the
end.

keep me posted... let me know how you want to handle any extra rows and
we'll
build a work around...
--
steveB

Remove "AYN" from email to respond
"Gary Keramidas" wrote in message
...
almost got this to work, until i got to the last 5 rows and got an error


Dim x As Long, x2 As Long, lrw As Long, z ' not sure of variable type
for z
Dim y As Long
Sub test22()
y = 1
x = 16 ' set first row to start
x2 = lrw - 16
z = "" ' set start value of concatination

lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For x = lrw To lrw - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
lrw = lrw - 15 ' set next loop
z = ""
Loop

End Sub


--


Gary


"STEVE BELL" wrote in message
news:HZgEe.303$9y3.249@trnddc06...
Not sure if I got it totally correct - but try this (untested)

Dim x as long, lrw as long, z ' not sure of variable type for z

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = 16 To 1 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Himu" wrote in message
...
Here is a simplified version of my problem:I have a column of about
1000
numbers, lets say all the numbers are in the column A. I want to take
the
first 16 numbers andconcatenate them so that they are arranged like
this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of
the
column and do the same thing. I want to repeat this for the numbers
that I
have in that column.

Please help!

HIMU

Gary,

Flattery will get you anything...

Decided to test my code and found some "my bads"
so touched it up and the following seems to work.
Notice that z is dim'd as a string. It has trouble with z as a numeric,
not sure why but it may be related to the restriction on the actual number
of numbers allowed in a cell.

Remember that if the total number of rows is not an even multiple of 16 than
the last rows will not be concatenated. Let me know if you would like a
work-a-round
for that - it's fairly easy...
============================
Dim x As Long, lrw As Long, z As String, y As Long

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = x To x - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(y, 1) ' concatinate
Next
Cells(x, 2) = z
' do code

z = "" ' reset z

x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Gary Keramidas" wrote in message
...
your solution didn't seem to work for me, i was just trying to adapt it.
i am nowhere near as proficient as you in excel code, i was just trying to
see if i could get it to work.

when i ran yours, it seemed to only give me one cell, not 16 concatenated.
i was probably doing something wrong.

--


Gary


"STEVE BELL" wrote in message
news:N5tEe.776$vY2.145@trnddc09...
Not sure why you used
For x = lrw To lrw - 15
and not sure why you are starting with the last row and stepping
backwards.
(perhaps you are doing this to try and capture all the rows - see below)

The problem is most likely occuring because lrw - 15 goes negative at row
14.

The idea of the first loop is to start at row 16 and concatenate rows 16
to rows 1
go to row 32 and repeat, and so on. When x + 16 is greater than lrw the
loop stops.

There is only one problem with this - if the total number of rows is not
equal to an
exact multiple of 16, than the last few rows will not be included in the
concatination.
The is a way around that, but it would entail concatination less than 16
rows at the
end.

keep me posted... let me know how you want to handle any extra rows and
we'll
build a work around...
--
steveB

Remove "AYN" from email to respond
"Gary Keramidas" wrote in message
...
almost got this to work, until i got to the last 5 rows and got an error


Dim x As Long, x2 As Long, lrw As Long, z ' not sure of variable type
for z
Dim y As Long
Sub test22()
y = 1
x = 16 ' set first row to start
x2 = lrw - 16
z = "" ' set start value of concatination

lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For x = lrw To lrw - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
lrw = lrw - 15 ' set next loop
z = ""
Loop

End Sub


--


Gary


"STEVE BELL" wrote in message
news:HZgEe.303$9y3.249@trnddc06...
Not sure if I got it totally correct - but try this (untested)

Dim x as long, lrw as long, z ' not sure of variable type for z

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = 16 To 1 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Himu" wrote in message
...
Here is a simplified version of my problem:I have a column of about
1000
numbers, lets say all the numbers are in the column A. I want to take
the
first 16 numbers andconcatenate them so that they are arranged like
this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of
the
column and do the same thing. I want to repeat this for the numbers
that I
have in that column.

Please help!

HIMU

i'll give it a try. this was just an exercise for me to try an learn some
more vba. i'm not sure what the op was looking for.

--


Gary


"STEVE BELL" wrote in message
news:yXvEe.3076$6M3.1118@trnddc03...
Gary,

Flattery will get you anything...

Decided to test my code and found some "my bads"
so touched it up and the following seems to work.
Notice that z is dim'd as a string. It has trouble with z as a numeric,
not sure why but it may be related to the restriction on the actual number
of numbers allowed in a cell.

Remember that if the total number of rows is not an even multiple of 16
than
the last rows will not be concatenated. Let me know if you would like a
work-a-round
for that - it's fairly easy...
============================
Dim x As Long, lrw As Long, z As String, y As Long

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = x To x - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(y, 1) ' concatinate
Next
Cells(x, 2) = z
' do code

z = "" ' reset z

x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Gary Keramidas" wrote in message
...
your solution didn't seem to work for me, i was just trying to adapt it.
i am nowhere near as proficient as you in excel code, i was just trying
to see if i could get it to work.

when i ran yours, it seemed to only give me one cell, not 16
concatenated. i was probably doing something wrong.

--


Gary


"STEVE BELL" wrote in message
news:N5tEe.776$vY2.145@trnddc09...
Not sure why you used
For x = lrw To lrw - 15
and not sure why you are starting with the last row and stepping
backwards.
(perhaps you are doing this to try and capture all the rows - see below)

The problem is most likely occuring because lrw - 15 goes negative at
row 14.

The idea of the first loop is to start at row 16 and concatenate rows 16
to rows 1
go to row 32 and repeat, and so on. When x + 16 is greater than lrw the
loop stops.

There is only one problem with this - if the total number of rows is not
equal to an
exact multiple of 16, than the last few rows will not be included in the
concatination.
The is a way around that, but it would entail concatination less than 16
rows at the
end.

keep me posted... let me know how you want to handle any extra rows and
we'll
build a work around...
--
steveB

Remove "AYN" from email to respond
"Gary Keramidas" wrote in message
...
almost got this to work, until i got to the last 5 rows and got an
error


Dim x As Long, x2 As Long, lrw As Long, z ' not sure of variable type
for z
Dim y As Long
Sub test22()
y = 1
x = 16 ' set first row to start
x2 = lrw - 16
z = "" ' set start value of concatination

lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For x = lrw To lrw - 15 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
lrw = lrw - 15 ' set next loop
z = ""
Loop

End Sub


--


Gary


"STEVE BELL" wrote in message
news:HZgEe.303$9y3.249@trnddc06...
Not sure if I got it totally correct - but try this (untested)

Dim x as long, lrw as long, z ' not sure of variable type for z

x = 16 ' set first row to start
z = "" ' set start value of concatination
lrw = Cells(Rows.Count, "A").End(xlUp).Row ' find last row

Do While x < lrw ' loop until last row is passed
For y = 16 To 1 Step -1 ' loop 16 rows from 16th to 1st
z = z & Cells(x, y) ' concatinate
Next
MsgBox z ' show value
x = x + 16 ' set next loop
Loop


--
steveB

Remove "AYN" from email to respond
"Himu" wrote in message
...
Here is a simplified version of my problem:I have a column of about
1000
numbers, lets say all the numbers are in the column A. I want to take
the
first 16 numbers andconcatenate them so that they are arranged like
this:
A16A15A14A13A12A11A10....A1. Then I want take the next 16 numbers of
the
column and do the same thing. I want to repeat this for the numbers
that I
have in that column.

Please help!

HIMU