one row per item one row per order
 

i have an order history spreadsheet that currently has one line per
item, with multiple orders for each item. i would like to move the data
with a vba macro to end up with one row per order. i would like excel
to check for text in each "orderqty" column in each row. if text
exists, copy columns a, b, and all 6 cells for that order starting with
"ordernum" to the first 8 cells in the first blank row at the bottom of
the sheet.

it should iterate to the right across a row for each column headed by
orderqty, and skip to the next row when it encounters the first blank
orderqty.

i guess i'll need two loops - one as it iterates across column headers,
and one as it iterates down the row.

for an example section of the spreadsheet, see my forum post he
http://www.mrexcel.com/board2/viewtopic.php?t=227255

one row per item one row per order
 

Sub ReorgData()
dim lastrow as Long, rw as Long
Dim i as Long, j as Long
Dim a, b

lastrow = Cells(rows.count,1).End(xlup)(2).Row
rw = lastrow
for i = 1 to lastwor
a = cells(i,1)
b = cells(i,2)
for j = 3 to 256 step 6
if isempty(cells(i,j+2)) then
exit for
else
cells(i,j).Resize(1,6).copy cells(rw,3)
cells(rw,1) = a
cells(rw,2) = b
rw = rw + 1
end if
next j
Next i
End sub

--
Regards,
Tom Ogilvy


" wrote:

i have an order history spreadsheet that currently has one line per
item, with multiple orders for each item. i would like to move the data
with a vba macro to end up with one row per order. i would like excel
to check for text in each "orderqty" column in each row. if text
exists, copy columns a, b, and all 6 cells for that order starting with
"ordernum" to the first 8 cells in the first blank row at the bottom of
the sheet.

it should iterate to the right across a row for each column headed by
orderqty, and skip to the next row when it encounters the first blank
orderqty.

i guess i'll need two loops - one as it iterates across column headers,
and one as it iterates down the row.

for an example section of the spreadsheet, see my forum post he
http://www.mrexcel.com/board2/viewtopic.php?t=227255

one row per item one row per order
 

that works perfectly. (well, other than the "lastwor" typo, but that
was easy to fix.)

thank you for your help!