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!

Are these rows already there? If, do they have any data? You could use a
looping macro to copy and then paste 4 rows down from the last row on the
destination sheet.

Growth
Gross
Net
# of Leads

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
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!

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!

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))