Day: 21 April 2022

Excel Duplicate Finder Using VLOOKUP

enabling excel macros

A common problem when working with data in Microsoft Excel is working out how to remove duplicates. In case you’re still looking, this is where the VLOOKUP function (vertical lookup) helps you find duplicate cells.

The tool ‘searches’ for a value declared by you in a column, and then returns a value from a different column in the same row.

How To Use VLOOKUP TO Find Duplicates

To use VLOOKUP, simply type =VLOOKUP() in the formula bar. You will need to enter your ‘lookup value’ between the brackets (parentheses) followed by a comma. This can be an actual value, or a blank cell that will hold a value, for example:

=VLOOKUP(H2,) – we’ve chosen to use cell H2.

Now you should enter your table array, or lookup table, the range of data you want to search, plus a comma. For example:

=VLOOKUP(H2,B3:F25,) – So, we have our H2, which can either hold data or be blank, and now we’ve included the range of data we want to search for duplicates, i.e. B3:F25.

So, we now need to enter a column index number. This will be a column to the right of your data where you think the answers are. Our VLOOKUP formula should now look like this:

=VLOOKUP(H2,B3:F25,3) – the 3 denotes column 3 where your answers are located.

The final part to complete a simple VLOOKUP is to determine whether we want to find a partial match (FALSE), or an exact match (TRUE). If we opt for an exact match, are VLOOKUP formula will look like this:

=VLOOKUP(H2,B3:F25,3,TRUE)

Excel-Duplicate-Finder-

Why We Love VLOOKUP

Simply put, think of VLOOKUP as using a phone directory that has over 1 million entries. You know the person’s name (data you know), but not their phone number (data you don’t know).

You can use VLOOKUP to search the data using what you already know (the person’s name), and then the VLOOKUP function will display something you don’t know – the person’s phone number.

Of course, you then need to tell VLOOKUP where to display that data!

So now you know how to perform a simple VLOOKUP, how do you use this tool to find and even replace duplicate values?

We’ll show you this next.


NEED HELP WITH AN EXCEL PROJECT, BIG OR SMALL?

The Excel Experts can help you or your business complete an unfinished project, integrate web applications, automate tasks, and much more.

image


    Find & Remove Duplicate Values

    One of the great things about VLOOKUP is that you can use it to find and remove duplicate data values, ensuring that your information is more accurate and that there’s no crossover

    It’s quick and easy to create a formula in Excel to identify duplicate data across a spreadsheet. Once you’ve identified duplicates, you can easily remove them, either by manually deleting them or by replacing them with fresh data. As you can find where the duplicates are in the data, you can also identify their source, which makes it easier for you to improve your spreadsheet and ensure that any mistakes don’t happen again. 


    Excel Basics Book

    Check out this excellent Excel Step-by-step Guide to the basics of Microsoft Excel from Kevin Clark.


    Using VLOOKUP To Find Duplicates

    With VLOOKUP it’s easy to find duplicates in your data. If you’re using one sheet, you can easily identify duplicates by adding an extra column to the end of your table. 

    Name each list simply, for example, list 1, list 2 etc. 

    Then you need to create a simple formula and add this to the blank column at the end of the table. 

    So, if we only had two lists, we would create a formula similar to the one below:

    =VLOOKUP(List1,List2,TRUE,FALSE)

    You then need to drag down, so that the formula runs across the entirety of each list you’re checking. 

    When you press enter, the formula will identify any duplicate data between the two lists and place it in the third column. 


    Microsoft Excel Udemy Course – Excel from Beginner to Advanced 2023


    Using VLOOKUP To Remove Duplicates

    Once you’ve identified duplicates in your data, you can remove them using VLOOKUP. This is useful for when the duplicates aren’t required and were a mistake. 

    All you need to do is click on Sort & Filter, then select the Filter function.

    Once you do this, a dropdown menu will appear at the top of each column. 

    Click on the dropdown for the duplicate column, and then select all and click N/A.

    Your duplicate values will then be replaced with the phrase N/A throughout the workbook, and you can then search the sheet to remove all N/A data or leave it in to highlight where duplicate data was included.

    Use-VLOOKUP-TO-Find-Duplicates

    Conditional Formatting To Highlight Information

    If you want to highlight specific information if it conforms to a rule, then you can do this by combining VLOOKUP and conditional formatting. 

    Conditional formatting allows you to set a rule, and then highlight data within your Excel workbook that meets your criteria. 

    To do this using VLOOKUP, you need to select the data you want to run your conditional formatting on, then go to Conditional Formatting under Home. 

    Go to New Format, and select Use a formula to determine which cells to format in the Select a Rule Type list box

    Then, enter your formula, which you can create depending on the function you want it to have. Include VLOOKUP at the start, then add your formula in brackets, outlining the rule and data involved. 

    Click on format, and then go to fill and select a colour so that you can easily see the cells that meet your criteria. 

    Then, you’ll be able to apply your formula and see which cells contain data that meets your needs.


    Excel Do’s and Don’ts: How To Make Your Spreadsheets Shine!


    How To Duplicate Rows

    When you’re applying a VLOOKUP formula, it’s possible to apply it to duplicate rows in your Excel worksheet. 

    To do this, you need to simply copy and paste the same formula to the new section of the worksheet, adjusting the target cells to suit. 

    Then you’ll easily be able to apply your VLOOKUP formula to multiple rows and use it for several data points. 

    This approach only applies if you’re keen to use the same formula: if you need to make a new one, then you can create it using the tips in this article. 

    Search & Find Duplicate Data

    Duplicate data can crop up in even the most well-maintained of Excel sheets, but if you don’t want duplicates, then it’s easy to find and identify them. 

    Using VLOOKUP, you can quickly search your data sheet for duplicate values and find them, then remove or analyse them, depending on your preference. 

    If you have two lists, named 1 and 2, and need to check for data across each, this is easy. Just create the formula =VLOOKUP(List-1, List-2,True,False) and add it to a third column. 

    The List-1 data will be searched in List-2. If there are any duplicates, then these will be listed in the third column where the formula was placed.

    With this duplicate data in your third column, you can understand the workings of your datasheet and eliminate the duplicate data if you want to. 

    How To Find Case-sensitive Duplicates In Excel

    If you want to find a case-sensitive duplicate, then it’s possible to update your VLOOKUP formula to account for this. 

    A case-sensitive duplicate is one that is the same word as another but has been written partially or fully in capital letters. So, for example, the words EXCEL, excel and Excel are all the same term, and might refer to the same program, but VLOOKUP won’t automatically identify them as duplicates as it is not case-sensitive. 

    As such, if you want to identify all the duplicates in a data set, no matter what case they are, you need to either create a helper column or by adapting your formula. Both approaches are similar in that they both include semi-colons to make the formula account for capital letters. 

    How To Count Duplicates In Excel

    If you want to count duplicate examples in Excel, then you can use the COUNTIF function and create a formula and place it in an additional column on the end of the workbook, such as:

    =COUNTIF($C$2:$C$8,E2)

    All you need to do then is highlight the cells you want to count, and the formula will give you numerical data on how many duplicates there are for each piece of duplicate data. 

    This formula can be used in conjunction with VLOOKUP and will count how many instances of duplication are included for each value that VLOOKUP identified. 

    Conclusion

    Duplicates are bound to crop up in any data, and whether you want to know where they are to find and assess them, or you want to get rid of them, VLOOKUP can help. 

    This useful tool can make the process of identifying duplicates in your data and eradicating them, if required, simple and straightforward. If you’re still not sure about how to make the most of VLOOKUP after reading this article, then consider using the Excel Experts’ Excel consultancy services

    FAQs

    Why does Excel not recognise duplicates?

    Excel does not recognise duplicates in raw data as it assumes that the user who made the sheet added the duplicates in deliberately. However, if you need to identify and remove duplicates that are in the data by accident, Excel has handy tools to help, incuding VLOOKUP. So, you get the best of both worlds with this versatile tool. 

    How do I filter duplicates in Excel?

    To find duplicates in Excel, you can use VLOOKUP to create a custom formula. If you have two lists, 1 and 2, and need to check for data across each, you use the formula =VLOOKUP(List-1, List-2,True,False).

    The List-1 data will be searched in List-2. If there are any duplicates, then these will be listed in the third column where the formula was placed. 

    How to find duplicates across multiple Excel sheets?

    Finding duplicates across multiple Excel sheets using VLOOKUP is similar to doing it in one sheet, only you’ll need to desginate each sheet a name, and then include this name in the VLOOKUP formula. 

    How to find Excel duplicates, but ignore blanks? 

    If you’re using the VLOOKUP tool to find and remove duplicates, but want to keep any blank cells, then you’ll need to find a blank cell next to the data you’re removing duplicates from, and type the filter =A3=A2. Drag down to autofill this next to every cell you’re removing duplicates from, then select the entire data range, including the formula cells. Go to Data, then Filter, then enable the filter function. 

    How to find duplicate email addresses in Excel?

    To find and remove duplicate email address data in Excel using VLOOKUP, you can use the same technique as mentioned above. 
    For more tips and tricks about how to use VLOOKUP to identify and remove duplicate data and more, follow our regularly updated blog.


    NEED HELP WITH AN EXCEL PROJECT, BIG OR SMALL?

    The Excel Experts can help you or your business complete an unfinished project, integrate web applications, automate tasks, and much more.

    image

      You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂

      Learn How To Convert XML To Excel In Minutes

      what-is-a-macro-enabled-workbook

      XML is a great way for systems and apps to work with, but it can be difficult for people because we don’t understand XML data very easily.

      Excel, on the other hand, is a popular spreadsheet application which is designed to make working with data much easier for humans. So converting XML data into Excel makes sense!

      There are two main ways to convert XML files into an Excel spreadsheet. One is to use a converter tool, or by manually editing the file. 

      Converter Tools

      Converter tools are pieces of software which can take an XML file and turn it into another format – in this case, an .xls or .xlsx file which can be opened in Excel.

      The advantage of using a converter tool is that it’s usually much faster and easier than doing it yourself. All you need to do is download the converter, open your XML file, select the output as an Excel file, and then click convert. 

      Your file will be ready in a few seconds or minutes, depending on its size.

      Manually Editing The File

      The other way to convert XML to Excel is by manually editing the file. This is a more complex process, but it gives you more control over the final outcome.

      To do this, you’ll need to open the XML file in a text editor (we recommend using Notepad++)

      Once you have the file open, you’ll need to find and replace all of the tags with commas (which can be cumbersome), for example, if your XML file looks like this:

      [insert example here]- this will presumably require a screenshot, we don’t provide these for standard content

      You ‘ll need to replace all of the “<” symbols with commas, so that it looks like this:

      [insert example here]- this will presumably require a screenshot, we don’t provide these for standard content

      Then you can save the file as a .csv file, which can be opened in Excel.

      Manually editing an XML file is definitely more complex than using a converter tool, but it does give you more control over the final outcome!

      What Is An XML File?

      Extensible markup language (EML) is a file that can be used for storage and transportation purposes. The file consists of tags and text. Tags give structure to the data stored in the file, while the text is the core part of the document.

      Take a look at the example below:

      <employees list>
      <employee id=”1”>
      <FirstName>Molly</FirstName>
      <LastName>Copoc</LastName>
      <certificate>True</certificate>
      <scores>
      <module1>50</module1>
      <module2>70</modeul2>

      The file contains tags and texts. The plain text is the data that is being stored, while the tags indicate what the data is. This example shows the XML is storing employee names and their test scores.

      What-Is-An-XML-File
      What-Is-An-XML-File

      XML Data Types

      A data type is an attribute of data that informs a computer system how to interpret its value. There are many different XML data types, and all have different values. Take a look at some examples below:

      Boolean

      Boolean, also known as Bool, is used to represent logical values. The two values it usually denotes are true or false. Boolean expressions use common operators, which are simple words like OR, AND, XOR, and NOT as conjunctions. These words are used to either combine or exclude words in a keyword search so users can access results that are more focused and productive. 

      Integer

      Integer is a data type used for whole numbers and is commonly used to store numbers without a fractional component. Examples of this data type include 33, 21, and 55. The whole number can be zero, negative, or positive. So, -456 would be valid integers. However, 4.2 would not as it is not a whole number. 

      Decimal

      The decimal data type is exact numeric data defined by its total number of digits and the number of digits to the right of the decimal points (precision and scale). You would tend to use this data type if you wanted to store or complete operations on precise decimal values. 

      String

      String, also known as str or text, is used for alphanumeric characters. It is a popular data type that is used in programming. However, it represents text rather than numbers. A string is a set of characters that contains characters and often numbers too. For example, the word “cake” and the phrase “I baked 3 cakes” are both string data type examples. 

      Duration

      This data type represents the difference between one point in time to another. It is intended for use with time durations measured in seconds, minutes, hours, or days. 

      DateTime

      DateTime stores a value that contains both date and time parts together. It allows programmers to store dates from 01/01/1753 to 31/12/9999 and time information from 00:00:00.000 to 23:59:59.997. It is ideal if you want to serve your data together regardless of different time zones. 

      Date

      This data type is the most self-explanatory of the group. It stores a date in the following format – YYYY/MM/DD. 

      Time

      This stores time in an hours, minutes and seconds format. This data type can also be used to store time intervals between two events. 

      How-To-Convert-XML-To-Excel
      How-To-Convert-XML-To-Excel

      Importing An XML Data File

      • Open the Excel file that you want to import your XML data into then select the “data” tab.
      • Click on the “Get Data” section. A list should appear. 
      • Click the “From file” option and from there click on “From XML”.
      • An input data dialogue box will open up. This is where you will locate your XML file that you wish to import. Once you find it, select the file.  
      • As you click import the file will import into the power query and the Navigator dialogue box will open.
      • Select the data that you wish to import from your XML file. 
      • Click on load and you are finished. 

      How To Convert XML To An Excel File

      • Open Microsoft Excel and click “File” on the top left of the screen. 
      • Click open to view your files and double click the XML file you wish to convert. 
      • Again, click “File” and select the “Save as” button. 
      • Find the file in the folder you have saved it in. 
      • Click on the file and select “Excel workbook” from the “Save as type” in the drop-down menu.
      • Once you click save your XML file will now be converted to an Excel file. 

      FAQS

      How To Convert XML To An Excel Table?

      Click on ‘File’ and open the Excel document you want to import your XML data into; make a new one if required. Click the Data tab, then go to the Get & Transform group, where you click on the ‘Get Data’ option. From there, you need to go to the ‘From Other Sources’ option and click ‘From Web’ In the ‘From Web’ dialog box, copy and paste the URL that has the XML data, and you’ll then be able to import that data into your Excel table and use it. 

      How To Convert XML To Excel On MAC?

      Click “File” and select “Open”. Locate your XML file and click “OK”. Once the file opens, you need to click on “File” again and choose “Save as”. After typing in a new name for your file, you will need to click “.CSV” in the file pop-up menu. Once you have done this, click “Save”.

      Convert XML To Excel Using Powershell?

      First, you need to use the XML cmdlets to import and parse your XML file. You can do this by downloading and using one of the many XML modules from the MS PowerShellGallery.com.

      Convert XML To Excel Python?

      It is possible to convert XML to Excel Python: you’ll need to make sure that you have all the software you need to make the conversion possible. You can then run the Python code that converts XML spreadsheet to Excel in your chosen software. You can learn more about selecting software for Excel by checking out our blog.

      9 Power Bi Tips to Find Key Insights For Your Business

      dashboards in power bi

      When it comes to business intelligence, Power BI is one of the most popular tools on the market. And for good reason – it’s incredibly powerful and versatile, and can help find key insights into your data that you might otherwise miss. 

      But Power BI is also complex and can be overwhelming for some users who are new to business intelligence, or data visualisation. For example, Power BI reports can be confusing when working with multiple visuals, such as pie charts, and data sets or data analytics. 

      To make the most of Power BI, it’s important to understand how to use the tool to its full potential. From understanding key metrics, working with data types and data modelling, you should know how to use Power BI to your advantage. 

      If you want to learn more about Power BI, the Excel Experts have years of experience in this field. We have drawn on that expertise to offer eight invaluable Power BI tips and tricks.

      Get To Know The Power BI Interface 

      One of the great qualities of the Power BI interface is that it is user-friendly. There are three main building blocks to the Power BI interface: your dashboard, reports, and datasets. You can connect as many datasets to the Power BI interface as possible. Upon entering the interface, you will be greeted by your dashboard. #

      Your dashboard will display a range of visualisations from your many datasets. The interface is equipped with drag-and-drop capabilities and preloaded templates that you can use to build your custom reports. Accessing data has never been so easy! Click here to learn more about the Power BI Interface over on our blog. 

      Power BI Dashboard Tips 

      Power BI continues to evolve each year. So, in order for you to get the most out of this software, you must understand how to use each feature effectively. These dashboard tips below will help you stay one step ahead. 

      • Tip 1 – Avoid using multiple features at once. This makes your dashboard less user friendly and you will also find that it slows it down. 
      • Tip 2 – You should always take your audience into account. Only give them the relevant information that will help them make decisions. Consider the device they are using the dashboard on. If it’s being viewed on a smaller device like a laptop, use less tiles as this will make the information easier to read.
      • Tip 3 – Think carefully about the page layout. Ideally, you will place the most important data at the top left of the screen as most people read left to right. 

      Take a look at some dashboard examples to give you some inspiration. A user-friendly dashboard that uses minimal features is the key to success. 

      Power-Bi-Tips

      Power BI Bookmarks 

      You can create report bookmarks in Power BI to share insights and build stories. Bookmarks capture the current state of the report page. So, when others view the report, you can still revisit it, and it will remain in its original form. If you want to create a bookmark, start configuring your chosen report page. Select “Add” from the Bookmarks pane and give it a name. 

      Three Examples of Power BI Bookmarks 

      • Report Bookmarks  
      • Use Bookmarks As A Slideshow 
      • Assign Bookmarks To Buttons  

      Enabling Tooltips 

      A tooltip demonstrates contextual data in the form of a pop-up when you hover over or click on a chart that is presented on a report in your dashboard. If you want to manually set up a tooltip, you will first need to select the visual that you want to specify – usually, this would be a chart. Select “Format” and expand the tooltip card in the visualisations pane – finally, select the tooltip page in the Page dropdown. 

      5 Popular Tooltips 

      • Share context for numeric using tooltips  
      • Include an instruction for workbook users 
      • Assign tasks using tooltips 
      • Explain a formula or calculation with tooltips  
      • Highlight anomalies in data and explain them 

      Using Power Pivot 

      Integrated into Power BI, Power Pivot is used for importing and shaping data. It takes huge volumes of data from various places and analyses the information very quickly. After analysing the data, Power Pivot processes it and offers valuable insights.

      Power Pivot has many uses. As well as performing robust data analysis, it creates sophisticated data models – and writes advanced formulas using data analysis expressions language. 

      Using-Power-Pivot-

      Creating Reports in Power BI 

      Understandably, you want to get the most out of your data when using Power Bi. Below are three top tips to assist you in creating reports. 

      • Tip 1 – Choose the correct dataset. Identify what you are trying to show your audience from your report. Failure to choose the right data can make your report redundant.
      • Tip 2  – Highlight the purpose of your report. Why are you creating it? What questions are you trying to answer? This will help you find the right audience and assist you in choosing the right information to include.
      • Tip 3 – Choose how you will visually represent your information. Some popular visuals include Comparisons, Correlation, Part-to-whole, Data Over Time, Distribution, and Ranking. 

      Power BI Cross-filtering Techniques 

      Cross filtering in Power BI provides interactivity among visuals on the page. They allow report makers to encourage interaction from their audience. The visuals you use in your reports will react to the user’s selection of other visuals on the page. Below are three examples of Power BI cross-filtering techniques: 

      • Highlight holiday days due to be taken by selecting a specific name  
      • Adjust prices to suit a selection made by the user
      • Show stock numbers of selected products 

      Exploring Data Analytics Using Hierarchies 

      Hierarchy visualisations have many uses, from powerful visualisations to improving data science productivity. You can visualise hierarchical data using Power BI such as datasets that contain records with multiple subcategories. If you want to create Power BI visualisations with hierarchies, there are two ways you can achieve this: Manual hierarchies and Automatic hierarchies. You can learn more about this by getting in touch with one of our Power BI consultants over on our website. 

      Power BI Desktop 

      Power BI Desktop integrates the proven Microsoft query engine, data modelling, and visualisation technologies. It is a free application that allows users to connect multiple pieces of data together – a term called data modelling. It differs from Power BI as the data that is visualised cannot be shared with others. 

      Typically, most users will use the Power BI desktop to create visuals that will be shared with a broader audience using the Power BI service. Power BI desktop has many great uses. It can connect to data, create data models, create reports using visuals, share reports using the Power BI service and allow the user to provide visual representations of data. 

      FAQs

      Is Power BI good for analytics? 

      Power BI is a fantastic tool for analytics. It can help your business generate quick analytics and insights regardless of how big or small your business may be. 

      How to make the Power BI dashboard faster? 

      You can speed up your dashboard by following some simple steps. Try to remove anything that is not used and use integers where possible. Using too much data and graphics will naturally slow your dashboard down. 

      Turn off snapping in Power BI? 

      This feature cannot be turned off, but they are useful for anyone who uses Power BI. If you want to learn more about these features, check out our blog or contact us.