Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to merge columns from one worksheet to another worksheet | Excel Worksheet Functions | |||
how to merge separate worksheet | Excel Worksheet Functions | |||
how do I merge a worksheet into another one? | New Users to Excel | |||
How to merge more than one worksheet | Excel Worksheet Functions | |||
Merge 2 rows in worksheet | Excel Discussion (Misc queries) |