If The Search Column For A Vlookup Contains A Duplicate Value

0
Vlookup for duplicate values

This articles would be clear your doubt on Vlookup with duplicate values with details explanation. Vlookup can provide many variety of results.

Usually Vlookup would be used to search right side values in the data using left side value. Here left side value as key to search the correspending same value related right side value.

When we have duplicate key values or right side values. Vlookup function returns you first occurance of the value. Please find below image to understand in more detailed

 

If you see the above image, Trump name is there twice and formula written for Trump’s values. Since it is there for twice. It is returning first instance related value. Usually in this case Vlookup does not fit to pull details. If you get as such requirement, you need to slightly modify the vlookup functionality using user defined functions. Sometimes we get requirement to pull all details of key value. This is very rare case. Still we need to know the way how we have to do that. Stay tuned, I will provide you the way how we can create to pull all the duplicate values for key value in vlookup.  Hope you understand vlookup behaviour with duplicate values

 

 

 

 

Why Is My Vlookup Not Working

1
Vlooup #NAME Error
Vlooup #NAME Error

Here you are going to learn the way how we have to fix the issue when your Vlookup is not working. If you would like to learn about VLOOKUP in detailed click here. First you need to the error types that occur in excel. First one is syntactical error and the second one is logical error for any formula these are the main error types occur. Hence first check syntax of the VLOOKUP that you have entered in your sheet. It should be like =Vlookup(lookupvalue, tablearray, columnindexnumber, false)

Lookupvalue: The value which you need to search in particular data range.

TableArray: This would be your lookup value related column to result existed column (Example: if you have lookup value in ‘A’ column and results are in column ‘C’, then your table array would be A:C.

Column Index Number: This is your result column number in the above example, your column index number is 3. Because A is first column, B is second column and C is your result column that is 3rd column.

False: This indicates zero always (that is known as approximate match)

Hence in the above example if we consider, we can get syntax in the below manner

if you are looking result in the same sheet

=Vlookup(A1,A:C,3,0)

if you are looking result in the different sheet then you need to add sheetname in the table. Please find below for syntax for your reference

=Vlookup(A1,Sheet2!A:C,3,0)

If you miss any thing in the above for any scenario, you get an error that would the syntactical error.

Now let’s understand logical error. If you enter syntax correctly still if you are getting error as “#N/A” then your vlookup working correctly. Still you need to check whether you have selected correct column or not in the respective syntax line. For example in the above described example. Instead of A:C , you selected B:C in the table array. that would be logical error and you will not get results. Because you have not specified look value related column in the table array.

If you enter column index number as 4 in the above example, then also vlookup throws an error. Then you need realize that you have not give correct number to lookup the value in the formula. Then your error would be #VALUE.

if you enter 1 in the last block instead false or zero, then also your formula throws an error. This is how you need to figure it out to work your vlookup correctly.

 

How To Delete Duplicates In Excel

0
Deleting Duplicates In Excel
Deleting Duplicates in Excel spread sheet

We have multiple ways to delete duplicate values in excel.  We have remove duplicate option itself in 2007 Excel version or higher versions.  If we need to delete manually in the sense, we have to follow below methods to do this.

1st Method:

  • Sort data either in A to Z or Z to A methodology in the required column
  • Next to that column keep formula = currentcell = Below cell.

(Note: For example if you need to identify duplicate values in the 1st column. Then in the 2nd column, in cell B1 you need to keep the formula as =A1=A2, then drag this till end of the data of the 2nd column)

  • If you get TRUE as result in the cells of 2nd column, those all are duplicates. You can just filter those to delete.

2nd Method:

Assume that you have to find duplicates in the 1st Column, then  just select B1 cell and keep formula  as “=Countif(A:A,A1). Then drag this till ending data cell of 1st Column. If you get more than one as number in the 2nd Column, those all are duplicates. Now you can delete by filtering

3rd Method:

We have direct option as remove duplicates, there you can just select and go to Data Menu -> Select Remove duplicates option to Choose column ->Click Ok. Then also it deletes duplicates.  Here you can use shortcut as Press ALT +A +M to get remove duplicates window after selection data by CTRL+A.

How to find Last Row in Excel VBA

0

There are several ways to find last row in the excel VBA. Finding last row is very important in the Excel VBA programming to work with ranges in spreadsheet. Based on your user or client requirement, you can use the below any one of the method in order to get last row value into one variable. The same can be used in particular worksheet in excel macros.

Usedrange.Rows.Count :

In case if you are going to update data in the worksheet by yourself as part of your automation, you can use this method to get last row of the particular excel spreadsheet.  However this method can be used only when you create new worksheet or created by your program related worksheet.

Example:

Dim Lastrow as long

Lastrow= Activesheet.usedrangerows.count

Or

If worksheet name is Summary then, you can define as shown in the below

Lastrow= Sheets(“Summary”).usedrange.rows.count

Or

Lastrow=Worksheets(“Summary”).usedrange.rows.count

 

Advantages of Usedrange.Rows.Count :

There is no requirement to specify any particular column or column letter to grab last row in this method. You can directly assign to one variable and use the same in the respective worksheet.

Disadvantages of Usedrange.rows.count:

If you use this predefined method in existing input excel spreadsheet, there are chances to get incorrect last row. Sometimes if excel spreadsheet downloaded from web portal or any other source, you get some garbage values in sheet.

Cells(rows.count,columnnumber).end(xlup).row:

 Using this also you can get last row and use the same in entire VBA program for the respective worksheet.  To use this, you should have non blank cells in any one particular column range. Then we can use that column number in this method as shown in the below example

Dim Lastrow as long

(Note: Better to define lastrow as long. It is best practice, because number of rows can be exceeded integer datatypelimit. If you know that rows compulsorily lesser than 32K, then you can go with integer)

I am assuming 1st column has non blank cells within the range)

Syntax: Lastrow=Sheets(“Summary”).cells(rows.count,1).end(xlup).row

Advantages of cells(rows.count,columnnumber).end(xlup).row:

This method always provides you accurate value of last row in particular column range. Hence you can use this in input files in excel macro or VBA.

Disadvantages of cells(rows.count,columnnumber).end(xlup).row:

We have to take care to use non blank cells column. Else there is a chance to get incorrect value into last row.

Range(rows.count,”columnletter”).end(xlup).row:

 In this method also we should have at least with column range with non blank cells.

Dim Lastrow as long

Syntax: Lastrow=Sheets(“Summary”).cells(rows.count,”A”).end(xlup).row

Note: Here I consider column “A” has non blank cells.

How To Become Excel Macro or VBA Programmer From MIS Executive

0

Here you are going to learn the way how you can become VBA Programmer from MIS Executive job. We are going to provide you easy ways to become VBA Programmer and Excel VBA / Macro Programmer Qualities. When you have already became expert in Excel formulas, template and dashboards. Once you learn excel formulas, reports then you are very near to become a Excel Macro or VBA programmer. There are two ways to become a VBA or Excel Macro expert. First one recording macro procedure and second one is Writing VBA coding as per VBA standards. All you need to do initially if you have support, you can start with small project and get help from senior guy. Else you can start small excel activity automation with the help of google. Initially it is very tough to do each and every line of the code. But once you have done each and every thing in that particular small project then you learn many things which repeats in every Macro or VBA program. VBA programs are very tough when you do not start. Once you start all VBA programs become easier for you. This is kind secret behind VBA Program or any other programming language. Keep this in mind to start learning VBA Programming.

How To Become VBA or Excel Macro Programmer from MIS Executive Job
How To Become VBA or Excel Macro Programmer from MIS Executive

Excel VBA or Macro Programmer Qualities:

When you get one activity for automation purpose, first we need to see value added things and non value added things in that particular activity. This is essential task for VBA specialist. And once we identify non value added things then we have to eliminate those things from the activity first by automating those sub processes. Once we do this, then exact process would be remained. And we have to see alternative ways to reduce steps or providing accuracy or creating control checks. Also there is no necessary to write code for every activity, because sometimes without writing code we can automate processes like creating Excel formulas or creating templates or creating some dashboards or any other way. We need to choose simplest way to automate the process always.

As a VBA developer, when we get the automation requirement from any of the team members, we need to identify the Judgmental cases and non judgmental cases in order automate. As of now we have not find any best solutions to automate the judgmental cases. Hence we have to automate all the non judgmental cases. Sometimes judgmental cases would be in between the process. At that time, we need to automate till the non judgmental case ending then we have to stop the middle judgmental case for user to do that manually. Once he completes the judgmental case manually. Rest of the process would have been automated. That’s why user can just run the macro and finish the task.

Judgmental case:

Judgmental case is nothing but thinking ability. Like there will be situations like we have to think and then we need to do that thing according to the input.

Example 1 (Judgmental case):

we have to study the comments and need to do the activity based comment sentences.

Example 2 (Judgmental case):

we have see the image and do the activity based on the image. Here VBA Macro cannot see or think as of now. Hence it cannot be automated.

Examples (Non Judgmental case):

Copy pasting data, repetitive tasks.

Once we automate the process by using Excel macro programming, we need to check for error possibilities to arrest them and keep the control in order to check by user whether the particular task done by macro correctly or not. If we can do this, your programming style would be excellent and reliable macros are being developed by you.

Modifying the Existing Macro:

Sometimes, we need to modify the existing macro which is very tedious task to VBA Developer. Then we need to check the code line by line first and understand the process which was automated by different VBA Macro specialist. Once you we understand the process, then we can respond to associate who has given the task to yourself to do modify the macro.  Based on existing, you need to build the new code in case if it requires, or you just do simple changes to and deliver the project to given person. This is how you need handle the existing macros while modifying.

 

Index & Match Combination (Reverse Vlookup) – Excel Formula

0
Reverse Vlookup ( Index & Match) Combination Excel Formula
Reverse Vlookup ( Index & Match) Combination Excel Formula

Here you are going to learn the way how you can use the Index & Match excel formulas combination (Reverse Vlookup) with examples in order to get the Hlookup, Vlookup outputs and more outputs in excel spreadsheet.

Index & Match combination also known as reverse Vlookup is powerful function in excel  which is very powerful than the Vlookup. Because it has lot of features to prepare excel reports. We can find any column header while preparing report using this function. Vlookup will have only one feature like pulling the right side value based on left side selected value. Here Reverse Vlookup or Index & Match combination can perform the right side value based on left side selected value, left side value based on right side selected value. Hlookup and Vlookup functionalities have been included in this combination function by Microsoft team.

Left Side Value Based On Right Side Selected Value

Example 1:


If you have data with Case, Contact Name, Customer Name in the same order. If you need to pull the Case details with Contact Name or Customer Name, you must use the Index & Match function. Please find below the screenshot to understand the data which is there in the “Index & Match Combination” Sheet

Index & Match Combination (Reverse Vlookup) required Data
Index & Match Combination (Reverse Vlookup) required Data

Now in the Result sheet, you have Contact Names to pull the Case details in the next column as shown in the below screenshot and here itself you can see syntax of the formula in “Result” Sheet

Index & Match (Reverse Vlookup) Excel Function Results
Index & Match (Reverse Vlookup) Excel Function Results

Please find below the syntax to understand the formula that we have used to pull the result sheet case information using the contact name (Left side value based on right side selected value).

Index & Match Combination (Reverse Vlookup) Excel Function Syntax
Index & Match Combination (Reverse Vlookup) Excel Function Syntax

If you see the above, Index Formula syntax has Array, Row, Column properties. In order to find the particular row of the contact name, we have used the Match function within the index formula in the place of row property. When you say array, you need to select from Case column to Contact Name column in the “Index & Match Combination” Sheet as you can see above the Syntax Screenshot. Once you select then you need to use the Match formula in the Row property, then column number can be specified. Here in this case column number is 1 (Case). Now let’s understand Match formula. Match formula is very important excel formula which can pull the matched values based on given input. It has Lookupvalue, Array, matchtype (0,1,-1) properties. We have just used to function within the index to pull the left side value based on right side selected value which is impossible by Vlookup function. Once you arranged the formula in one cell, the same can be dragged till end row of the result sheet data. Once you drag, you can see “case” details which are available in the Array property related sheet here in this case “Index & Match Combination” Sheet. Hope you understand the first example of the Index & Match combination or Reverse Vlookup function in Excel Spreadsheet.

Right Side Value Based On Left Side Selected Value

Example 2:


Now you are going to do the Vlookup formula functionality with Reverse Vlookup function. In order to do that let’s use the same above example related data and get the Customer contact values using Case details. Please find below the screenshot to understand more better way.

Index & Match (Reverse Vlookup) Excel Function Results Right side values pulling based Left side value Vlookup functionality
Index & Match (Reverse Vlookup) Excel Function Results Right side values pulling based Left side value Vlookup functionality

Here we can see the function to understand the Vlookup functionality with Index & Match Combination or Reverse Vlookup. Hence this combination of formula is very strong among all excel formulas. Now let’s understand the syntax using below the screenshot.

Syntax for Vlookup functionality with Reverse Vlookup (Index & Match Combination)
Syntax for Vlookup functionality with Reverse Vlookup (Index & Match Combination)

We have just taken the array property of index formula as “Index & Match Combination” Sheet Column A to Column B as those are related Case & Customer Name. And we just reversed the requirement first Customer Name is there, hence we just need to pull the Case details in the next column. In order to do this again we have used the Match function and in that now Lookvalue property would be Case, array property would be “Case” column in the “Index & Match Combination” Sheet that is 1st column, matchtype property would be 0 (zero) as we need exact matched row. Once it is done, then column of the Index formula would be 2. Because Customer Name column is 2nd Column in the data source. Screenshot is available to understand the Syntax and match with this explanation.

Formulas of above both cases are similar but slide differences. As per first example we just took the 2nd column as base in order to get the left to right and as per second example, we just reversed our requirement 1st column as base and 2nd column as result one. First try to understand properties of the Index formula. Then understand properties of Match formula. That’s it you can play around excel spreadsheet with this formula. As of now you just saw only half part of the Reverse Vlookup. Here let’s summarize the properties meaning in Index and Match formula to get in dept knowledge of this combination formula. Array means data set which has all required columns here in our case 1st and 2nd columns of “Index & Match Combination” Sheet those are Case and Customer Name columns. Row means which is the row do we need to get into the result, columns means which is the columns that we need refer to get the value. This is all about properties of Index formula.

Now let’s know about Match formula properties. LookupValue means the value which we need to look, here in our case Result sheet related Case value. Array means again in the “Index & Match Combination” sheet in which column result is available that particular column alone. Matchtype means it is just exact match or greater than match or less than which are indicating with 0, -1, 1 values. Here in our case we have used only 0 (Zero) for exact matched values.

See Also :

Vlokup – Excel Formula
VLOOKUP Error types 
How To Use If And VLOOKUP Together

Note: Stay tuned as we are going to provide you more examples on this to make you understand better.

How To Search Data Values with Vlookup Function in Excel Spreadsheet

0

Here I am going to train you to learn the way how you can search the data values with Vlookup formula in excel spreadsheet. It helps you a lot when you need to search some duplicate values and all in excel. Vlookup is a valuable function in excel spreadsheet. In fact this function has many traits to deal with excel data easily without any support of other excel functions. Hence I thought of providing you the search trick with Vlookup. It is very important when you dealing with important data or sending essential information to client or your management or to any one else in your organization.

How To Modify Vlookup Function Using User Defined Function

0

Here you are going to learn the way how you can modify the Vlookup functionality, when there is a requirement to do in your Excel report using User defined function technique.

In order to do this, you need to add the module in the excel by pressing the Alt+F11 key or selecting the view menu then select the view macro option.

Once you get the module, you need to specify the function name and functionality to modify the Vlookup according to your requirement. This is a very simple process to excel beginners also. Any excel function can be modified using this method.