I have data in spreadsheet 1 that consists of 8 columns and 2500 rows

The data is laid out as follows
Col A Col B Col C Col D Col E Col F Col G
Col H
Row 1 AL BF0050C Bigfoot 1000 800 200 Smithfield 200
Row 2 VA CS0027F Smith 200 120 80 Austin 215
Row 3 GA HN4237D Maximum 1000 750 250 Smithfield
200

I am trying to pull the information into a spreadsheet consisting of 4
columns
Col A Col B Col C Col D
Row 1 3/31/08 =H1&"-4005-"&A1 -D1 =B1&" - "&C1
Row 2 3/31/08 =H1&"-5360-"&A1 E1 =B1&" - "&C1
Row 3 3/31/08 =H2&"-4005-"&A2 -D1 =B2&" - "&C2
Row 4 3/31/08 =H2&"-5360-"&A2 E1 =B2&" - "&C2
Row 5 3/31/08 =H3&"-4005-"&A3 -D1 =B3&" - "&C3
Row 6 3/31/08 =H3&"-5360-"&A3 E1 =B3&" - "&C3

I need to have 2 rows per line of the original data and need to be able to
create for various number of rows

Any ideas on how to automate or copy or use fill to get every other row to
look at the next row in my data spreadsheet?

Thanks in advance for any assistance!

All I can say is WOW and thanks -- this works perfectly!

"Jialiang Ge [MSFT]" wrote in message
...
Hello Gail,

In order to automate Excel to generate 2 rows per line of the original
data
and create for various number of rows, the most convenient way I think is
to write an Excel macro.

I write one macro for you according to your sample data. The macro can
generate a report in Sheet2(ColA~ColD) according to the data in
Sheet1(ColA~ColH). You may copy & paste the macro into Excel VBA Project
(Alt+F11). For your convenience, I also build a sample spreadsheet (xls)
to
demonstrate how to use the macro. You can download the demo(41751914.xls)
from this message with Outlook Express or Windows Mail.

I think this macro should be self-explanatory because I have added a lot
of
comments among codes. If you have any problem with it, feel free to let me
know.

Sub Generate()

'clear sheet2 (the target sheet)
Sheet2.Rows.Clear

'current row number in the source sheet
Dim sourceRowNum As Integer

For sourceRowNum = 1 To Sheet1.UsedRange.Rows.Count

Dim srcRowNumStr As String
srcRowNumStr = CStr(sourceRowNum)

'the first row in the target sheet
With Sheet2.Rows(sourceRowNum * 2 - 1)
'column A in the target sheet
.Cells(1, 1).Value2 = "3/31/08"

'column B in the target sheet
.Cells(1, 2).Formula = "=Sheet1!H" & srcRowNumStr &
"&""-4005-""&Sheet1!A" & srcRowNumStr

'column C in the target sheet
.Cells(1, 3).Formula = "=-Sheet1!D" & srcRowNumStr

'column D in the target sheet
.Cells(1, 4).Formula = "=Sheet1!B" & srcRowNumStr & "&"" -
""&Sheet1!C" & srcRowNumStr
End With

'the second row in the target sheet
With Sheet2.Rows(sourceRowNum * 2)
'column A in the target sheet
.Cells(1, 1).Value2 = "3/31/08"

'column B in the target sheet
.Cells(1, 2).Formula = "=Sheet1!H" & srcRowNumStr &
"&""-5360-""&Sheet1!A" & srcRowNumStr

'column C in the target sheet
.Cells(1, 3).Formula = "=Sheet1!E" & srcRowNumStr

'column D in the target sheet
.Cells(1, 4).Formula = "=Sheet1!B" & srcRowNumStr & "&"" -
""&Sheet1!C" & srcRowNumStr
End With

Next

End Sub

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Attaching files is frowned upon in these news groups.

I would think someone from Microsoft Online Community Support should be aware of
that.

Post files to an internet file-hosting site and leave the URL for interested
parties to download from there.


Gord Dibben MS Excel MVP

On Tue, 01 Apr 2008 03:03:54 GMT, (Jialiang Ge
[MSFT]) wrote:

For your convenience, I also build a sample spreadsheet (xls) to
demonstrate how to use the macro.

Thanks Gord, I will take care of it in future.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================