Macro that deletes values with condition

Hi,

I need a macro that deletes duplicates of numbers that appear an odd
number of times and that deletes duplicates and the value duplicated
an even number of times. Example:

Original data

A

1
2
3
1
2
1
2
3
4

Result:

A

1
2
4

Values 1 and 2 must remain and only delete duplicates because they
appear an odd number of times (3), 3 must be deleted because it
appears an even number of times (2) and 4 appears because it has no
duplicates.

Hope this can be done!

Thank you so much
0
canvas
12/25/2009 5:34:40 PM
excel 39879 articles. 2 followers. Follow

1 Replies
678 Views

Similar Articles

[PageSpeed] 4

Hi

Insert a heading in row 1 and try this macro:

Sub aaa()
Dim f As Range
Dim ResultArr()
Dim LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set f = Range("A1:A" & LastRow)
'*** Heading required in row 1!
f.AdvancedFilter xlFilterInPlace, unique:=True
ReDim ResultArr(0)
ResultArr(UBound(ResultArr())) = f(1)
For Each r In f.SpecialCells(xlCellTypeVisible)
    If r.Row > 1 Then
        Count = WorksheetFunction.CountIf(f, "=" & r.Value)
        If Count = 1 Or Count Mod 2 <> 0 Then
            ReDim Preserve ResultArr(UBound(ResultArr) + 1)
            ResultArr(UBound(ResultArr)) = r.Value
        End If
    End If
Next
ActiveSheet.ShowAllData
Columns("A").ClearContents
For r = LBound(ResultArr) To UBound(ResultArr)
    c = c + 1
    Range("A" & c) = ResultArr(r)
Next
End Sub

Regards,
Per

"canvas" <spyele123@gmail.com> skrev i meddelelsen 
news:3aaa5f27-5511-4c62-bc12-54dd9e8ed80b@z41g2000yqz.googlegroups.com...
> Hi,
>
> I need a macro that deletes duplicates of numbers that appear an odd
> number of times and that deletes duplicates and the value duplicated
> an even number of times. Example:
>
> Original data
>
> A
>
> 1
> 2
> 3
> 1
> 2
> 1
> 2
> 3
> 4
>
> Result:
>
> A
>
> 1
> 2
> 4
>
> Values 1 and 2 must remain and only delete duplicates because they
> appear an odd number of times (3), 3 must be deleted because it
> appears an even number of times (2) and 4 appears because it has no
> duplicates.
>
> Hope this can be done!
>
> Thank you so much 

0
Per
12/25/2009 8:16:22 PM
Reply:

Similar Artilces:

Clear Unused Column Filter List Values in Pivot Table
Hello Everyone, I have a Pivot table that is dynamically linked to data, over time, the filter data on the columns will become obsolete. My question is how do I clear the invalid values? PivotTable("tableName").PivotCache.Refresh() doesn't seem to do it. TIA See below link. http://www.contextures.com/xlPivot04.html Regards, Shailesh Shah http://in.geocities.com/shahshaileshs/ If You Can't Excel with Talent, Triumph with Effort. http://in.geocities.com/shahshaileshs/menuaddins (Free addins old\classic Office Menu-2003 for Office-2007) "AMDRIT" &l...

Microsoft Word 07 Macro problem
I just re-installed the Microsoft Word 2007, but when I open a Word document, it pops up this. " The command cannot be performed because a dialog box is open, Click OK, and then close open dialog boxes to continue." Then I click OK, the dialog box shows " The document contains macros. Macro language support for this application is disabled. Features requiring VBA are not available. Would you like to open this document read-only?" And the Help is.... "This error usually occurs because of macro security settings. If you know that the macro comes from a sour...

Formula to enter to round an Excel value up to the next $0.5?
If I have a value of $10 and add 10% by dividing by 0.90, I get $11.11 which I would like to round up to the nearest $0.5, i.e. $11.15. Does anybody know the formula and can you use it on a result which is also a formula? Thanks, Lee "Lee" wrote: > If I have a value of $10 and add 10% by dividing by 0.90, > I get $11.11 which I would like to round up to the > nearest $0.5, i.e. $11.15. From your example, I presume you mean "nearest $0.05" (nickel). > Does anybody know the formula and can you use it on a > result which is also a formula? If you truly ...

Minimum and maximum value of Axis set by user by reference to form
Excel seems to have 2 alternatives for defining the min/ max value of an axis: either automatically or preset by the user. But is there any way that the minimum and maximum value that the axis is defined by the user based on the values of the spreadsheet (i.e., halfway between the above two apparent options provided by microsoft)? Yes, you can tie axis values to worksheet cells via a macro. The process is described via the link below . . . http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html -- John Mansfield http://cellmatrix.net "jonny" wrote: > Excel se...

Issue importing pick list values using Import Wizard
Hi All, We have an issue with importing picklist values using the Import Wizard. We have tried importing the and the underlying numeric value, but both import as null fields. Some of the fields have been modified in the Leads entity but not in the Account entity yet. Since CRM 3.0 maps the two (as in leads to Accounts), I wonder if that is the problem? Some pick lists are only found in the Lead entity at this time, and they don't import either, rather they DO import, but as nulls. As I understand things, usually when a null value is imported the problem is a mis-match in data ty...

Disallow LOW macro security
Using group policy we can set the Macro setting in EXCEL2002 to any of the following:- HIGH MEDIUM LOW However what we want to be able to do is to allow the user to change Excel Macro security to whatever suits them except for "LOW". We don't want LOW to ever be an allowed option. Is there any way in Group Policy (or otherwise) that we can achieve this result? ...

How to Lookup based on 2 values?
I am working on a worksheet to keep track of and display employee's leave. On one sheet, I enter a new record into a list that contains in Column A) Employee Name, B) Start of Leave, C) Number of Days, D) Number of Hours, E) Type, F) End Date. On another sheet, I have a leave calendar, spanning 4 weeks. I can enter the start date, and I have it set up so that the dates line up with the proper days (Mon, Tues, etc) automatically, so dates are calculated, not constant. On this sheet, there is a list of all personnel's names in Column A. In Row 5, the dates run across the sheet...

criteria with null value
Hi, I am running this SQL to find the price of an item: SELECT PARTS.UNITPRICE, PARTS.ITEMDESC, PARTS.WIDTH, PARTS.LENGTH, PARTS.LHEIGHT FROM PARTS WHERE (((PARTS.ITEMDESC)=forms!form1.descrip) And ((PARTS.WIDTH)=forms!form1.text2) And ((PARTS.LENGTH)=forms!form1.text4) And ((PARTS.LHEIGHT)=forms!form1.text6)); The problem is, if any of these items are null - the query shows no records. How can I conditionally check and ignore null values? Thanks in advance, Bonnie On Wed, 7 Nov 2007 14:08:00 -0800, Bonnie <Bonnie@discussions.microsoft.com> wrote: >The problem is, if any ...

Two X value scales for the same data
I have a scatter graph with x values of 1, 2, 3, 4. I want a scale on top or maybe below the first scale that is, say, X^2, i.e., 1 4,9 16 which matches the bottom X scale as to placement Can I do that? It like plotting data in inches but also showing the equivalent measure in centimeters. I made an XY plot of this data 1 10 2 20 3 30 4 40 Then I made this data 1 10 4 20 9 30 16 40 I selected and Copied this; activated the chart; used Edit | Paste Special and specified New Series with Category data in first column Then I formatted the new data series in the chart to have a se...

How do I Auto-Filter with multiple values in a cell in Excel?
Is it possible to use the Auto-Filter in Excel 2003 in the following way: Cell Value: Requirements, Testing Cell Value: Requirements Cell Value: Testing Is there a way I can enter my cell value such that the Auto-Filter values will only show "Requirements" and "Testing" in the dropdown, but not "Requirements, Testing"? I've tried using ; , | and even hard returns as separators, but to no avail. Am I asking too much of the auto-filter? Thanks in advance Unless you create another column to indicate the match, I think you are asking too m...

check box value
Help. I am creating an order form for my customers. We have a standard product and then a few options. Rather than have my customers type the price of the options in (& risk holding up an order due to incorrect pricing), I want them to select a check box. When "red hats" are checked I want $1.00 to show up in AE20 and when "green hats" are checked I want $2.00 to show up in AE21. I guess I want a set value to populate a cell when a box is checked. right click on your check box and go to format control select ad20 and ad21 as your cell link,in ae20(and similar ...

password protect a macro
i have a sheet with a button which executes a macro, i would like t make it so that you can only run the macro (clcik the button) if yo supply the correct password thanks robert -- Message posted from http://www.ExcelForum.com Add the line If inputbox("Please Enter Password") <> "PASSWORD" then end Dunca -- Message posted from http://www.ExcelForum.com spot on.thank -- Message posted from http://www.ExcelForum.com ...

Lookup Value in Regarding field
I have written a script in the phone call onSave that fills in the Sender and Recipient fields base on the value of the Owner and Regarding. My problem is that there are 4 possible option values for Sender/Recipient and 11 values from Regarding. Example: Company, User, Contact, Lead are in all of them. If I choose Opportunity in Regarding, it errors out because the typename does not match any typenames in Sender/Recipient. What is the scriupt necessary to check for this before performing the action? Thanks. -- Client Solutions Manager Micro Strategies ...

Delete a document in excel mobile?
How do I delete a document in excel mobile? I am using a smartfone. ...

How to find corresponding registry key and value for a particular setting in the application ??
Hi, I am trying to automate a third party application with menus and dialogs. In the process I need to check registry. the project is in vc++ using mfc. What is the process to find/locate corresponding registry entry (key and value) for a particular setting in the application ?? Thanks in advance, Abhishek A. Use the following registry API's to query data from the registry. RegOpenKey[Ex]() RegQueryValue() RegCloseKey() If you are simply looking for the list of registry keys that the third party app is accessing, then use the registry monitor ( regmon ) ...

Change Automatic Dimension Callout Value
Is there a way to change the Automatic Dimension Callout stencil from feet to "U's". I am drawing computer racks, and I would like to use this as a guide when placing the computer stencil in the rack. The RACK stencil has this feature, I tried to copy the properties, but no luck. Thank you. Hey Vinnie, I couldn't resist dinking with Visio's dimension line shapes, and posted a modified "Rack Unit" version on my web site! Have a look here: http://www.visguy.com/2007/04/01/rack-unit-dimension-line/ -- Hope this helps, Chris Roth Visio MVP Free Visio ...

Combo Box
Greetings, I had an issue of how to populate my combo box with unique values only, after some research I discovered collections and how they dont allow duplicate values and I could simply skip the error to achieve the results I wanted. However how can I modify the code to sort the values in the collection (Unique) in alphabetical order? I've seen some complex looking loops based on the old bubble sort routines that I wrote many many moons ago, but wondering what other peoples solutions would be? Any assistance would be appreciated. Cheers Rob 'Populate combo box with unique...

Deleting Attachments from Outlook Express
I would like to delete these attachments from emails in Outlook Express. How is that done? My file is getting huge with all these files. Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress fo...

Sum(if ... multiple conditions ... Interpretation?
Excel 2000 ... I attempted to write the following array formula free-hand & failed ... Consequently, I used Conditional Sum Wizard to build formula for me. Above said ... Can one of you Excel Magicians interpret (in plain English) how end of this formula works? =SUM(IF($M$2:$M$12000=$BG2,IF($T$2:$T$12000=0,1,0),0)) ,1,0),0)) What do last 3 positions & characters stand for? I am pretty certain the 1st 1,0) is part of 2nd IF Function, but have no idea how formula is working. Thanks ... Kha Look in HELP for sumif instead. This if you want to sum t for cells in m that match ...

How do I recover a folder I deleted in Outlook Express
Help! I accidently deleted a folder in my "received" folder in Outlook expess. Can I undelete this a folder? How? "JTC" <anonymous@discussions.microsoft.com> wrote in message news:07c901c3ad1e$5c1e9c80$a501280a@phx.gbl... > Help! > I accidently deleted a folder in my "received" folder in > Outlook expess. Can I undelete this a folder? How? have you looked in the Deleted items Folder? this newsgroup is for support of Outlook 97/98/2000/2002/2003(beta) from the Office suite of products. Outlook Express is actually a separate program despite the s...

custom built Excel macros
How do I transfer custom built excel macros to another computer? John, you can export a macro and save it, to do this from your workbook, right-click the workbook's icon and pick View Code. This icon is to the left of the "File" menu this will open the VBA editor, click on the module you want to save and go to file and export file, then save it to a floppy or what ever you backup to, then import it to the new workbook. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is ...

Conditional Number Formatting & Selective Summing #2
Attached is the workbook I need help with. From what I have bee discovering from the Excel Macro-VBA Editor-Help screens, what I wan to do should require fairly simple VBA code. However, I have no VB coding experience or skills (the VBA code in Module 3 was gleaned fro the Excel Tips Forum. Thank you Harlan Grove) Sheets �Run� through �Run (30)� are identical, except that the cel formulas on sheets 2-30 are only active if the active sheet i �Enabled� by the user and will only populate with pulled data if al preceding sheets are �Enabled�. The nature of the workbook is such tha a user will alw...

Recover Deleted Items
Our CEO is having a strange issue. We use Outlook 2007 on Windows XP Pro; Exchange Server 2003 Not using cached-exchage mode He's a local admin, and has full access to all the boxes he's trying to open. In Outlook he has his primary email account set up. He also has a whole bunch of additional mailboxes added (employees) We turned on the registry hack that makes 'Recover Deleted Items' always visible. When he tries to open 'Recover Deleted Items' (RDI) for his main account, it opens right up. When he tries to open RDI for a remote user, nothing happens... no error, n...

Deleted Tools Option from Menu Bar
Ok i know this is going to sound stupid but... I was writting a macro to format a report and wanted to add it to th tools drop down menu; however whilst i was doing so i stuffed up th code and instead have deleted the "Tools" option from the command bar Could someone please let me know how to restore it. Cheers, Ma -- Mat ----------------------------------------------------------------------- Mat K's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1952 View this thread: http://www.excelforum.com/showthread.php?threadid=49858 Right click on your too...

Color Values in VBA
I have an AC2003 database that has a textbox with a backcolor. I need to dynamically change the backcolor value to a lighter shade of the textboxes current backcolor. After playing with the colorpicker in Access, I noticed that the Hue and Sat values remain constant, however the Lum increases when the shade gets lighter. I know how to specify the RGB values to determine the access color value. For example RGB(255,0,0) equals 255 for red and RGB(255,255,0) equals 65535 for yellow. I have a subroutine that converts any backcolor value to it's RGB equivalent. Since the use...