Moving Grouped Rows Based on One Cell Value

Mastrius

New Member
Joined
Dec 12, 2016
Messages
2
Hello,

I wasn't sure how to phrase this but here is my data:

ACCOUNT#|CUSTOMER|BATCH|ORDER#|ITEM_NAME|
123______|Customer1|*Blank*|1234 |Nail |
123______|Customer1|FA216 |1234 |Screw |
123______|Customer1|FB315 |1234 |Bolt |
[Blank Color-Filled Row ]
222______|Customer2|FA980 |4567 |Nut |
222______|Customer2|FB287 |4567 |Pedal |
222______|Customer2|FB983 |4567 |Hinge |
[Blank Color-Filled Row]

If there is a blank cell under the "BATCH" column, I need all rows associated with that Customer/Order# to be moved to a separate worksheet, and deleted from the existing sheet. So for example above, since Customer1's order has a *Blank* "BATCH" cell, all three rows of data that are associated with Customer1 need to be removed from this sheet, and inserted into a new sheet.

The column headers are always the same and will never change, but the number of rows (so number of orders, customers, etc.) change every day.

Any help would be greatly appreciated.

Thank you,
-Ryan
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
I think this would be helpfull:
[url]http://www.mrexcel.com/forum/excel-questions/980097-copy-row-condition.html#post4702453 [/URL]

Code:
Sub FilterMini()
With Range("A1").CurrentRegion
.AutoFilter Field:=3, Criteria1:="", Operator:=xlFilterValues  'if it is exactly "*Blank*" put it between the "" area
.SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")   'Edit sheet name   
.EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub
Maybe works ;)
Yours,
M
 

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
I checked it, It works perfect, But it will also copy the header row and cut it to the next page.
maybe one offset command can resolve it, I will try and let you know.
Just be mindful the code originally belongs to My Aswer Is This I just made a simple edit.
Yours,
M
 

Mastrius

New Member
Joined
Dec 12, 2016
Messages
2
Hello M,

Thank you for the response. I tried the code, and it copied the header row, as well as the single row that included the blank "Batch" cell. The only problem is that it did not copy the other two rows that were associated with that customer. So for the above example, I need it to copy all three rows associated with "Customer1" but it only copied the one row that had the blank batch when I tried it.

I also need this to scale with my orders, so that if there are multiple orders on the sheet that have this issue, it will copy all of them to the new sheet. But if I can get it working right with just one of the orders, I'm confident I can figure out the scalability issue myself.

Thanks,

-Ryan
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,675
Messages
5,838,714
Members
430,566
Latest member
ChanchalSingh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top