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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 ((E-Mail Removed), 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:
> (E-Mail Removed).
>
> ==================================================
> 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, (E-Mail Removed) (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 ((E-Mail Removed), 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:
(E-Mail Removed).

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