Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VLOOKUP With Duplicates

I'm looking for a formula solution to return the result of names even through the names may appear more than once in a list. Below is some sample data, example I would want to look up say Noah and return 2 results as Noah appears twice in the list, result 1 would be 333 and result 2 would be 200. VLOOKUP will only return the 1st instance...

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Vlookup Where Duplicates Occur
i need to delete rows from sheet2 which contains source data for Vlookup on sheet1.

that is i need to run a vlookup for a value in sheet1 and after the value is obtained , delete the row which has that value in the source data......

i am sure a simple for loop will help but it needs to do the events in sequence:
vlookup for row1> fetch / place value (sheet1) > delete data in the source table (worksheet2) > go back to row2 and repeat till the the last.. or till the end of a predefined range..

View Replies!   View Related
VLOOKUP With INDIRECT (become Dynamic As The Table Array Part Of The Vlookup Will Change)
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.

So the basic vlookup is as follows:
but the data I am looking for wont always be in the range M60:P73.

So I tried to make it dynamic by doing the following:
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73

This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.

View Replies!   View Related
Hyperlinks And VLookup: VLookup To Find EMail And Web Addresses
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?

View Replies!   View Related
Vlookup Across Sheets, Nested Vlookup Possibly?
Iím trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that monthís interest payment. Can anyone help me figure this out?

The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. Itíd also be great if the formula can be transferred between workbooks. Iím not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.

View Replies!   View Related
Double VLookup (vlookup The Same Data From 2 Different Sheets)
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.

View Replies!   View Related
Using Vlookup & Indirect To Ref List And Vlookup Files
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.

This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.

I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:

View Replies!   View Related
Vlookup Looping (for Vlookup Loop Technique
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:

View Replies!   View Related
Add Duplicates
The data is copied daily to load and duplicates are removed and new entries are copied to list sheet, by searching on the concatenation.

The problem i have is that i also want to add to the list, the row lines from the load sheet if the date changes in column G. Not really sure how yo approach this. I have attached sample data.

View Replies!   View Related
How To Keep Only 10 Duplicates
I have a bunch of phone numbers in a row and I want to eliminate duplicates of 10 for a given number. For example - 5556839898 may appear 15 times, I want to delete the 5 extra instances of that number, leaving me with a maximum of 10 duplicates. This is for an SMS voting application where the entries where supposed to be limited to 10x.

View Replies!   View Related
I have 2 columns, one has UPC which are sometime duplicated but the number associated with it in the next column could be the same or different. I need to identify which duplicate UPCs have different numbers, if they are the same I don't really care.

01030088834524need to identify
01116101270212don't care
0111610141268need to identify

View Replies!   View Related
Vlookup Error Msg "unable To Get The VLookup Property Of The WorksheetFunction Class"
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.

I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?

View Replies!   View Related
Duplicates In Two Columns
I have a list of numbers in column A of my Worksheet, and a list of names in column B.
There are duplicates in both columns:

234 John Smith
253 Charles White
461 Mary Carlsson
876 Erica Alvin
954 Joe Brown
234 John Smith
461 Mary Carlsson

The duplicates in column A and in column B are correct, because the same names correspond to the same numbers. I would like a formula or VBA macro, if possible, to detect when there is no correspondence, for example, if the last row above were

461 Ben Wayne - where it should be Mary Carlsson.

Is it possible to check all the rows of both columns and identify errors?

View Replies!   View Related
Duplicates In List
I need to check a list of names to see if there are duplicate entries.
At the bottom of the list I would like to count the number of duplicates.
In the list I want conditional formatting to highlight cells where the duplicate appears.
Using Excel 2007.

View Replies!   View Related
Deleting Non-duplicates
I have a large spreadsheet of over 5,000 rows and 20 columns. What I would like to be able to do is find a formula(?) that will enable me to retain rows of data when Cells in column B and column E contain duplicate data but delete entire rows if the contents of cells in column B and E are not duplicated.

For example:

Column B contains Part Numbers
Column E contains Manufacturer

If a part number (Cell B)has been made by two or more manufacturers (Cell E), retain all relevant rows.

If a part number has only been made by one manufacturer, however many times, delete all relevant rows.

What I'm trying to be able to achieve is retain data so that I can analyse it when 2 or more manufacturers have produced the same parts.

View Replies!   View Related
Duplicates - Moving To 1 Row
I'm working with duplicate rows of data and need some help to put them onto one line. I'm working with about 58,000 rows of data.

I have the following as an example:

Ref Name Address 1 Address 2
AAB01 Dr Jones 16 Marsh Ave CL4 2JH
AAB02 Dr Peterson 18 Marsh Ave CL4 2JH
AAB01 Dr Jones 21 Marsh Ave CL4 2JH
AAB01 Dr Jones 18 Marsh Ave CL4 2JK

AAB01 is the duplicate, however it's not clear which address and postcode is correct. I want to perform some sort of formula that will add to the first instance of ABB01 the details from the duplicates.

For Example:
Ref Name Address 1 Address 2 Address 1 Address 2 Address 1
AAB01 Dr Jones 16 Marsh Ave CL4 2JH 21 Marsh Ave CL4 2JH 18 Marsh Ave

View Replies!   View Related
Remove Certain Duplicates ....
Is it possible to scan through column A for duplicates if found delete the row that has not got any data in either column D E or F? If Duplicates are found and neither have any data in D E or F Delete all but one of the duplicates.

View Replies!   View Related
Remove Duplicates
I have a sheet that looks like this.
JOE D.02082008/28/199 H PAnnual Increase06/05/2006
JOE D.02082008/28/1995 H PReclassification06/11/2007
JOE D.02082008/28/1995 H PReclassification02/11/2008

The name is in Column F the employee Number is the 020820 and we want to only have the latest date in the last column.

So out of this data above. The bottom one with the 2/11/2008 is the one we want to keep.
And delete those other 2.

The next group has duplicate names 10 of them. But only want to keep the most recent.

View Replies!   View Related
Check For Duplicates Using VBA
I have created a userform that inputs employees names in to a sheet called employee list. It works well, now I want to take it a stage further and check to see if the employee being entered is already in the list and if so let the end user decide whether enter the new data or save the data already in the list.

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Employee List").Activate
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtLast.Value
ActiveCell.Offset(0, 2) = TxtNum.Value
ActiveCell.Offset(0, 3) = cboCourse.Value
If optIntroduction = True Then
ActiveCell.Offset(0, 4).Value = "Operative"
ElseIf optIntermediate = True Then
ActiveCell.Offset(0, 4).Value = "Team Leader"
ActiveCell.Offset(0, 4).Value = "Manager"
End If

End Sub

View Replies!   View Related
Removing Duplicates
I have a spreadsheet which contains 2 columns of data, most of which are duplicates.

I'm looking for a macro which will check all of colum A (A2:A138)


Column B (B2:B163)

I would like the macro to remove duplicate entries (from column A) in column B so that all that is left in column B are entries which don't match any in column A

View Replies!   View Related
Extracting Of Duplicates
i have this situation There are names( in column A) which have relating data in columns B (has dates) , and C ( codes ), it is an example - Bill(column a), 22.02.2009(b), 08(c).The names in column D have duplicates and non- duplicates in the column A .

When the name in the column D has several duplicates in the column A and every his duplicate has different data in column b and c , is it possible all of his duplicates go to the column E , however together with their data in column b and c ,( data from b go to f column and data from c go to g column..
Visually , it should look like something like that

namesdatescodesnamesbill21.09.2009f45billbill21.09.2009f45bill 12.10.2002g67bobbill 12.10.2002g67bob14.07.2001k90bob14.07.2001k90bob12.08.2001d45bob12.08.2001d45joan 07.05.2003d12

View Replies!   View Related
All Duplicates In Column
I've got a spreadsheet with a column of company names. I need to search the entire column and retrieve the rows of any duplicate names.

I tried using :

cRow = ActiveSheet.Columns("D").Find(coName).row
However, it only ever retrieved the first instance of the name and not all the duplicates.

View Replies!   View Related
Delete The Duplicates
I'm new to this and trying to write my own code (quit satisfying actually), I realise there's a lot of threads on duplicates but I couldn't really solve my problem. My code looks like the following

Private Sub Dubletter_Click()

Dim i As Long, lastrow As Long
Dim cell As range

For Each cell In Sheets("LR").range(Cells(3, 2), Cells(Rows.Count, 2))
lastrow = Cells(Rows.Count, 2).End(x1Up).Row
For i = lastrow To 3 Step -1
If Cells(r, 2).Value = cell Then
cell.EntireRow.Delete .....................

View Replies!   View Related
Check For Duplicates ..
I have a distribution list which has names in columns G, H & I (titles in row 2 & 3) data starting row 4. A persons name may appear in any of the 3 columns and may appear only once or many times in the same or multiple columns BUT should not happen in the same row, (rows are cost centres while the columns are types so a person can only get one type per cost centre).

What I need is a formula that will check that there are no duplicate names on a row in columns G:I.

If there were duplicates, is there a formula that would tell me where it is?

The formula could return a result in say G1 which I could format using Conditional Formatting, or would a routine be better for this?

The data could be moved down if more space is needed above the title rows or a separate sheet could be used as a check and query sheet.

View Replies!   View Related
Row Matching And Duplicates
I have a long table with duplicating id numbers but different values which i need to match. Basically the initial state is like this:

ID1 | example 1 |
ID2 | example 2 |
ID1 | example 3 |

and what i need is this:

ID1 | example 1 | example 3|
ID2 | example 2 |

View Replies!   View Related
Checking For Duplicates
On the attached example i am concatenating surname with the first letter of the forename. However, what i want to add into the formula is if the answer is a duplicate of another answer in a range that these duplicates are changed to surname and full forename.

Is this possible with a formula or would it need a bit of vba programming?

View Replies!   View Related
Count Without Duplicates
In my attachment is what i'm working with. In the first worksheet tab, "NOTBA 10 09", i need it to count what's in the other worksheets, minus the duplicates.

So C4 should be referencing to H3:H21 in the 04-Edmonton worksheet. The number it should be showing is 11, however it's counting 15 because there's 5 entries for CDAU034479. Is there a way for it to basically count what's in that column, minus the duplicates?

View Replies!   View Related
Grouping Duplicates...
I have a table in excel that contains many rows, each row being a product. Each row has a product ID, and should be unique, but there are multiple instances of products in the table, some that are duplicate and some that contain different info (product notes, description, etc).

What I would like to do is group the products by product ID, so that I can show the different occurances of the product within each product ID, so that we can weed out the unique values within the duplicate products by ID. Is there any easy way to do that?


View Replies!   View Related
Identify Duplicates
I have two worksheets one worksheet contains Insurnace names and Addresses from a hospital. I need to match these up to specific Insurance codes from a billing company worksheet. The bad thing is my billing database has multiple duplicate addresses for different insurances (Yes this does happen where different Insurances have the same address-don't ask me why). So originally I did a VLOOKUP where I looked up the address from the hospital Spreadsheet and matched it to the Insurance Specific code. The pitfall to doing it this way is that it only grabs the first exact match from the Billing worksheet. Is there a way to either automatically identify the possible matches and allow me to choose which one is the exact match or how do I just identify duplicates on my billing worksheet and match them manually? I attached the worksheet.

View Replies!   View Related
How To Highlight Duplicates ..?
how to highlight duplicates in Excel?

I have 3 columns A: surnames, B: names and C: dates of birth. I have already used filtering to sort the data, however I have too many names ( many thouands) to look through them to ldelete duplicates.

I was thinking about something like =IF(A1:A3=B1:B3; "1"), but it doesn't work.

View Replies!   View Related
Finding Duplicates ...
I have a workbook that I want to find if I have any duplicate numbers in a specific area.

The area of cells that I am checking is C3 through AO70

I am checking for numbers between 95 and 800. These are all ID# of individuals and not all the numbers between 95 and 800 are used. IE: 97 through 100 are not used ect.

I have already written a macro that does something else and I can use it to check each number as it comes up. However, once the number comes up I don't know how to use it to check the area.

If I can check all the area at one time to find duplicates it would be easier.

I do not know how to do either way but I can adapt my macro to whatever way is possible.

View Replies!   View Related
Show Duplicates Only
I have a movie list. column A is the number location i have my movie stored, and column b is the actual movie name. I backup my movies so that my kids wont destroy them. The list is around 1000. I have duplicate backups, i want to have a list of just the duplicate movie names and there locations.

View Replies!   View Related
Deleting Duplicates
I m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.

View Replies!   View Related
Removing Duplicates..
I have list of data references about 60000 of them but some are duplicated. I have used advanced filted then unique records only. So now i have just the unique records showing now. How do i copy the accounts that is just unique into a new worksheet? I tried copying it but its copying everything. I even tried using paste value but still copying everything?

View Replies!   View Related
Count Only Duplicates
I would like to be able to count only the duplicate numbers without having to put the actual number in the formula.



View Replies!   View Related
SUM But Only The First Instance If There Are Duplicates
This is an export from a shopping cart. Each row is an individual SKU that was ordered. So if John Doe orders 4 items on the same order, I've got 4 rows with most of the data being the same. I want to sum my "Grand Total" column, but only take one instance when the Order-ID is repeated.

Now the sample data: ...

View Replies!   View Related
Border Around Duplicates
I have a list (approx 2000 rows). I'd like to put a seperate border around any duplicates that are in the fourth column. If there isn't a duplicate then the regular gridlines are fine. I'd only like the first four columns to include this border. Is there a way for excel to automatically place borders around duplicates? Here is an example of what I have and what I would like it to look like.

View Replies!   View Related
Duplicates And Averaging
Column A has approximately 50,000 rows with unique property parcel numbers. Column B has numbers that represent an elevation point on the parcel. The parcels are not level. The elevation of a parcel varies depending where one measures. So,...I have many rows with the same parcel number in column A but the adjacent column B shows a different elevation. Is there any way to:

1) remove all of the duplicate column A parcel number rows so I have just one column A parcel number row.

2) get the average of all the column B elevations to show up on the same one row?

I should only have approximately 15,000 rows if I could get rid of the duplicates and get the average elevation. I have version 2007 but I have to share with others who have version 2003. I don't know if that makes a difference but I thought I should mention it

View Replies!   View Related
Finding Max Value In Duplicates
I have data that has about 10 duplicate values (UTC Time) in one column and another column with number values (depth ft) that vary. I need to obtain the maximum (highest) value in the depth column and remove the other duplicates to filter out the low values. So for the data example below for UTC 15:56:28 I only want the 5.7 row, for 15:56:29 I want5.3 row and so on. I can attach the sheet. - this is a huuge dataset so manual filtering wont work. Data is from a sonar that gives 10 depth readings per second - I only need one depth tat is the highest value.


View Replies!   View Related
Delete Duplicates That Have Sum Zero
I want to create a macro that will look at different account numbers and delete those rows that have a sum of zero for that same account number. In other words, I am working with an accounting spreadsheet which shows both accounts payable and receivable. When payment is received for an account, I would like the macro to delete it, leaving only outstanding accounts. For instance: ....

View Replies!   View Related
Combobox With No Duplicates
From a list of data in a column (which will be containing duplicate values), is there a way of producing a list containing no duplicates, for use in a userform combobox?

View Replies!   View Related
Check For Duplicates
I have 3 columns A,B and C and at the end of each column i have the row with total values of A, B and C. So I need to make sure that only one value is posted in one of the 3 column values and also if in the current row if the values are properly entered( that is only one of the 3 values is entered) then insert a blank line just before the Total row. I have this code to insert the new row which is working fine. but i need to validate before i could run the code. that is i need to make sure in the selected row, only one value is entered. Note: I want to validate only for the rows that has the cursor present in it.

View Replies!   View Related
Highlight Duplicates
I have a sheet that I use to go through and check data that is returned to me from clientel. Currently I am working on getting it to check for duplicates and having a heck of a time. Here is how I would like it to work. check the comment on all the cells in row 1. If the comment box has the keyword unique anywhere in its text then select that column, and highlight all duplicate entries. I have found plenty of examples on how to get it to highlight duplicates, but nothing close about trying to do it within the same contexts as I am trying.

View Replies!   View Related
Searching For Duplicates
Just trying to find a formula to look for duplicate entries in the same workbook or a single column

View Replies!   View Related
Delete Duplicates In Column
Hi, the code in the codebox under here, which I wrote, is supposed to delete all duplicate records in the column. Which column to uniquify is stated in cell Skin!B17. I searched the internet round, and there are different solutions everywhere. I have been unable though to find the right one - so I thought, why not ask here. If you have a solution, I'd be happy to hear it ...

View Replies!   View Related
VBA Code To Remove Duplicates
Private Sub Dupe_Remover()
'29/06/2005 by nhunter

Application. ScreenUpdating = False
Dim R1 As Range
Dim drow As Integer
Dim lastitem As String

Set R1 = ActiveCell


If Trim(ActiveCell) = "" Then
Goto procend
End If............

View Replies!   View Related
Finding Duplicates Between 2 Rows ...
Just wondering if anyone has a macro, or formula that would allow me to find out (and possibly highlight), when any value in column A is equal to any value in column B. Im dealing with about 2000 rows so its almost impossible to complete manually.

Sorry the heading is supposed to read need help finding duplicates between 2 COLUMNS

View Replies!   View Related
Conditional Formatting / Add Duplicates
I need to create a formula that will look at a p/n find its match and add the value from all matching p/n together. After this is done, all of the line items that matched I need them to turn red. But I do not want the first p/n to turn red. It is better explained in my example.

View Replies!   View Related
Comparing Two Duplicates To Second Criteria
I have two columns: Column A with SS#, Column B with loan #. i need a formula that would return 2 different loan #s for same SS# on a different worksheet. Example

SS#:Loan #ResultSS#Loan #123567812312356781231235678345123567834534578917862950013351

Spend all morning on this. Tried various INDEX, COUNTIFs, MATCH, VLOOKUPs but when looking at the same SS# in a column it always returns the first Loan#.

View Replies!   View Related
Highlighting The Duplicates In Eight Rows:
Range from AK1:AK8 to AT1:AT8. These are eight rows, total eighty entries. Can a formula or conditional formitting determine if there are duplicates values in eighty entries . The need is not column-based rather row based.

View Replies!   View Related
Sum Column Exclude Duplicates
I've got data for different regions of our company. For this example, what I need to do is count the number of jobs in the "South Atlantic" region (Column T) that are NOT "Wal-Mart Stores, Inc" jobs. In order to do this, I put a "1" in Column F so that the formula would have something to sum:

=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),(F2:F1079))

The problem is that some job numbers, which are located in Column G, are duplicated. As a result, this number is higher than it should be.

So what I need to do is only count each job number once. How do I do that? Is there a way to modify the existing formula? Or do I need something completely different?

View Replies!   View Related
Copyright © 2005-08, All rights reserved