Combine col & row headings and sequence them to single product val
 

This is my first posting, even though I use this site all the time for help &
reference. I know just enough about macros to be dangerous! : ) I can't
figure this one out, since mostly I record, not write the code!! I ended up
with a very manual process, despite my attempts. Ideally I'd like to use a
macro, but the sequencing involved has me stumped. Any ideas/input using
functions as well code would help!

The extracted data looks like this short example with 3 rows/5 columns:

A B C D E
Acct# Acct Name Comp Comp Comp
111 Cash 500.00 550.00 590.00
112 Cash REC 600.00 660.00 690.00

I need to display like this:
A B C
000-111 Cash-Comp 500.00
001-111 Cash-Comp 550.00
002-111 Cash-Comp 590.00
000-112 CashREC-Comp 600.00
001-112 CashREC-Comp 660.00
002-112 CashREC-Comp 690.00

Column A to be a combined # derived from the Comp(as a sequencing # prefixed
by 00, beginning with 0)
Column B to be a combined description derived from Acct Name & the text in
the cell for Comp.
Column C to be the intersecting value of those.

The number of rows & columns will vary from extract to extract.

Any ideas? This manipulation has to be done several times a week, for
several different extracts. Changing the way it extracts is not optional
(it's a gov't website) Am I stuck spending hours manipulating this data
around or can Excel help?
Thanks so much in advance! D. Cleghorn

Combine col & row headings and sequence them to single product val
 

I've made a few assumptions he raw data is in "Sheet1" and is to be pasted
into "Sheet2"; raw data starts with titles at A1 and has no blank lines. You
can probably figure out which variables to change.

Sub Transpose_Report()

Dim iRowIndex As Integer
Dim iColIndex As Integer
Dim iPasteRow As Integer

Dim wkSrc As Worksheet
Dim wkTgt As Worksheet

Set wkSrc = ActiveWorkbook.Sheets("Sheet1")
Set wkTgt = ActiveWorkbook.Sheets("Sheet2")
iPasteRow = 2

For iRowIndex = 2 To wkSrc.UsedRange.Rows.Count
For iColIndex = 3 To WorksheetFunction.CountA(wkSrc.Rows(iRowIndex))

With wkTgt.Rows(iPasteRow)
.Cells(1).Value = Format(iPasteRow - 1, "000") & " - " &
wkSrc.Cells(iRowIndex, 1)
.Cells(2).Value = wkSrc.Cells(iRowIndex, 2)
.Cells(3).Value = wkSrc.Cells(iRowIndex, iColIndex)
End With
iPasteRow = iPasteRow + 1
Next iColIndex
Next iRowIndex

End Sub

Combine col & row headings and sequence them to single product
 

Pete- Thank you for the quick response! Your assumptions are on target, but
unfortunately I've been unable to make it work. Initally I got a syntax
error, but when I (thought!) I corrected that, I got an error I'm not
familiar with:
Connection to type library or object library for remote process has been
lost. Press OK for dialog to remove reference.
I have NO clue what this means, except that I probably didn't "fix" the
syntax correctly. What does this error mean?
Thanks again! Dana

"Pete McCosh" wrote:

I've made a few assumptions he raw data is in "Sheet1" and is to be pasted
into "Sheet2"; raw data starts with titles at A1 and has no blank lines. You
can probably figure out which variables to change.

Sub Transpose_Report()

Dim iRowIndex As Integer
Dim iColIndex As Integer
Dim iPasteRow As Integer

Dim wkSrc As Worksheet
Dim wkTgt As Worksheet

Set wkSrc = ActiveWorkbook.Sheets("Sheet1")
Set wkTgt = ActiveWorkbook.Sheets("Sheet2")
iPasteRow = 2

For iRowIndex = 2 To wkSrc.UsedRange.Rows.Count
For iColIndex = 3 To WorksheetFunction.CountA(wkSrc.Rows(iRowIndex))

With wkTgt.Rows(iPasteRow)
.Cells(1).Value = Format(iPasteRow - 1, "000") & " - " &
wkSrc.Cells(iRowIndex, 1)
.Cells(2).Value = wkSrc.Cells(iRowIndex, 2)
.Cells(3).Value = wkSrc.Cells(iRowIndex, iColIndex)
End With
iPasteRow = iPasteRow + 1
Next iColIndex
Next iRowIndex

End Sub