VBA Code

brs

New Member
Joined
Oct 5, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
I have a tab called Master in my workbook. This tab includes a ton of data. It has 8,300+ rows and columns from A-AF. Data is entered into the Master tab once a week, so the rows will continue to grow. One of the columns on the spreadsheet is "Customer ID." Let's say we have 100+ Customer ID's.

I'm wanting to extract every row that does not contain one of 15 different "Customer ID's" to another tab within the same workbook. Let's call this tab "Others." I want the "Others" tab to automatically update each time the "Master" tab is updated.

I'm decent using excel formulas, but I don't think a formula is the best answer here. From the research I've done on this, I need to write a VBA code, but I'm not sure where to start.

If anyone could provide step-by-step instructions on how to write the code for Dummy's, that would be greatly appreciated!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,668
Office Version
  1. 365
Platform
  1. Windows
If you are able to provide some sample data to a third party location, I can demonstrate a solution using "Power Query/Get and Transform Data" with an anti-join. This item is located on the Data Tab of Excel.
 

brs

New Member
Joined
Oct 5, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
If you are able to provide some sample data to a third party location, I can demonstrate a solution using "Power Query/Get and Transform Data" with an anti-join. This item is located on the Data Tab of Excel.
Thanks for your response. Could you provide steps on how to start the query?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,668
Office Version
  1. 365
Platform
  1. Windows
First steps
1. Click in a cell in the range you wish to bring to the PQ editor.
2. On the Data Tab, select Get & Transform and select From Table or Range
3. Click the box or unclick the box for headers
4. The PQ editor will open and you can now manipulate your data.

You will need to load the second table containing the list of valid customers in the same manner. Once both are loaded you can merge (join) the tables using an Anti join.

This might be helpful to look at to start
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,984
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi and welcome to MrExcel.

Do the following:
1. A "Master" sheet with your data, for example:
varios 05oct2021.xlsm
ABCDEFG
1Tit1Tit2Tit3Tit4Customer IDTit6and so on …
2datadatadatadataid 1datadata
3datadatadatadataid 2datadata
4datadatadatadataid 3datadata
5datadatadatadataid 4datadata
6datadatadatadataid 5datadata
7datadatadatadataid 1datadata
8datadatadatadataid 2datadata
9datadatadatadataid 3datadata
10datadatadatadataid 4datadata
11datadatadatadataid 5datadata
12datadatadatadataid 1datadata
13datadatadatadataid 2datadata
14datadatadatadataid 3datadata
15datadatadatadataid 4datadata
16datadatadatadataid 5datadata
17and so on …
Master


2. A "Customer" sheet with the 15 customer IDs that you already know, starting at cell A1, for example:
varios 05oct2021.xlsm
A
1Customer ID
2Id 1
3Id 2
4Id 3
Customer


3. An "Others" sheet

Put the following macro in a module. Updates the data marked in blue in the macro.
Rich (BB code):
Sub FilerOthers()
  Dim dic As Object
  Dim a As Variant, b As Variant, c As Variant
  Dim col As String
  Dim i As Long, j As Long, k As Long, n As Long
  
  col = "E" 'change "E" to the Customer_ID column
  
  Set dic = CreateObject("Scripting.Dictionary")
  n = Columns(col).Column
  a = Sheets("Master").Range("A1:AF" & Sheets("Master").Range(col & Rows.Count).End(3).Row).Value
  b = Sheets("Customer").Range("A2:A" & Sheets("Customer").Range("A" & Rows.Count).End(3).Row).Value
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  dic.comparemode = vbTextCompare
  '
  For i = 1 To UBound(b)
    dic(b(i, 1)) = Empty
  Next
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, n)) Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        c(k, j) = a(i, j)
      Next
    End If
  Next
  
  With Sheets("Others")
    .Cells.ClearContents
    .Range("A1").Resize(k, UBound(c, 2)).Value = c
  End With
End Sub

Result on sheet "Others"
varios 05oct2021.xlsm
ABCDEFG
1Tit1Tit2Tit3Tit4Customer IDTit6and so on …
2datadatadatadataid 4datadata
3datadatadatadataid 5datadata
4datadatadatadataid 4datadata
5datadatadatadataid 5datadata
6datadatadatadataid 4datadata
7datadatadatadataid 5datadata
8and so on …
Others


____________________________
HOW TO INSTALL MACROs
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FilerOthers) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,556
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is another macro that should also work, however, I am not sure if it is faster or slower than the code Dante posted.
VBA Code:
Sub FilterOthers()
  Dim V As Variant, Cust As Variant
  Cust = Sheets("Customer").Range("A2", Sheets("Customer").Cells(Rows.Count, "A").End(xlUp))
  With Sheets("Master")
    For Each V In Cust
      .Columns("E").Replace V, "=""" & V & """", xlPart, , False, , False, False
    Next
    Sheets("Others").Cells.ClearContents
    .Columns("E").SpecialCells(xlConstants).EntireRow.Copy Sheets("Others").Range("A1")
    .Columns("E").Replace "=", "", xlPart
    .Columns("E").Replace """", "", xlPart
  End With
End Sub
 

brs

New Member
Joined
Oct 5, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
First steps
1. Click in a cell in the range you wish to bring to the PQ editor.
2. On the Data Tab, select Get & Transform and select From Table or Range
3. Click the box or unclick the box for headers
4. The PQ editor will open and you can now manipulate your data.

You will need to load the second table containing the list of valid customers in the same manner. Once both are loaded you can merge (join) the tables using an Anti join.

This might be helpful to look at to start
Ok, so I've made it through step 4 where the PQ Editor opens.

I know that I'm going to need to list the 15 customers ID's that I want to exclude, but I'm not sure where or how that should be done. I'm assuming that I will need to do another PQ Editor for those, but how do I get to another tab? Should I select "Close & Load?"
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,668
Office Version
  1. 365
Platform
  1. Windows
Close and Load to Connection and then go to the tab that has the 15 IDs and load that into PQ Editor. Close and Load that to Connection also. Now you have the data in the PQ editor. Putting it in the Connection means that it is in the editor and you have not put it into a new table in Excel which is only clutter as you have the originals already and don't need to replicate them.
 

Forum statistics

Threads
1,148,250
Messages
5,745,638
Members
423,965
Latest member
visionquest1972

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