Does anyone know how to find duplicates within an Excel range object?
78823
Private Sub GetUniqueItems() \n    Dim cell        As Range                'Current cell in range to check \n    Dim rngToSearch As Range                'Cells to be searched \n    Dim dic         As Scripting.Dictionary 'Dictionary Object \n    Dim dicItem     As Variant              'Items within dictionary object \n    Dim wks         As Worksheet            'Worksheet to populate with \nunique items \n    Dim rngPaste    As Range                'Cells where unique items are \nplaced \n\n\n    'Create range to be searched \n    Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) \n\n\n    'Confirm there is a relevant range selected \n    If Not rngToSearch Is Nothing Then \n        'Create dictionay object \n        Set dic = New Scripting.Dictionary \n\n\n        'Populate dictionary object with unique items (use key to define \nunique) \n        For Each cell In rngToSearch            'Traverse selected range \n            If Not dic.Exists(cell.Value) Then  'Check the key \n                dic.Add cell.Value, cell.Value  'Add the item if unique \n            End If \n        Next \n\n\n        If Not dic Is Nothing Then          'Check for dictionary \n            Set wks = Worksheets.Add        'Create worksheet to populate \n            Set rngPaste = wks.Range("A1")  'Create range to populate \n            For Each dicItem In dic.Items   'Loop through dictionary \n                rngPaste.NumberFormat = "@" 'Format cell as text \n                rngPaste.Value = dicItem    'Add items to new sheet \n                Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range \n            Next dicItem \n            'Clean up objects \n            Set wks = Nothing \n            Set rngPaste = Nothing \n            Set dic = Nothing \n        End If \n    End If \nEnd Sub \n\n\nPrivate Sub GetDuplicateItems() \n    Dim cell        As Range                'Current cell in range to check \n    Dim rngToSearch As Range                'Cells to be searched \n    Dim dic         As Scripting.Dictionary 'Dictionary Object \n    Dim dicItem     As Variant              'Items within dictionary object \n    Dim wks         As Worksheet            'Worksheet to populate with \nunique items \n    Dim rngPaste    As Range                'Cells where unique items are \nplaced \n    Dim aryDuplicates() As String \n    Dim lngCounter  As Long \n\n\n    'Create range to be searched \n    Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) \n    lngCounter = 0 \n\n\n    'Confirm there is a relevant range selected \n    If Not rngToSearch Is Nothing Then \n        'Create dictionay object \n        Set dic = New Scripting.Dictionary \n\n\n        'Populate dictionary object with unique items (use key to define \nunique) \n        For Each cell In rngToSearch            'Traverse selected range \n            If Not dic.Exists(cell.Value) Then  'Check the key \n                dic.Add cell.Value, cell.Value  'Add the item if unique \n            Else \n                ReDim Preserve aryDuplicates(lngCounter) \n                aryDuplicates(lngCounter) = cell \n                lngCounter = lngCounter + 1 \n            End If \n        Next \n\n\n        If lngCounter > 0 Then        'Check for values \n            Set wks = Worksheets.Add        'Create worksheet to populate \n            Set rngPaste = wks.Range("A1")  'Create range to populate \n            For lngCounter = LBound(aryDuplicates) To UBound(aryDuplicates) \n'Loop duplicates \n                rngPaste.NumberFormat = "@" 'Format cell as text \n                rngPaste.Value = aryDuplicates(lngCounter)    'Add items to \nnew sheet \n                Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range \n            Next lngCounter \n            &#39
739521
A fantastic place to look for excel answers is Google groups.\n\nFor programming you can follow this link:\n\nhttp://groups.google.com/group/microsoft.public.excel.programming\n\n\nYou can ask any question and within minutes to a couple hours your question will be answered.  If you have other questions on Excel just search for google groups excel and get tons of answers there on anything you EVER wanted to know about excel.  I had a problem I could not solve and asked my question. It was pretty complicated and withing 15 minutes I had my answer!!! I love that place for excel questions and answers!\n\nI guarantee you will be happy with the results
1022400
You need to do a lookup. If you type this into excel is should popup a wizard.
366534
It completely depends on where you are using this formula, is it in Excel? A program? What language?\n\nWe need more info.
1038128
Yes there is a way to do this, but you'll have to split the master document up into about 4 spreadsheets.  There's only one good way of doing this if you don't want to spend years matching these together and that is with a macro.  The problem is that macros take a descent little bit of expertise to develop.  So it's time for me to give you a crash course in macros.  First you need to make sure that your security level in Excel for macros is set to Medium or Low.  From the menu bar, go to Tools, Macro, Security.  Reset it to Medium save the file, then restart Excel.  Excel needs to be restarted for the change to take effect.  Now on to creating macros.  Go to Tools, Macro, Record New Macro.  Pick a name for your macro then click OK.  Don't do anything whatsoever to the Excel workbook, just click the stop button.  This creates basically a blank macro named Macro1 or whatever you called it in your workbook.  Now we want to go edit that macro.  Go to Tools, Macro, Macros.  \n\nNow select your macro and click Edit.  Now past the following text in as macro code:\n\n'Set column A to your part number and column B to your lead time\nFor Each x In [Doc2! A:A]           'Looks through every cell in column A in a worksheet called Doc2\n    If x.FormulaR1C1 = "" Then      'Tells computer to end the search if it reaches\n        Exit For                    'a blank cell in column A of Doc2\n    End If\n    \n    For Each y In [Doc1! A:A]       'Looks through every cell in column A in a worksheet called Doc1\n        If y.FormulaR1C1 = "" Then  'Tells computer to go to the next part number in Doc2 if it reaches\n            Exit For                'a blank cell in column A of Doc1\n        End If\n        \n        If x.FormulaR1C1 = y.FormulaR1C1 Then           'Compares the part numbers to see if they match\n            x.Offset(0, 1).Value = y.Offset(0, 1).Value 'Sets column B in Doc2 equal to column B in Doc1 (your lead times)\n            Exit For                                    'Moves to next part number in Doc2\n        End If\n    Next y  'Moves to next part number in Doc1\nNext x      'Moves to next part number in Doc2\n\n\nEverything after an apostrophe is comments and will appear green.  This will have no affect on how the macro runs.  You will need to modify the macro or you worksheets so the macro works properly.  The code gets pasted between the Sub Macroname and End Sub portions.  The comments I listed should help you understand how the macro works.  Here's something REALLY IMPORTANT!!!  Save your workbook BEFORE you run the macro.  You can't undo what a macro does by hitting the undo button.  So if it doesn't do what you want, simply close the file without saving it.\n\nContact me, I probably need to explain a few more things too you.  Since your master list is over one spreadsheet long there are going to be some complications you'll have to work through that could make it very difficult, even for a macro.  \n\nMacros can save hours or days of time, but they can be difficult to a new user.  The Visual Basic 6 Blackbook by Coriolis is a good reference guide if you want to learn to write macros in Excel.  Just remember the coding may be a little different in Excel than for writing a program in VB6.
606125
sort the data by cell phone number\n\ninset a column to the right of the number column.\n\nin the first cell, enter this formula:\n=if(a1=a2,1,0)\nwhere "a" refers to the column with the numbers.\n\nhighlight the cell with the formula.  copy it.\npaste is all the way down to the end of the column.\n\nhighlight the column with the formulas.  copy it.  go to the edit menu, choose paste special and choose values.\n\nsort data by the column with the 1s and 0s in it.  anything with a 1 is a duplicate.
586461
I thought the istext formula would work but they don't.\nHere's another way to solve this issue. Assume the cell to test is in A1, enter the following formula in another cell:\n\n=IF(ISERROR(LEFT(A1,1)/1) ,"yes","no")
317360
If for example your older data is on Sheet1, then in the column next to the 'New company name type this formula in cell B1\n\nHere is the formula\n\n=IF(ISNA(VLOOKUP (A1,Sheet1!$A$1:$A$2310,1)), "New Data","Existing Data")\n\nCopy this formula next to each of the new addresses.\n\nIf it finds an existing entry the Word "Existing Data" will appear, and if it's a brand new entry then the word "New Data" will appear!\n\nYou can then simply use an AutoFilter to sort out the new and existing data (You will need headings over your data)\n\nHope this helps.
391107
USe the function "CountIF"\n\ne-mail me for more details
72978
I'd use the vlookup function.\nInsert another column between the 2 you have, then (thinking that you'll have column headers) in b2 =vlookup(a2,C:C,1,false) this will bring back the value if you have it in the second set (which should now be in col C). Paste it to the bottom of your data set.\nCopy the formula into col D, & paste down.\nI'd then sort by col B then by D. What's matched is good, what isn't is unique to the column.\nHope this helps
8029
There are a ton of ways to do this.  You can use the Edit>Find window.  Or you can actually create an auto filter.  Auto filter will allow you to group all like categories for each column in your excel spreadsheet.  Select or create the row just above your data, click on Data>Filter>Auto Filter.  This will place little drop down boxes at the top of each column.  If you select one you will see all the groupings in that column.  If you select one of the groupings in the list it will only show those rows.  It's an awesome feature which I use all the time.
76070
Actually when you're in Excel you can add a Visual Basic line that makes a SQL query that selects one random name. I don't know the particulars, but I know it's really really easy and it's literally one line of code or two.\nYou could also have two excel files, and one of them acts as a "filter" of sorts, by bringing "external data" from the second one and filtering with the SQL statement.
501771
=COUNTIF(A3:A6,"CO")  A is column \n\n=COUNTIF(B3:B6,">55") B is column
792191
If you really want them to be numbers, then change the format of the cells. Use the number format "0000#" (or however many zeros you want).\n\nHowever, VLookup will not match the leading zeros in this case because the internal format of the numbers will be mathematical not textual. If you want to store the numbers as text (for instance, if they are ID numbers) then preface them with ' when you enter them. This tells Excel not to evaluate the cell.
943817
[EDIT - DELETED A WHOLE BUNCH OF TEXT]\n\nthat kind of thing is complex to create in a macro form, normally the kind of thing that you'd pay someone to do because i'd imagine it's easily a couple of hours work to write all the code, test it, iron out bugs etc. there *may* be a built in function in excel to do something like this but i wouldn't have thought so - i'm happy to be proved wrong:\n\n----found on t'internet----\nA1:A10 (your list of values)\nB1:B10 (leave blank)\nC1: =A1*B1 (copy thist fomula down through to C10)\nC11: =SUM(C1:C10)\n\nNow to use Solver....\nTools>Solver\nSet Cell: C11\nEqual to the Value of: 1173.76\nBy Changing Cells: B1:B10\nSubject to the Constraints....\n(click the add button and constrain B1:B10 to Binary)\nClick [OK]\nClick [Solve]\n\nExcel will toggle cells B1:B10 between 1 and 0 until it comes up with a combination that sums to 1,173.76\n----end of instructions----\n\nit depends on the setup of your spreadsheet - if it's one huge page that's a jumble of undistinguishable invoices with no reference to who the invoices belong to, it means manually checking the results of the macro for the set of results that applies only to one 'customer'. ideally you'd want a seperate sheet for each customer and then the macro or above method can be ran on whichever sheet/customer the cheque is from.\n\ni dunno about other people on here but i wouldn't spend hours of my time creating such a thing for the sake of 10 points which aren't really worth anything - i CAN tell you that if the above method doesn't do the job that it'd mean creating a loop that will add up every possible combination of totals from the sheet then comparing it each time against the grand total until it gets a 'hit'.\n\ni can't imagine this kind of sorting being practical for huge amounts of figures, probably a list of 20 at most because you could get into some big big processing time otherwise.\n\ni would be interested in hearing anyone elses ideas about doing it?
348716
Can't do that in Excel. The best thing you can do is type your headings in row 1 in each column.
687364
In spread sheet 1 I created the following\nSorry but the formatting is not to good.\nColumn A\n1\n2\n3\n\nColumn B\nhello\nhi\nhowdie\n\nIn spreadsheet 2 I created this\nColumn A\n1\n2\n3\n\nColumn B\n=LOOKUP(A1,Sheet1!A1:B3)\n=LOOKUP(A2,Sheet1!A1:B3)\n=LOOKUP(A3,Sheet1!A1:B3)