Excel VBA - Compare Two Lists and Remove Duplicates

Summary

This tutorial from computergar.com demonstrates using Excel VBA to eliminate duplicates between two customer lists. The video showcases a practical example with highlighted duplicates for clarity. It guides viewers through the VBA code, explaining the process of identifying and removing matching items. The presenter also offers a downloadable file with the code and mentions an online Excel VBA course for further learning. The demonstration concludes with a macro execution that successfully removes the duplicates, leaving only unique customers in the list.

Takeaways

• 😀 The video tutorial is from computerggar.com and focuses on using Excel VBA to compare two lists and remove duplicates.
• 📚 The demonstration involves two worksheets within a workbook: 'customers one' and 'customers two'.
• 🔍 The script explains that the duplicates are already highlighted in the demonstration for clarity, but this step is not necessary for the process to work.
• 💡 The video provides a link in the description to download the VBA code used in the tutorial, allowing viewers to learn and adapt the code for their needs.
• 🎓 A link to an online Excel VBA course is also provided for those who want to learn more about automating Excel procedures.
• 📈 The tutorial begins by declaring variables for worksheets, row numbers, and tracking duplicates.
• 🔄 The process involves looping through the first customer list and checking against the second list to find and remove duplicates.
• 🔍 The script uses a 'do while' loop to iterate through the first list and a nested loop to search for matches in the second list.
• 👷‍♂️ When a duplicate is found, the corresponding row in the first list is removed, and a count of duplicates is updated.
• 📉 The script accounts for the shifting of row numbers when a row is deleted by adjusting the row number accordingly.
• 📣 At the end of the process, a message box confirms that the duplicates have been removed, demonstrating the successful execution of the macro.

Q & A

What is the main purpose of the video tutorial?

The main purpose of the video tutorial is to demonstrate how to use Excel VBA to compare two lists and remove duplicates.

What are the two worksheets in the workbook used for?

The two worksheets, 'customers one' and 'customers two', are used for identifying and removing duplicate customer entries from 'customers two'.

Why are the duplicate customers highlighted in color in the demonstration?

The duplicate customers are highlighted in color to visually indicate what to expect from the video and what the ultimate result will be, but this is not a requirement for the VBA code to work.

What does the provided macro code do?

The macro code provided is designed to automate the process of finding and removing duplicate entries between the two customer lists in an Excel workbook.

How can viewers access the VBA code used in the tutorial?

The VBA code file is made available via a link in the description of the video, allowing viewers to download, learn from, and use the code.

What additional resource is mentioned in the video description for those who want to learn more about Excel VBA?

An online Excel VBA course is mentioned in the video description, which can be accessed for a fee of $49 for those who want to learn more about automating Excel procedures.

What are the key elements of the VBA code discussed in the tutorial?

The key elements of the VBA code include variable declarations, loops for iterating through the lists, conditional statements for finding matches, and a message box to indicate the completion of the duplicate removal process.

How does the VBA code handle the removal of duplicates?

The VBA code uses an external loop to iterate through the first list and an internal loop to search for each entry in the second list. If a duplicate is found, the corresponding row in the first list is removed, and a counter is adjusted to account for the change in row numbers.

What is the significance of adjusting the row number with a minus one operation after removing a duplicate?

The minus one operation ensures that after a row is deleted, the code does not skip the next row in the list, as the subsequent rows move up to fill the gap left by the deleted row.

How does the tutorial demonstrate the successful removal of duplicates?

The tutorial demonstrates the successful removal of duplicates by running the macro and showing that the highlighted entries in the 'customers one' list are no longer present after the macro execution.

Outlines

00:00

📊 Excel VBA Tutorial: Removing Duplicates

This video tutorial from computer ggar.com introduces viewers to the process of using Excel VBA to eliminate duplicate entries from a list. The demonstration involves two worksheets within a workbook, labeled 'customers one' and 'customers two.' The presenter has marked duplicate customers in 'customers one' for clarity. The video provides a step-by-step guide through the VBA code necessary for the task, which is available via a link in the video description. The code includes variable declarations for tracking row numbers and identifying duplicates. The main focus is on the looping structure, which iterates through the first list to find and remove duplicates in the second list. The video concludes with a demonstration of the macro in action, showing the removal of highlighted duplicates and a message box confirming the completion of the task.

05:01

🔍 Detailed Explanation of VBA Code for Duplicate Removal

In this paragraph, the presenter delves deeper into the VBA code used for removing duplicates from an Excel list. The explanation covers the initialization of variables, the purpose of each, and the logic behind the looping structure. The external loop iterates through the first customer list, while the internal loop searches for matches in the second list. When a duplicate is found, the corresponding row in the first list is removed, and a counter is adjusted to account for the change in row numbers due to deletion. The presenter emphasizes the importance of not moving to the next row after deletion to avoid skipping any entries. The video also mentions a message box that appears at the end to notify the user that duplicates have been removed. The presenter encourages viewers to follow the link in the description to access the file and use the provided code, suggesting further learning through an online Excel VBA course for those interested in automating procedures.

Keywords

💡Excel VBA

Excel VBA (Visual Basic for Applications) is a programming language used to automate tasks in Microsoft Excel. In the video, Excel VBA is used to compare two lists and remove duplicates, streamlining the process of managing customer data in Excel.

💡Compare two lists

Comparing two lists involves checking two sets of data for matching entries. In the video, the tutorial shows how to use VBA code to compare customer lists in two worksheets and identify duplicate entries that need to be removed from the first list.

💡Remove duplicates

Removing duplicates refers to eliminating repeated entries from a dataset. The video demonstrates how to use VBA code to identify and delete duplicate customer records in one Excel worksheet based on comparisons with another worksheet.

💡Worksheets

Worksheets are individual pages within an Excel workbook where data is stored and manipulated. The video uses two worksheets, 'customers 1' and 'customers 2,' to illustrate the process of comparing lists and removing duplicates using VBA code.

💡Macro

A macro is a set of instructions that can be triggered to perform a specific task automatically in Excel. The video features a macro designed to compare customer lists and remove duplicates, showcasing the practical application of VBA in automating repetitive tasks.

💡Loops

Loops are programming constructs that repeat a block of code multiple times. In the video, loops are used in the VBA code to iterate through customer lists and check for duplicates. The 'do while' loop and 'for' loop are specifically mentioned.

💡Variables

Variables are used in programming to store data that can be referenced and manipulated. The VBA code in the video declares several variables to track worksheets, row numbers, customer IDs, and the number of duplicates found, facilitating the comparison process.

💡Developer tab

The Developer tab in Excel provides access to advanced features like VBA and macros. In the video, the instructor accesses the Developer tab to open the Visual Basic for Applications editor, where the VBA code is written and executed.

💡Visual Basic for Applications editor

The VBA editor is a tool within Excel where users can write, edit, and run VBA code. The video shows the instructor using the VBA editor to explain and execute the code that compares customer lists and removes duplicates.

💡Message box

A message box is a dialog box in Excel VBA that displays information to the user. At the end of the VBA script in the video, a message box appears to notify the user that the duplicates have been removed, enhancing the script's interactivity.

Highlights

Introduction to using Excel VBA to compare two lists and remove duplicates.

Demonstration with two worksheets named 'customers one' and 'customers two'.

Color coding is used for demonstration purposes to identify duplicates.

Customers like Lawrence, Philipe, Jose, and Rita are marked as duplicates in 'customers one'.

Customers 2 list is larger and contains duplicates.

Macro code is provided to automate the duplicate removal process.

Link to the macro file and Excel VBA course will be provided in the video description.

Explanation of the initial variable declarations in the VBA code.

How the VBA code determines the number of customers in the second list.

Description of the external loop that iterates through the first customer list.

Explanation of the internal loop that searches for duplicates in the second list.

Process of identifying and removing duplicates when found.

Adjustment of row numbers after removing duplicates to continue the search.

Final message box indicating the completion of duplicate removal.

Demonstration of running the macro to remove duplicates in 'customers one'.

Result of the macro showing the removal of highlighted duplicates.

Encouragement to check out other video tutorials and the YouTube channel.

Transcripts

hello and welcome to this video tutorial

from computer ggar.com in this video we

are going to look at how to use Excel

VBA to compare two lists and to remove

any duplicates any matching

items so I have two worksheets of this

workbook two customer sheets this is

customers one and this is the one that I

want to remove the duplicates

from and just for the ease of this

demonstration I've changed the color of

the duplicate customers so don't be

misled here you don't have to change the

color of them for this to work I've only

done that so that we know what to expect

from this video and what the ultimate

result will

be so that is Lawrence Philipe Jose and

Rita now if we look at customers 2 it's

a slightly larger list so those four

customers I just mentioned are in this

list somewhere there's R for example

they're in this list somewhere they are

the duplicates there's

Jose so they're the ones I'm going to

remove anyone else must be a new

customer I guess in this pretend

scenario okay now the macro code we're

going to need is already been written

I'm just going to pop over there now and

going to talk you through the key

elements of it so let me click the

developer tab on my ribbon and Visual

Basic on the far left there and this is

our VBA code now I'm going to provide

this file via a link in the description

of this video so check out the

description below I'm going to have a

link to this exact file so you can come

and you can take this code play around

with it learn from it use it

uh whatever you wish it's going to be

made available I'm also in the

description of this video going to put a

link to my online EXL VBA course which

you can get for just

$49 so if this is a little bit much for

you but you really want to learn Excel

VBA to automate your procedures like

this then that's also worth checking out

and uh we'll learn that together get

through that together okay this is the

code for now so I've declared a few

variables at the top uh two for the

worksheets customers one customers 2 two

to track the row numbers customers one

customers two one to identify how many

customers are in that second uh

list and then I've got uh one to track

the ID I'm currently searching for and

one just to report back how many

duplicates so what we've got here we've

got the initial kind of Declaration and

stuff here this one finds out how many

customers are in the second list so the

one that I'm kind of checking against

and then we start off our Loops which if

you've done some Excel VBA you knew that

there would be Some Loops involved in a

task like

this we have the external Loop which is

this do while loop you see the words do

while and then loop at the bottom this

our external Loop this one is looping

through the first let me uh shrink this

a little bit this first customers list

so starting from Maria then frederique

then Lawrence then Peter and so on and

so forth that's the one that's switching

between those so initially it assigns

the ID of the first person Maria that AF

Alf Ki assigns it to this variable and

then uses that to check against it

it jumps into the second Loop customers

2 so it switches over to this

sheet and starts from row

two which is way up here and starts

looking for Maria in that scenario so we

start from that custom ID and start

looking for each sale looking for

Maria if it finds Maria then the first

sheet is activated the row is removed

uh a note is made that one was removed a

duplicate was found and I've just done a

minus one on this row number now the

reason for that is that you know on this

first worksheet as we're looking for

each person once we've of found or not

found Maria we need to move to

frederique we need to move to Lawrence

but in a case of when we find Lawrence

that row is going to be

deleted and whenever you delete a row in

Excel the other rows are going to move

up so Peter Franken in row five will

move into row

four so when it comes to removing

somebody I don't want to add a new row

on which is this line down here like

okay they're not in the list move to the

next row I don't want to move to the

next row because if I've deleted it the

other row is going to move up into that

place so I'll just kind of put a

negative so when it gets to here and

adds it on it effectively counteracts it

it doesn't it doesn't increase in row

numbers that increases the active sheet

and that exits the four Loop down here

so it comes out of this Loop really for

the next customer to look

for now I know I'm kind of shooting

through this doing some

explanation uh so you know if you've

done some VBA before this will be

sufficient for you and you'll understand

a bit of what I'm saying and now I'm

just trying to explain the key bits if

not this video is probably going to be a

bit much which why I recommend the the

course in the description of this video

or to to kind of understand a little bit

about variables and about

looping um so this video will make a

little bit a kind of sense for you

essentially though if this is something

you need to do just follow the link in

the description to get this file and you

can take and use this code uh it should

be ready to adapt for your needs to an

extent as long as the kind of structure

of it is similar to what I have right

now at the end here just have a message

box telling me that the duplicates have

been removed so let's see this in action

I told you there were four

duplicates if I go to my macros button

here's my macro ideally I'd have a

button to click but I want to

demonstrate using it this way and if I

run this

macro it simply removes those four would

be helpful actually if I went back to

the other sheet looking at this tells me

four duplicates were removed I click

okay I go back to customers one and

those ones that are highlighted in that

kind of orange color are no longer there

so the job has been done they have been

removed I hope you enjoy this video

please check out some of our other video

tutorials on our YouTube channel come

check us out at computer.com