Same Row merge to Second worksheet
 

I have a little problem with a few of the worksheets that have been given to
me. It looks like they have merged two and maybe three worksheets into one
and I have to seperate them. The common two colume is a CAT Number and
DESCRIPTION. What I need if it is possible to look at the CAT# number and if
it is the same as the next row place bith rows into another worksheet. I can
then go down through that worksheet and figure out if it is needed or not.

CAT# COST LOCATION DESCRIPTION REORDERPT QUAN LAST PUR
1223 1.20 I7R2 Joint 20 5 01/23/05
1223 1.20 I7R2 nuckle 20 5 01/23/05
1245 7.20 IAR1 Paste 10 2 11/14/04
1254 3.40 I6R2 Backing 15 3 12/13/04
1401 1.25 I4R7 Backing 100 20 02/10/05
1401 1.25 I4R7 Backing 100 20 02/10/05
1502 2.10 I3R9 Jell 30 10 01/09/05
1506 1.76 I3R6 Paste 20 5 01/21/05
1506 1.76 I8R3 Paste 15 2 01/15/05


What I would like to see on the second worksheet would be all of the info
from every duplicate Cat#, is this possible?

Thank in advance for any help.

Same Row merge to Second worksheet
 

After someone took such great pains to put it all together we just want to
tear it apart. Ain't that the way life goes... Have you considered using a
pivot table? Place your cursor anywhere in the middle of the data and select
Data - Pivot Table... A wizard will pop up . Try just selecting finish.
Excel is really pretty good at makeing all of the right guesses here. A new
sheet will be created with a pivot table in the middle of it. Drag the Cat#
off of the toolbar and into the left hand column. Then drag the Quantity
Ordered into the middle. You can drag the date and put it beside the Cat# if
that makes you happy. Play with it for a while and see if that gets you what
you want.

HTH

"Daniell" wrote:

I have a little problem with a few of the worksheets that have been given to
me. It looks like they have merged two and maybe three worksheets into one
and I have to seperate them. The common two colume is a CAT Number and
DESCRIPTION. What I need if it is possible to look at the CAT# number and if
it is the same as the next row place bith rows into another worksheet. I can
then go down through that worksheet and figure out if it is needed or not.

CAT# COST LOCATION DESCRIPTION REORDERPT QUAN LAST PUR
1223 1.20 I7R2 Joint 20 5 01/23/05
1223 1.20 I7R2 nuckle 20 5 01/23/05
1245 7.20 IAR1 Paste 10 2 11/14/04
1254 3.40 I6R2 Backing 15 3 12/13/04
1401 1.25 I4R7 Backing 100 20 02/10/05
1401 1.25 I4R7 Backing 100 20 02/10/05
1502 2.10 I3R9 Jell 30 10 01/09/05
1506 1.76 I3R6 Paste 20 5 01/21/05
1506 1.76 I8R3 Paste 15 2 01/15/05


What I would like to see on the second worksheet would be all of the info
from every duplicate Cat#, is this possible?

Thank in advance for any help.

Same Row merge to Second worksheet
 

Daniell,

Try (tested)


Sub move1()
For y = Sheet1.UsedRange.Rows.Count To 2 Step -1
Sheets(1).Activate
If ActiveSheet.Cells(y, 1).Value = ActiveSheet.Cells(y - 1, 1).Value Then
y1 = Trim(Str(y))
ActiveSheet.Rows(y1 + ":" & y - 1).Copy
Sheets(2).Activate
ActiveSheet.Cells(Sheets(2).UsedRange.Rows.Count + 2, 1).Select
ActiveSheet.Paste
Sheets(1).Activate
ActiveSheet.Rows(y1 + ":" & y - 1).Delete
End If
Next
End Sub

ben








"Daniell" wrote:

I have a little problem with a few of the worksheets that have been given to
me. It looks like they have merged two and maybe three worksheets into one
and I have to seperate them. The common two colume is a CAT Number and
DESCRIPTION. What I need if it is possible to look at the CAT# number and if
it is the same as the next row place bith rows into another worksheet. I can
then go down through that worksheet and figure out if it is needed or not.

CAT# COST LOCATION DESCRIPTION REORDERPT QUAN LAST PUR
1223 1.20 I7R2 Joint 20 5 01/23/05
1223 1.20 I7R2 nuckle 20 5 01/23/05
1245 7.20 IAR1 Paste 10 2 11/14/04
1254 3.40 I6R2 Backing 15 3 12/13/04
1401 1.25 I4R7 Backing 100 20 02/10/05
1401 1.25 I4R7 Backing 100 20 02/10/05
1502 2.10 I3R9 Jell 30 10 01/09/05
1506 1.76 I3R6 Paste 20 5 01/21/05
1506 1.76 I8R3 Paste 15 2 01/15/05


What I would like to see on the second worksheet would be all of the info
from every duplicate Cat#, is this possible?

Thank in advance for any help.

Same Row merge to Second worksheet
 

Thanks Guys I will try both ways.

"ben" wrote:

Daniell,

Try (tested)


Sub move1()
For y = Sheet1.UsedRange.Rows.Count To 2 Step -1
Sheets(1).Activate
If ActiveSheet.Cells(y, 1).Value = ActiveSheet.Cells(y - 1, 1).Value Then
y1 = Trim(Str(y))
ActiveSheet.Rows(y1 + ":" & y - 1).Copy
Sheets(2).Activate
ActiveSheet.Cells(Sheets(2).UsedRange.Rows.Count + 2, 1).Select
ActiveSheet.Paste
Sheets(1).Activate
ActiveSheet.Rows(y1 + ":" & y - 1).Delete
End If
Next
End Sub

ben








"Daniell" wrote:

I have a little problem with a few of the worksheets that have been given to
me. It looks like they have merged two and maybe three worksheets into one
and I have to seperate them. The common two colume is a CAT Number and
DESCRIPTION. What I need if it is possible to look at the CAT# number and if
it is the same as the next row place bith rows into another worksheet. I can
then go down through that worksheet and figure out if it is needed or not.

CAT# COST LOCATION DESCRIPTION REORDERPT QUAN LAST PUR
1223 1.20 I7R2 Joint 20 5 01/23/05
1223 1.20 I7R2 nuckle 20 5 01/23/05
1245 7.20 IAR1 Paste 10 2 11/14/04
1254 3.40 I6R2 Backing 15 3 12/13/04
1401 1.25 I4R7 Backing 100 20 02/10/05
1401 1.25 I4R7 Backing 100 20 02/10/05
1502 2.10 I3R9 Jell 30 10 01/09/05
1506 1.76 I3R6 Paste 20 5 01/21/05
1506 1.76 I8R3 Paste 15 2 01/15/05


What I would like to see on the second worksheet would be all of the info
from every duplicate Cat#, is this possible?

Thank in advance for any help.

Same Row merge to Second worksheet
 

Hi ben,
I tested your macro with Daniell's data and it looks like it sometimes moves
both following lines with same CAT#, but sometimes moves only one of them!.
Any idea why?.
J_J

"ben" wrote in message
...
Daniell,

Try (tested)


Sub move1()
For y = Sheet1.UsedRange.Rows.Count To 2 Step -1
Sheets(1).Activate
If ActiveSheet.Cells(y, 1).Value = ActiveSheet.Cells(y - 1, 1).Value Then
y1 = Trim(Str(y))
ActiveSheet.Rows(y1 + ":" & y - 1).Copy
Sheets(2).Activate
ActiveSheet.Cells(Sheets(2).UsedRange.Rows.Count + 2, 1).Select
ActiveSheet.Paste
Sheets(1).Activate
ActiveSheet.Rows(y1 + ":" & y - 1).Delete
End If
Next
End Sub

ben








"Daniell" wrote:

I have a little problem with a few of the worksheets that have been given
to
me. It looks like they have merged two and maybe three worksheets into
one
and I have to seperate them. The common two colume is a CAT Number and
DESCRIPTION. What I need if it is possible to look at the CAT# number
and if
it is the same as the next row place bith rows into another worksheet. I
can
then go down through that worksheet and figure out if it is needed or
not.

CAT# COST LOCATION DESCRIPTION REORDERPT QUAN LAST PUR
1223 1.20 I7R2 Joint 20 5 01/23/05
1223 1.20 I7R2 nuckle 20 5 01/23/05
1245 7.20 IAR1 Paste 10 2 11/14/04
1254 3.40 I6R2 Backing 15 3 12/13/04
1401 1.25 I4R7 Backing 100 20 02/10/05
1401 1.25 I4R7 Backing 100 20 02/10/05
1502 2.10 I3R9 Jell 30 10 01/09/05
1506 1.76 I3R6 Paste 20 5 01/21/05
1506 1.76 I8R3 Paste 15 2 01/15/05


What I would like to see on the second worksheet would be all of the info
from every duplicate Cat#, is this possible?

Thank in advance for any help.