Same Row merge to Second worksheet

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Guys I will try both ways.

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

 

[J_J]

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