Excel VBA - Compare Two Lists and Remove Duplicates
Summary
TLDRThis 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
📊 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.
🔍 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.
Mindmap
Keywords
💡Excel VBA
💡Compare two lists
💡Remove duplicates
💡Worksheets
💡Macro
💡Loops
💡Variables
💡Developer tab
💡Visual Basic for Applications editor
💡Message box
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
Посмотреть больше похожих видео
Excel Visual Basic (VBA) for Beginners - Part 3 of 4: Positioning and Offset
Oggetto Application - VBA Excel Tutorial Italiano 31
Excel Like a Boss: Create Macros with ChatGPT
Cleaning Data in Excel | Excel Tutorials for Beginners
Excel VBA Programming - Variables and Data Types | 1 - Syntax Tips
How to Use ChatGPT and Excel to Automate BORING Tasks
5.0 / 5 (0 votes)