How to remove several ranges in table at the same time

B

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
166
Office Version
  1. 365
Platform
  1. Windows
Hi, I have a sheet with thousands or rows showing transport statuses. (See example below) . The start (Sendt) and end status (Delivered) is always the same, but the number of steps between these statuses will wary. The id number (Showing in col D will always be available for all steps in a shipment, and will be unique.

How can I find all shipments that are completed? (has both the Sendt and Delivered status so these can be removed from the list, including all steps between Sendt and Delivered? )

Example.xlsx
ABCD
11245New YorkSendt55468
21245New YorkStep 155468
31245New YorkStep 255468
41245New YorkStep 355468
51245New YorkStep 455468
61245New YorkStep 555468
71245New YorkStep 6 55468
81245New YorkStep 755468
91245New YorkDelivered55468
101354ChicagoSendt6657985
111354ChicagoStep 26657985
121354ChicagoStep 36657985
131354ChicagoStep 46657985
141354ChicagoStep 56657985
151445SeattleSendt5578223
161445SeattleStep 15578223
171445SeattleStep 25578223
181445SeattleStep 35578223
191445SeattleStep 45578223
201445SeattleStep 55578223
211445SeattleStep 6 5578223
221445SeattleStep 75578223
231445SeattleDelivered5578223
245589 DallasSendt997855
255589 DallasStep 1997855
265589 DallasStep 2997855
Sheet1
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Is it correct that there is no heading row?
 
Upvote 0
Added headers (fields) to your data and made it into an Excel Table:
Book1
ABCD
1CodeDestinationStatusCode2
21245New YorkSendt55468
31245New YorkStep 155468
41245New YorkStep 255468
51245New YorkStep 355468
61245New YorkStep 455468
71245New YorkStep 555468
81245New YorkStep 6 55468
91245New YorkStep 755468
101245New YorkDelivered55468
111354ChicagoSendt6657985
121354ChicagoStep 26657985
131354ChicagoStep 36657985
141354ChicagoStep 46657985
151354ChicagoStep 56657985
161445SeattleSendt5578223
171445SeattleStep 15578223
181445SeattleStep 25578223
191445SeattleStep 35578223
201445SeattleStep 45578223
211445SeattleStep 55578223
221445SeattleStep 6 5578223
231445SeattleStep 75578223
241445SeattleDelivered5578223
255589 DallasSendt997855
265589 DallasStep 1997855
275589 DallasStep 2997855
Sheet1

I actually started with a FILTER formula, but couldn't get it to work. Then saw that Power Query might be able to handle this easier, and sure enough it did!
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupedRows = Table.Group(Source, {"Code", "Destination", "Code2"}, {{"Count", each _, type table [Code=number, Destination=text, Status=text, Code2=number]}}),
    AddedTableRowCount = Table.AddColumn(GroupedRows, "Custom", each Table.RowCount( [Count] )),
    AddedDeliveredTF = Table.AddColumn(AddedTableRowCount, "Custom.1", each [Count][Status]{[Custom]-1} = "Delivered"),
    FilteredTrueRows = Table.SelectRows(AddedDeliveredTF, each ([Custom.1] = false)),
    RemovedOtherColumns = Table.SelectColumns(FilteredTrueRows,{"Code", "Destination", "Code2", "Count"}),
    ExpandedCount = Table.ExpandTableColumn(RemovedOtherColumns, "Count", {"Status"}, {"Status"}),
    ReorderedColumns = Table.ReorderColumns(ExpandedCount,{"Code", "Destination", "Status", "Code2"}),
    ChangedType = Table.TransformColumnTypes(ReorderedColumns,{{"Code", Int64.Type}, {"Destination", type text}, {"Status", type text}, {"Code2", Int64.Type}})
in
    ChangedType
Which resulted in this table:
Book1
FGHI
1CodeDestinationStatusCode2
21354ChicagoSendt6657985
31354ChicagoStep 26657985
41354ChicagoStep 36657985
51354ChicagoStep 46657985
61354ChicagoStep 56657985
75589 DallasSendt997855
85589 DallasStep 1997855
95589 DallasStep 2997855
Sheet1

Maybe worth noting that when an entry (Code) is either changed to or added as Delivered (regardless of its position in the source table), or if a new code is added without a Delivered status, a Refresh of the resulting table will automatically add/remove the changed entries. Also, since the grouping is by Code, Destination, and Code2, any given Code must always have the same value for both Destination and Code2 which is what appeared in the original data.
 
Upvote 0
jdellasala said:
Added headers (fields) to your data and made it into an Excel Table:
Book1
ABCD
1CodeDestinationStatusCode2
21245New YorkSendt55468
31245New YorkStep 155468
41245New YorkStep 255468
51245New YorkStep 355468
61245New YorkStep 455468
71245New YorkStep 555468
81245New YorkStep 6 55468
91245New YorkStep 755468
101245New YorkDelivered55468
111354ChicagoSendt6657985
121354ChicagoStep 26657985
131354ChicagoStep 36657985
141354ChicagoStep 46657985
151354ChicagoStep 56657985
161445SeattleSendt5578223
171445SeattleStep 15578223
181445SeattleStep 25578223
191445SeattleStep 35578223
201445SeattleStep 45578223
211445SeattleStep 55578223
221445SeattleStep 6 5578223
231445SeattleStep 75578223
241445SeattleDelivered5578223
255589 DallasSendt997855
265589 DallasStep 1997855
275589 DallasStep 2997855
Sheet1

I actually started with a FILTER formula, but couldn't get it to work. Then saw that Power Query might be able to handle this easier, and sure enough it did!
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupedRows = Table.Group(Source, {"Code", "Destination", "Code2"}, {{"Count", each _, type table [Code=number, Destination=text, Status=text, Code2=number]}}),
    AddedTableRowCount = Table.AddColumn(GroupedRows, "Custom", each Table.RowCount( [Count] )),
    AddedDeliveredTF = Table.AddColumn(AddedTableRowCount, "Custom.1", each [Count][Status]{[Custom]-1} = "Delivered"),
    FilteredTrueRows = Table.SelectRows(AddedDeliveredTF, each ([Custom.1] = false)),
    RemovedOtherColumns = Table.SelectColumns(FilteredTrueRows,{"Code", "Destination", "Code2", "Count"}),
    ExpandedCount = Table.ExpandTableColumn(RemovedOtherColumns, "Count", {"Status"}, {"Status"}),
    ReorderedColumns = Table.ReorderColumns(ExpandedCount,{"Code", "Destination", "Status", "Code2"}),
    ChangedType = Table.TransformColumnTypes(ReorderedColumns,{{"Code", Int64.Type}, {"Destination", type text}, {"Status", type text}, {"Code2", Int64.Type}})
in
    ChangedType
Which resulted in this table:
Book1
FGHI
1CodeDestinationStatusCode2
21354ChicagoSendt6657985
31354ChicagoStep 26657985
41354ChicagoStep 36657985
51354ChicagoStep 46657985
61354ChicagoStep 56657985
75589 DallasSendt997855
85589 DallasStep 1997855
95589 DallasStep 2997855
Sheet1

Maybe worth noting that when an entry (Code) is either changed to or added as Delivered (regardless of its position in the source table), or if a new code is added without a Delivered status, a Refresh of the resulting table will automatically add/remove the changed entries. Also, since the grouping is by Code, Destination, and Code2, any given Code must always have the same value for both Destination and Code2 which is what appeared in the original data.
Click to expand...
Power query is good, but I am afraid I need VBA as it will be part of a longer process
 
Upvote 0
Brutusar said:
Power query is good, but I am afraid I need VBA as it will be part of a longer process
Click to expand...
The first spreadsheet app I used was VisiCalc which was released in 1979. I retired in 2020 after 27 years in IT Support and the last 5 years in Inventory Management of over 350,000 Smartphones at various stages of life, four carriers, and over 100,000 users, and replacement inventory for over 50 offices. It could take a day to fully analyze an Inventory Report against ONE carrier. Had I known Power Query, that process would have been reduced to the time to download the reports and then clicking Refresh All - less than an hour.
I don't understand what can only be described as a fetish for VBA which is an antiquated language, overly verbose, takes hours to write and debug, and easily broken. Power Query for all its shortcomings is intuitive, has a very helpful UI, and easy to learn. I'm just sorry I didn't learn it five years earlier! There are great YouTube playlists on the subject here and here. They are long, but you should be able to start being productive after just a few videos. And you can always get help here.
If VBA is your preferred solution, I'd have to withdraw.
 
Upvote 0
jdellasala said:
The first spreadsheet app I used was VisiCalc which was released in 1979. I retired in 2020 after 27 years in IT Support and the last 5 years in Inventory Management of over 350,000 Smartphones at various stages of life, four carriers, and over 100,000 users, and replacement inventory for over 50 offices. It could take a day to fully analyze an Inventory Report against ONE carrier. Had I known Power Query, that process would have been reduced to the time to download the reports and then clicking Refresh All - less than an hour.
I don't understand what can only be described as a fetish for VBA which is an antiquated language, overly verbose, takes hours to write and debug, and easily broken. Power Query for all its shortcomings is intuitive, has a very helpful UI, and easy to learn. I'm just sorry I didn't learn it five years earlier! There are great YouTube playlists on the subject here and here. They are long, but you should be able to start being productive after just a few videos. And you can always get help here.
If VBA is your preferred solution, I'd have to withdraw.
Click to expand...
Will have a look, thanks!
 
Upvote 0
Assuming the transport statuses to be deleted are the only ones that will have "Delivered" in Column C, give this code a try...
VBA Code: 
Sub DeleteDeliveredTrensports()
  Dim Cell As Range
  Columns("C").Replace "Delivered", "", xlWhole
  For Each Cell In Columns("C").SpecialCells(xlBlanks).Areas
    Columns("D").Replace Cell.Offset(, 1).Value, "#N/A", xlWhole
  Next
  Columns("D").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub
 
Upvote 0
Rick Rothstein said:
Assuming the transport statuses to be deleted are the only ones that will have "Delivered" in Column C, give this code a try...
VBA Code: 
Sub DeleteDeliveredTrensports()
  Dim Cell As Range
  Columns("C").Replace "Delivered", "", xlWhole
  For Each Cell In Columns("C").SpecialCells(xlBlanks).Areas
    Columns("D").Replace Cell.Offset(, 1).Value, "#N/A", xlWhole
  Next
  Columns("D").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub
Click to expand...
Does this really delete the original data, or is my VBA that bad (TOTALLY POSSIBLE!)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Determine match based on if a date range falls within a larger date range :S
Replies
1
Views
85
dermie_72
D
I
  • Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
493
ItsNursinTime
I
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
599
ahaye
A
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
158
SunnyAlv
SunnyAlv

Forum statistics

Threads
1,215,563
Messages
6,125,572
Members
449,237
Latest member
Chase S

Share this page

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
Back
Top