On Nov 10, 3:23*pm, Joel wrote:
You really just need a simple macro.

Sub CreateSheet2()

RowCount = 2
NewRowCount = 2

'copy header row from sheet1 to sheet2
Sheets("Sheet1").Rows(1).Copy _
* *Destination:=Sheets("Sheet2").Rows(1)

With Sheets("Sheet1")
* *Do While .Range("A" & RowCount) < ""
* * * .Rows(RowCount).Copy
* * * With Sheets("Sheet2")
* * * * *.Rows(NewRowCount).Paste
* * * * *.Range("A" & (NewRowCount + 1)) = "Growth"
* * * * *.Range("A" & (NewRowCount + 2)) = "Gross"
* * * * *.Range("A" & (NewRowCount + 3)) = "Net"
* * * * *.Range("A" & (NewRowCount + 4)) = "# Number of Leads"
* * * * *NewRowCount = NewRowCount + 5
* * * End With
* * * RowCount = RowCount + 1
* *Loop
End With

End Sub



" wrote:
Hello,
I have the following table on sheet1.
State * * * *Jan08 *Feb08 Mar08
Ny * * * * * * *1000 * *1500 * 1800
NJ * * * * * * * 8000 * 2500 * *3000
-
-

How do I bring in the above info to the below format on sheet2 without
having to do copy and paste. *I would love to use some kind of
function that will compare state and the month and will give me the
numbers as the output.

State * * * * Jan08 *Feb08 Mar08
Ny * * * * * * *1000 * *1500 * 1800
Growth
Gross
Net
# of Leads
NJ * * * * * * * 8000 * 2500 * *3000
Growth
Gross
Net
# of Leads

thanks!- Hide quoted text -

- Show quoted text -

I got the answer without having to use the macro. I used sumproduct
function. Here is the formula that I've used
=SUMPRODUCT(($B$8:$K$8=B1)*($A$9:$A$22=A1)*($B$9:$ K$22))