Free Microsoft Excel 2013 Quick Reference

VLOOKUP with duplicate items to find

I have duplicate entries in a worksheet in the "Item To Find" cell, and if I copy the VLOOKUP formula by dragging the cell down through all entries on the sheet, the duplicate entries show the columtopickfrom value as #N/A. This appears to be due to the range values increasing by 1 for each row.

How do I copy the VLOOKUP formula and ensure that the range stays the same for every copied cell ?

e.g.
Cell E2 =VLOOKUP(D2,'BearBox Sites'!A2:C16,3,FALSE)

Drag down to E3 and:

Cell E3 =VLOOKUP(D3,'BearBox Sites'!A3:C17,3,FALSE)


Post your answer or comment

comments powered by Disqus
Hi guys,

I have a spreadsheet which lists all the price changes for the month.
Before doing any updates, I will need to search through the spreadsheet for any duplicates.

If any duplicate rows are found, I need the VBA code to look at the 6th column of those duplicate rows to find out which duplicate row has the lowest value, and delete the rest of the duplicate rows.

For e.g. If there are 4000 rows, and duplicates are found in row 10, 100 and 1000, the code will find that row 100 has the lowest value in the accompanying cell in the 6th column, and delete row 10 and row 1000.

So in the end there will only be 3999 rows left, with no blank rows in between.

I think I would need to use arrays right? I'm still not good with arrays and looking up on it now.

Will appreciate any advice given!

I have read all threads I could find but don't seem to have the same needs.

I have two worksheets they both have same and different data.
I need to bring "ACE Report" into Master 1 customs. If I just copy/paste, I will have replicated (duplicate) or transposed info which I don't want. Each row has to be unique. It will then be exported to Access.

If I run a Vlookup in Master 1 to find the replicated data in ACE Report, my result should be:

"N/A"
"N/A"
"N/A"
"N/A" and
110-3758795-9
112-1234567-8
"N/A"
etc. But, I get the N/A and some of the entries appear but the are repeated a certain number of time and then another number apprears and it goes on and on. What is wrong in my formula or is it the wrong formula? I have tried performing the Vlookup in the worksheet "ACE report". I have tried the Match, Hlookup, Lookup, unsuccessfully.
I'm baffled!!

Any kind of help will be greatly appreciated.

I am in the process of adding items to a search string that all have a common result i.e that Me.Checkbox2 = True if the condition is met. Is there an easier way of adding these items i.e "G1009","E1001","E1026" ect ect rather then typing the same code again and again with just a different sString?

sString =
"G1009"
    Set rFind = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=sString)
        If Not rFind Is Nothing Then
            Me.CheckBox2 = True
            End If
            
    sString = "E1001"
    Set rFind = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=sString)
        If Not rFind Is Nothing Then
            Me.CheckBox2 = True
            End If
            
    sString = "E1026"
    Set rFind = ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=sString)
        If Not rFind Is Nothing Then
            Me.CheckBox2 = True
            End If


Dear all

I observed there is a lot of forum question related to row delete with duplicate item or empty.

I just wondering is that a VBA code that can be used to delete the rows that have non-duplicate item

Hello everyone!

What an amazing wealth of information this place offers! I'm truely pleased to have found it.

After searching and searching, I couldn't find an answer to my problem, however.

I'm doing a spreadsheet for an engineer in my office. He has to choose what type of steel beam to use, based on a strength requirement determined by several factors. My worksheet will currently take these inputs, and then generate the required strength value, for example

A B
1| Beam Width [USER_INPUT]
2| Weight Capacity [USER_INPUT]
3| Strength Required [GENERATED VALUE]

I have inputted all of the strength values of the steel beams we use most commonly, for example

C D
1|Beam: Max Strength:
2|W8x10 7.8
3|W8x13 9.5
4|W8x20 18.2

So I want the spreadsheet to search through column D to find the steel beam whose strength value is greater than the strength required, and is the lowest among those meeting this criteria.

So if my StrReq is 6.7, I want to select the 8x10, if the requirement is 9.9 I want to select the 8x20, if the requirement is 9.3 I want to select the 8x13.

The default lookup() will find the lower value, I need it to find the higher value.

Does this make sense?

If so - what do I do here?

Thanks so much!!

Dear All,

I have a list of open invoices, debits and credits for a customer. I want to apply positive amounts against negative amounts to reduce the number of open items. I want to find the best combination of the amounts that when summed together get me as close to zero as possible and hopefully includes the highest number of transactions.

Any takers?

Thanks,

GL

I want to automate a vertical table in Excel to find multiple occurrences of an item eg. the string "***" and list them horizontally.
VLOOKUP only finds the first of multiple entries in the table.
I want to search a sequential list in ColB using a value from ColA which is the string "***" and list the corresponding values for the match in other columns.
How Can I use VLOOKUP and a next to find the next occurrence of the look up value?

Hello. I have a userform that has multiple data entry boxes. When a city is selected from the userform the following code is suppose to narrow down the zip code(s) for that particular city and list only those zip codes applicable as a drop down in the same userform. As some cities have more than one mailing zip code the vba coding is suppose to find all affiliated zip codes with the selected city name. The problem I'm having is that the following code will work sometimes and on other occasions the drop down from the userform will contain empty data, as if it isn't finding any data, which shouldn't happen. It appears to work better when the zip codes aren't hidden, but I'd prefer that this code would find the selected data all the time, hidden or not. I was curious if anyone had any suggestions to improve this existing code? Thanks in advance!
Dim ws         As Worksheet

Private Sub CboCitybox_Change()
  CboZipCode.clear
  Set findit = Sheets("2012").Range("BU:BW").Find(what:=CboCitybox.Value)

   'make sure we found something
  If Not findit Is Nothing Then
    '74 below relects the column number
    i = 74
    Do
      CboZipCode.AddItem Cells(findit.Row, i).Value
      i = i + 1
    Loop Until Cells(findit.Row, i).Value = ""
  End If
End Sub


I have an excel spreadsheet where I'm using macros to find dept numbers and replace them with macro names. The macro works well until it can't find a department number. Then I get the error message "Run Time Error 91"
"object variable or with block not set". How can I get the macro to go find the next department number when it doesn't finds that the current one doesn't exist?
A portion of the macro is shown below.

Sub Convert_dept_Nos()
'
' Convert_dept_Nos Macro
' Macro recorded 12/14/2006 by Guilford County
'

'
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SPREADSHEET WITH DEPT NAMES").Select
Columns("B:B").Select
Selection.Find(What:="101", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="101", Replacement:="COUNTY COMMISSIONERS", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Find(What:="102", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

Hello Roy,

My apologies for not complying with Rules 2 & 3. It won't happen again.

In respect to my problem on how to transfer the data from an invoice with many items to another worksheet being the Invoice Records (or Database), I have managed to RESOLVE it myself.

I used a modified version of a macro "TransmitAndClearInvoice" by Iwer Morck, and initially managed to only transfer the basic details of the invoice (eg: customer ID, Customer Name & Address,Date of Inv,etc), and not the details of the many items purchased (eg: Item, Qty, Description, Price, Amount, etc).

The culprit was that my invoice consisted of some columns of merged cells and I did not get the Cell column coordinates right, and assumed that the invoice had only 5 columns.

Here is Iwer Morck's code:

Sub TransmitAndClearInvoice()
   ' Makro by Iwer Morck
   '
    Sheets("Invoice").Select
    If [C12].Value = "" Then Exit Sub
    If [B20].Value = "" Then Exit Sub
    If [K2].Value = "" Then Exit Sub

    With Sheets("InvRecords")


        x = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

        If .Columns(1).Find(What:=[K2], _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole) Is Nothing Then

            r = 20

            Do
                .Cells(x, 1).Value = [K2] ' Invoice Number
                .Cells(x, 2).Value = Date ' Date
                .Cells(x, 3).Value = [C12] ' Customer Number
                .Cells(x, 4).Value = [C13] ' Name
                .Cells(x, 5).Value = [C14] ' Address
                .Cells(x, 6).Value = [C15] ' Code
                .Cells(x, 7).Value = [C16] ' Phone
                .Cells(x, 8).Value = [J16] ' Sales Consultant
                .Cells(x, 9).Value = Cells(r, 2).Value ' Item Number
                .Cells(x, 10).Value = Cells(r, 3).Value ' Description
                .Cells(x, 11).Value = Cells(r, 8).Value ' Unit
                .Cells(x, 12).Value = Cells(r, 9).Value ' Qty
                .Cells(x, 13).Value = Cells(r, 10).Value ' Price
                .Cells(x, 14).Value = Cells(r, 11).Value ' Total
                x = x + 1
                r = r + 1
            Loop Until Cells(r, 2).Value = ""
            Range("C12").Value = ""
            Range("ITEM_NO").ClearContents
            Range("QTY").ClearContents
            Range("K2").Value = Range("K2").Value + 1
            Range("C12").Select

        Else
            MsgBox "The invoice number already exists"
        End If
    End With

End Sub
Here is my modification of the code:

Sub TransmitAndClearInvoice_Click()
' Makro by Iwer Morck as modified by Nik
'
Sheets("InvTemp").Select
If [G9].Value = "" Then Exit Sub
If [A21].Value = "" Then Exit Sub
If [AA10].Value = "" Then Exit Sub

With Sheets("InvRecords")


x = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

If .Columns(1).Find(What:=[AA10], _
LookIn:=xlValues, _
LookAt:=xlWhole) Is Nothing Then

r = 21

Do
.Cells(x, 1).Value = [AA10] ' Invoice Number
.Cells(x, 2).Value = [AA12] ' Date
.Cells(x, 3).Value = [G9] ' Customer Number
.Cells(x, 4).Value = [D12] ' Name
.Cells(x, 5).Value = [D13] ' Address1
.Cells(x, 6).Value = [Y15] ' Project
.Cells(x, 7).Value = Cells(r, 1).Value ' ItemNo
.Cells(x, 8).Value = Cells(r, 8).Value ' Qty
.Cells(x, 9).Value = Cells(r, 11).Value ' Description
.Cells(x, 10).Value = Cells(r, 27).Value ' Rate
.Cells(x, 11).Value = Cells(r, 32).Value ' Amount

x = x + 1
r = r + 1
Loop Until Cells(r, 1).Value = ""
Range("G9").Value = ""
Range("PROJECT").Value = ""
Range("ITEMNO").ClearContents
Range("QTY").ClearContents
Range("DESCRIPT").ClearContents
Range("AA10").Value = Range("AA10").Value + 1
Range("G9").Select

Else
MsgBox "The invoice number already exists"
End If
End With

End Sub
Cheers,

Nik

Is it possible to nest a VLOOKUP and an HLOOKUP to find a value in a chart of data by providing the vertical and horizontal values to search for? Using the below chart as an example, I would want to be able to search for B, 3 to return the result "y3".

1 2 3 4 5
A x x2 x3 x4 x5
B y y2 y3 y4 y5
C z z2 z3 z4 z5
D w w2 w3 w4 w5
E v v2 v3 v4 v5

Can anybody help me with a formula to find out if a cell contains (a part of) a string ?

Thanks in advance

Hi,
I am stuck with this problem.
What I want is when I click the vlookup. I want to run a vlookup with Column F20, to sheet "Master" and paste the values of Procedure in J20 and continue the procedure.

Then I want the data pulled up by using vlookup be converted to values,So that I can edit them as needed.

I am attaching a dummy sheet for reference with before and after sheet.

Thanks in advance.

hi all,
I'm trying with no success to find (by find method) the date entered in a cell (custom formatted as "dd.mm.yy") in a range of cells (custom formatted as "d" and being formula result). Any ideas how could I do this?

Hi, newbie in VBA here so would appreciate any help.

Essentially this is a simple inventory management sheet in which i want to check if i have sufficient quantity of a particular product in inventory to sell to a customer.

I need help with a macro to find in "Inventory" sheet, B17, in "Data" sheet Column B from row 11 onwards and if a match is found, compare the number in "Inventory" sheet, D17, with "Data" sheet, in column D but in the row in which the first match was identified.

If the "Inventory" sheet, D17, is more than the corresponding "Data" sheet cell in column D, then a MsgBox appears.

If the "Inventory" sheet, D17, is equal to or less than the corresponding "Data" sheet cell in column D, then a different MsgBox appears.

If the the first match can't be found in row 11, then it would move on to the next row and so on.

Sorry for the long question and messy upload.

Many thanks in advance!

I've attahced an example of what I need. I need a formula that looks thru a column of parts to find the corresponding price in the cell next to it.

Hi,

I seem to having a problem with my spreadsheet using Vlookup. It's mainly because on the 1st column, there are items which have duplicates and are differentiated only by the 3rd and 4th column.

example:

1st column:
Bob
Jane
John
Bob

3rd column:
Red
Orange
Purple
Blue

4th Column
1
2
3
4

Basically I'm doing a vlookup and but i want to combine (or sum) the 4th column values of Bob (Red) and Bob (Blue). I know I could use the SUMIF function in order to do what I want but I was wondering if I could nest that function within the vlookup so that it would do it automatically?

Thanks in advance!

How can I find duplicate items in a sheet.. Example..

I have a sheet that has a series of numbers

1112
1114
1115
1119
2223
3434
2222
2222
1112

I need to sort and delete 1112 because it has been entered twice

also with alphabets too..

I have a VLOOKUP formula for the following columns. I am looking for the ID number from column E, to find the ID number in column B and place the cash value from column C in column F. My problem is duplicate numbers in column B and also other characters in column B. I have to import the information from column B and C from a *.csv file with numerous page headers, which would take an hour to delete all of the header information.

The formula I am trying to use, which works if I do not have the duplicate numbers or header information is:

=VLOOKUP(E4,$B$3:$C$950,MATCH($F$1,$B$1:$D$1,0),FALSE)

******** ******************** ************************************************************************>Microsoft Excel - DIA Commission Worksheet.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3F4=
BCDEF1ID*Cash*ID*Cash2001*Fr****361448**614480461448**611050561448**N/A*661448**5643771*761448**1219521*861448**4207455*961448**7852649*1061448411.7*4207456*11001*Au**61477*1261105**194061*1361105**5776364*1461105**1613886*1561105**5787047*1661105**61440*1761105505.6*5764238*Sales*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I was given a formula that did not work before and need to try this again.
I have two lists which I have put in one worksheet, column A is 797 rows
long and column B is 319 rows long. I need to isolate the duplicates in these
two columns so I can remove them and see what was not duplicated (all of the
items in column B will be duplicated in column A)
The lists are labels which consist of letters and numbers some have spaces
and other have signs like - , ( , ).
The lists are of finished items and un-finished items, the finished items
will be duplicated, so by removing those lables I will be left with the
un-finished items.
Any help with be appreciated.

Hello,

I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.

A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15

I have another table (table2) in another sheet in the same workbook have a material and date.

A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008

My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.

I have tried using vlookup but it only works for the first match and doesn't check for other values

below is the function that i tried

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

Can anyone please help me with this problem.

I have a table of relationships - where an item can relate to one or
more items. Specifically, these are systems and their interfaces.

For example, System A relates to (shares info/data) with System B which
also has a relationship with System C. I want to generate something
that looks like the previous statement. Column A would have System A
(the lookup value) and Column B would have the result of a vlookup
based on the value in column A. Column C would then vlookup from Column
B and so on.

The issue here is that System A relates to multiple systems. How can I
rig a vlookup to find the second, third, fourth, etc, instances?

Sample Data
A - B - A
A - C - B
A - D - E
A - E - B
B - A - C
B - E - H
E - H

I need help! Basically I need to create a array formula in Microsoft Excel (not allowed to use a macro) to do multiple functions. I have a list of data that can be updated regularly and can not be sorted. So additional line items can be added at any time (I have specified the max of data) I have created dummy data below. I want to find all the “Apple” Entries and then look up the highest B Value and Display Column C. And then I want to look up the next “Apple” with the 2nd highest B Value and Display Column C again.

Data Sheet
A B C
Apple 5 300
Banana 20 125
Cherry 25 200
Apple 10 210
Cherry 25 250
Apple 15 100

OUTPUT
Apple (Column C Data) (Highest B Value) == Apple 100 15
Apple (Column C Data) (2nd highest B Value) == Apple 210 15

(do not display next Apple Data)

I was using the following formula, but it does not select the largest value Column B first.
=IF(ISERROR(INDEX($A$2:$C$6,SMALL(IF(A$2:$C$6="Apple",ROW(A$2:$C$6)-ROW(A$2)+1,ROW(C$6,)+1),1),2)),"NONE",(INDEX(($A$2:$C$6,SMALL(IF(A$2:$C$6="Apple",ROW(A$2:$C$6)-ROW(A$2)+1,ROW(C$6,)+1),1),2)))

I also tried this formula, but the large function does not seem function correctly when there are duplicate values or when the value is blank. APPLE=Row A, BValues=Row B, and All Values = Row B & C.

=IF(ISNUMBER(MATCH("Apple",FRUIT,0)),VLOOKUP(LARGE((FRUIT="Apple")*(BVALUES),1),ALLVALUES,2,0),"")

Any ideas? I know this is complicated so feel free to ask questions I will do my best to explain.

Hi Guys,

I have just under a million rows of URL's and need to somehow find the duplicates.
The remove duplicates function in excel 2010 is great and really fast however i dont want to remove them, i need a way of identifying the duplicates.

Countifs are way too slow, and the conditional formatting doesnt really help because I need to find all the duplicates and flag them up in another column.

I need to someone flag duplicates, not every row if it is a dupe. For example, If I have:

example1
example2
example2
example1

I would want to flag upthe last 2 but the first two would be valid rows.

Does this make sense? I need a fast way of doing this, I have the latest i7 with mega fast ram but countifs are still taking forever.

Any ideas?

Thanks!!

Mike


No luck finding an answer? You could always try Google.