Using Vlookup With Duplicate Data

Nov 25, 2009

Message board virgin here, but I need help with an excel issue so excuse me if I don't follow protocol.

I'm using vlookup to return the salesperson by serial number, but run into a problem when my criteria has a duplicate value, but my answers does not.

Here's my example:

Serial#, Salesperson
123 Bill
456 Bob
456 Suzy
789 Bob

ADVERTISEMENT

Vlookup And Duplicate

Jun 17, 2009

I want to check with the vlookup function and some other form of either index or other function where if I check (enter an ID) an ITEM ID and then it will tell me how many different products have been assigned to that ID ITEM. In some cases the ITEM ID has only used One Product, whereas other ITEM ID's have used muliple products.

I have attached an example of what I am trying to achive (its possible the same ITEM ID could have several products used against it.

View 3 Replies View Related

Using VLookup On Duplicate Values To Get Sum Of Both

Feb 11, 2013

I am currently busy with a material tracking sheet. The sheet tracks all the material from fabrication to being released from painters.

My problem is that in a summary sheet that I created, I used vlookup to get the exact value of the item number that was released.

Focus on Item # 043-06016

example summary.jpg

[Code] .....

This formula only gets the first value and not the 2nd value that was also released so that in my summary sheet i can get 2

example released items.jpg

View 3 Replies View Related

VLookup With Duplicate Data

Jan 27, 2012

I have a table simmilar to this:

Reptile Dog Red
Reptile Tiger Purple
Mammal Tiger Stripes
Mammal Tiger Spots
Mammal Racoon Black
Mammal Cat Plaid
Mammal Dog Brown
Mammal Dog Purple
Reptile Dog Red
Reptile Tiger Purple
Tree Dog Orange
Tree Pine Green

I would like to use this data to populate within a seperate worksheet that reads:

Mammal, Tiger, Stripes
Mammal, Tiger, Spots
Mammal, Dog, Brown
Mammal, Dog, Purple

Essentially pulling all of one duplicate item within a column. The problem I ran into is when I run a vlookup within the entire table, it gave me duplicates.

Basically, it gave me...
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal Tiger Spots

I know this is because of the array, just forwarding to the next item, and rerunning the lookup... since Mammal is not at the top... it has to wait till the array gets to the area of "mammal".

How do I create the list, so it will not create the duplicates... like in the example I gave.

View 2 Replies View Related

VLookup With Duplicate Values

Apr 12, 2012

I have an interesting problem where I am trying to display a list of top ten ranked items and I have multiple items tying for a rank, creating two or more values for one rank.

I have a table that shows the ranking, numbers 1 to 10. I'm using a vlookup formula to find the rank and return the corresponding name from my data table.

The problem I have is there are two that are tied for 3rd place, so I have two ranking at three. The current table looks something like this:

1 Warren
2 Stan
3 Mike
4 #N/A
5 Dan

I can change my table so that the numbers on the left are dynamic so it will display the number 3 twice if there are multiples of the same ranking, but when I do my lookup I'm still stuck.

Is there a way to return the second "3" on the lookup? Or is there a better way to solve the issue?

View 1 Replies View Related

Vlookup With Duplicate Id & Interval

Oct 16, 2008

I have a template whereby it show agent hourly performance. I unable to use vlookup formula because duplicate id with different interval. If I select id 1977 it will auto update agent performance it the table according to the interval.

View 9 Replies View Related

Vlookup - Pick Last Of Duplicate Entries

Jan 15, 2010

Writing this workbook in 2007, but it will be used in 2003. Have searched but can't find answer to this. I have a 5-column (A to E) table array and I'm using VLOOKUP in several other cells to return values in col2, 3, 4 & 5 with my lookup value being col 1. However, col 1 will contain numbers which occur more than once. How can I get VLOOKUP (or something else?) to pick the last occurence (lowest row) and use that one? Also, the array will be sorted - smallest to largest - as the rows go down - how will the SORT work on duplicate entries? I'd like, if possible, for the entry made most recently to be the lowest of the duplicate entries so that VLOOKUP (or whatever) will pick that one to use. Will any kind of SORT do that?

View 9 Replies View Related

(UDF) MACRO To VLookup Duplicate Values To Columns

Apr 13, 2013

I need a User Defined Function (UDF) to Vlookup duplicate values in separate Columns.

I can't use an Array function as the data is huge and it takes lot of time to calculate.

I have attached a Input and the desired output in the file attached.

UDF_MACRO.xlsx

View 9 Replies View Related

Get Correct Value Using Vlookup Formula In Duplicate Look Up Values?

Mar 4, 2014

How to get correct value using vlookup formula in duplicate look up values.

Here i mentioned a eg;

VlookUp_DuplicateValue.JPG

View 8 Replies View Related

VLookup - Find Duplicate Accounts And Get Count?

Apr 24, 2012

I have a workbook where i am trying to find duplicate accounts. I need to get a count of these duplicate accounts and i need to find the accounts as well. One last thing the workbook covers an entire year and i would like a formula that will cover the 12 sheets to provide the results.

View 4 Replies View Related

Removing Duplicate Information In Same Cell After Performing VLookup?

Apr 8, 2014

I have used a vlookup code from a past post (Vlookup multiple values in one cell) in VBA and successfully retrieved my information to one cell.

However, I would now like to remove any duplicates in this cell; preferably while running the same code.

--vlookup code
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range

[Code].....

View 2 Replies View Related

Duplicate Values For VLookup - How To Receive Data Back

May 23, 2014

I have a situation where I have about 20000 lines to populate and looking for a quicker alternative. I have duplicate numerical values in the lookup_value. I want the same number to appear for all lookup_value's but there is only one instance of that value on the other sheet I am searching. My table looks like this:

lookup_value

50058459
50058459
50058459
50058459
68594523
68594523
68594523
58965214

Table_array

Column A Column B

50058459 1234
68594523 9876
58965214 3456

I want my output of vlookup to look like this

Column A Column B

50058459 1234
50058459 1234
50058459 1234
50058459 1234
68594523 9876
68594523 9876
68594523 9876
58965214 3456

View 7 Replies View Related

Macros To Delete Entire Duplicate Row For Duplicate Values?

Aug 19, 2014

I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.

Copy of OCL 2010 (3).xlsx‎

View 1 Replies View Related

Delete Duplicate Rows :: Duplicate Company Names

Dec 11, 2008

I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.

Attached is a sample of that spreadsheet.

View 5 Replies View Related

Delete Duplicate Cells Or Rows With A Duplicate Cell

Nov 1, 2007

I feel as though I have spent enough time searching the previous posts to ask this question.

I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.

COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner

I want to end up with rows that each contain unique info in COLUMN B.

View 9 Replies View Related

Mark Duplicate Values :: Insert Word Duplicate Next To Row

Jun 12, 2008

I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:

12345 a
12345 a DUPLICATE
11111 b
23123 b

Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?

Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub

View 3 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.

Col A
100
101
102
105
100
101
102
105

Col D
5
4
2
4
1
2
3
1

After my code is run, I need for my spreadsheet to look like this

Col A
100
101
102
105

Col D
6
6
5
5

I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.

Here is my code thus far....

-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)

[Code]....

View 9 Replies View Related

Sum Duplicate Values Then Delete Duplicate Rows

Jan 5, 2004

I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.

Col A
100
101
102
105
100
101
102
105

Col D
5
4
2
4
1
2
3
1


After my code is run, I need for my spreadsheet to look like this
Col A
100.........................

View 9 Replies View Related

VLOOKUP With INDIRECT (become Dynamic As The Table Array Part Of The Vlookup Will Change)

Aug 18, 2009

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:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
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:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
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 3 Replies View Related

Hyperlinks And VLookup: VLookup To Find EMail And Web Addresses

Oct 5, 2009

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 5 Replies View Related

Write VLookup Where Data Array Changes Each Time VLookup Used

Oct 29, 2012

I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:

Loans to countries
Mar
Apr
May
Jun

Loans to banks
Mar
Apr
May
Jun

Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.

View 4 Replies View Related

Double VLookup (vlookup The Same Data From 2 Different Sheets)

Jul 13, 2009

I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.

View 4 Replies View Related

Vlookup Across Sheets, Nested Vlookup Possibly?

Jun 9, 2009

Im 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 months 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. Itd also be great if the formula can be transferred between workbooks. Im 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 14 Replies View Related

Vlookup Looping (for Each...next?). Vlookup Loop Technique

Jul 24, 2009

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 5 Replies View Related

Using Vlookup & Indirect To Ref List And Vlookup Files

Sep 17, 2008

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 14 Replies View Related

Vlookup Error Msg "unable To Get The VLookup Property Of The WorksheetFunction Class"

Jan 8, 2009

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 3 Replies View Related

Vlookup + Vlookup Discounting Any #n/a

Jul 15, 2008

I'm taking a spreadsheet that I produce each month and creating a year to date spreadsheet in the same format. I'm using a vlookup to find the campaign name in each sheet and add up the totals. This works fine but sometimes a camapign ends and so the vlookup for that month will produce an #n/a value so will reduce the whole sum to #n/a.

The VLOOKUP + VLOOKUP + VLOOKUP I was using that produced an #n/a is shown below.

=VLOOKUP($A6,'[Margin by Site Net April 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net May 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net June 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)

To get round it I've added in an IF statement combined with ISERROR as shown below. It works but is looking quite messy. Is there an easier way to do this ? (the formula below is from the cell below the one above so the look up value is one cell down)

View 9 Replies View Related

Min With No Duplicate

May 6, 2008

I have a spreadsheet with 7 golf teams (4 members each). For each hole, I want to award 1 point to the team with the lowest score, if and only if there is not a tie between two or more teams.

Example:

Team 1 - 7
Team 2 - 6
Team 3 - 5
Team 4 - 4

I would want to give team 1, one point, but if this were the case:

Team 1 - 7
Team 2 - 6
Team 3 - 5
Team 4 - 5

then nobody would receive a point. I came up with a barbaric way of using another row

=IF(MIN(C$9,C$24,C$36,C$50,C$61,C$75,C$86,C$100),1,0)

and then using an if statement: if the sum of all those above cells was greater than 1 then 0 and adding that, but I was wondering if there is a more efficient way?

View 9 Replies View Related

Remove Duplicate

Apr 9, 2014

I attached a file in which column A is dr_cr and E id INST_NO and column G is INST_AMT. This file like a bank statement. in which one instrument(cheque) present and i denote it c(credit) in column A. but if cheque credit then d(debit) means that this cheque present and dishonour. but some time one cheque credit and then debit and then credit. it means that we have to remove previous credit and debit entries. in this attached file you found this type of entries. i want to remove this type of entries. i further explain.

1. if one instrument have one credit and one debit its ok.

2. if one instrument two credit and one debit then remove one credit and one debit where instrument no and amount and drawee bank must be same.

3. if one instrument have two credit and two debit we have two remove one one debit and one credit.

4. if one instrument have three credit and two debit then we have to remove two credit and two debit so one credit left.

Attached File : remove duplicate.xlsx‎

View 2 Replies View Related

Duplicate Lines

Jan 1, 2008

i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.

how would i go about eliminating the duplicate strings of text quickly?

View 9 Replies View Related

Extract The 1st Row Of Each Duplicate Set

Nov 17, 2009

I have a worksheet which looks like below.
ColA ColB
1 Red
2 Red
3 Red
4 Dog
5 Dog
6 Blue
7 Blue
8 Green
9 Green

Is there a formula I can use to extract the 1st row of each duplicate set (column A having unique values, column B having duplicates)? So from above my result would be:
ColA ColB
1 Red
4 Dog
6 Blue
8 Green

View 3 Replies View Related

Duplicate All Rows

Mar 6, 2009

I will be both apologetic and happy, though, if you can suggest a solution that does not require programming. If a programming solution IS required, I'd be grateful if you could give me a note or two on how to run the code if it is necessary. I'm competent with computers and I could program what I need in C++ if I had to, but I haven't used VBA before.

Here's my excel problem:

I have two long sets of data:

One is pressure from a transducer under water (in the river) recorded every 30 minutes. The other is pressure from a transducer above the water recording every hour.

I need to find the pressure due to water for each point (meaning I need to subtract the atmospheric pressure from each point of total pressure). From that, the height of water can be calculated, which will allow me to calculate discharge, or flow, of water at this spot in the river.

Because the atmospheric pressure is only recorded hourly, I need to duplicate each row of the atmospheric data worksheet so I can copy it over and make it the 'subtract' column.

Since I am working with years of data, there are thousands of rows, and the idea of duplicating each row manually is lame.

I tried to figure out a way for my calculation formula to use each row of the 'subtract' column twice (by making the first two subtract the value in E5, the second two use E6, the third pair use E7, and then dragging the auto-fill formula thingy down through the whole data set, but it doesn't work because the first one that gets auto-filled subtracts the value right next to it {..., D9-E7, D10-E7, D11-E11, D12-E11, ...} and so on).

So, like I said, I think i'll probably need to program it. If there was a way get the auto-formula-fill thingy to stop skipping back to the cell directly next to it as soon as it starts over the loop of copying, then that would be great.

Thank you for your help, and I apologize if this has been posted before, but all I could find were like a billion threads on deleting duplicate data.

View 10 Replies View Related

Duplicate Row [A1] Times

Aug 26, 2008

I have a question. Imagine this scenario:

Column A is a quantity column.
Column B is a product name column.
Column C is a description column.

So say I have the following chart:

|2|eggs|white|
|1|banana|organic|
|3|apples|mackintosh|

Can I set up a formula so that it ends up looking like:

|eggs|white|
|eggs|white|
|banana|organic|
|apples|mackintosh|
|apples|mackintosh|
|apples|mackintosh|

so that the quantity is represented by how many times an item is listed?

View 9 Replies View Related

How To Find Row Of Last Duplicate

Nov 7, 2011

I have multiple items in the similar column. I need to find the row number of the last one. For eg. In column B1 to B5 I have Apple in the first 4 cells and Mango in the last cell.

Apple
Apple
Apple
Apple
Mango

I need the Row number of Last Apple i.e row 4. How can I achieve that using VBA?

View 9 Replies View Related

ID Each Duplicate In A Column

Sep 5, 2012

I have a spreadsheet with a large number of records. Each row contains info about people and there are multiple rows for each person. The database is sorted by the last name column so you can see each row of data for that person. We have been manually highlighting the name by each group, alternating so you can easliy see when the name changes. (hightlight Adams, don't hightlight Brown, higlight Carter, etc) But the file is long and this will take forever. I would like to highlight each group of names without manually scrolling thru the large spreadsheet. It would even be fine to just hightlight the first occurance of each name so that you can easily find the first record for that name. Conditional Formatting doesn't seem to work since I need it to hightlight when the last name changes - not find duplicates.

View 5 Replies View Related

Remove 1st Duplicate Only?

Aug 9, 2013

Is there any way of Removing the first duplicate in a list only? I am writing some vba to automate a month end process and wonder if there is a way to achieve this? (excels remove duplicates function keeps the first, and removes everything else). The data is in column C.

View 2 Replies View Related

Duplicate Cells

Feb 20, 2007

I have a sheet where I input 8 columns of data from an email template which is sent to me throughout the day. I enter data in one row per email along the 8 columns. eg A1 to H1 then A2 to H2 etc.
Column C has the entry as a job number '123456'.
I need to be able to see if the same job number appears more than once.
In one week I have input 135 rows of data and have spotted 3 occasions where the same job number has been called in.
Any way I can set up a seperate sheet which will search and show duplicated rows from sheet 1.

View 9 Replies View Related

VBA To Duplicate Dates

Nov 15, 2008

I have a request of a code. Very simple:

Sheet1 *FG39-Nov9-Nov412-Nov12-Nov514-Nov14-Nov616-Nov16-Nov718-Nov18-Nov8*0-Jan9*0-Jan10*0-Jan Excel tables to the web >> Excel Jeanie HTML 4

I need that the code duplicate all dates but 0-Jan in column G into column F.

The reason I need code for this is that the cells below the last date must be empty, not only " ".

View 9 Replies View Related

Duplicate Pop-up Message

Feb 17, 2009

Let say I have these data on a sheet:

A1 = abc123 B1 = 12345

A4 = abc123 B4 = 12345

I would like excel to display a pop-up message saying that the data in A4 AND B4 duplicate with the data in A1 AND B1

View 9 Replies View Related

Duplicate Entries

Sep 24, 2009

I have been trying a number of different functions!

I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.

Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?

=(COUNTIF('Cases Closed'!O:O,"John"))-(COUNTIFS('Cases Closed'!O:O, "John", 'Cases Closed'!X:X, "*Solution*"))

View 9 Replies View Related

How To Get Together All Duplicate Lines

Oct 22, 2009

How to get together all duplicate lines? ...

View 9 Replies View Related

ADVERTISEMENT