+ Reply to Thread
Results 1 to 6 of 6

Removing Unique Values Identified by Conditional Formatting in Excel 2007

  1. #1
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Removing Unique Values Identified by Conditional Formatting in Excel 2007

    Good Morning

    The attached worksheet contains the record number (A) and organization number (B) for 1997 and (D) and (E) for 2010. I have been using conditional formatting to identify common organizational numbers for 1997 and 2010. I then manually delete each organizational number and corresponding record number for organizations not found in both years. Is it possible to write a formula or macro (the latter is less desirable due to newbie skill level) that eliminates unique organizations and their corresponding record numbers? I have tried filtering on color or the lack there of but it only filters out the values in one column. And frankly, I have experienced problems working with filtered data.

    Thank you for taking the time to read and/or respond to this request.

    Al Charbonneau
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing Unique Values Identified by Conditional Formatting in Excel 2007

    I am not sure what you mean by:

    I have tried filtering on color or the lack there of but it only filters out the values in one column.
    if you select columns A:B and apply auto filter, then filter column B by colour, you should get all the duplicates.. then you can delete them. (You should first put the other table in another sheet).

    You can also use a formula in C2 like: =COUNTIF($E$2:$E$170,B2)>0 to get TRUE for duplicates, then filter for TRUE
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Removing Unique Values Identified by Conditional Formatting in Excel 2007

    Thanks for responding to my query. Sorry for lack of clarity. First, I am trying to filter out unique organizations, that those that do not appear in both 1997 and 2010. When I tried to filter on color, I selected the no fill option because the colored cells are the ones that I want to keep. When I tried selecting A:B and then filtering on B, it did not keep the record number on the same row as the organizational number. Secondly, while I find filtering very useful, after filtering, I always seem to drag the values that were filtered out into the analysis. Do you have to copy and paste the filtered column to get rid of the data filtered out?

    Thanks again for responding to my query.

    Al

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing Unique Values Identified by Conditional Formatting in Excel 2007

    If you have the filters on A and B, and filter by B, then all the A values should stay in line. Then you filter B for no fill, then select the visible items in A:B and press Delete on keyboard... then unfilter to see the rest. Now reselect column A and B and hold the CTRL key and hit G. Then click Special and select Blanks, then Ok. Then right click on one of the blank cells in those columns and select Delete, then select Move Rows Up.

    Then repeat for D:E columns.

    I have done it on the attached.
    Attached Files Attached Files

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Removing Unique Values Identified by Conditional Formatting in Excel 2007

    Hi

    I beleieve that NBVC suggestions are the best way to do the job..

    But if you need to do this using formula, you can do this using an Array formula for Organization numbers. I use Named renges for shorter formula...

    So in another column(let's say H, in H2 put this formula and copy down)
    Please Login or Register  to view this content.
    Change semi colons in my formula, to comma.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Forum Contributor
    Join Date
    03-22-2012
    Location
    Narragansett, Rhode Island
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Removing Unique Values Identified by Conditional Formatting in Excel 2007

    Bingo! Great suggestion.

    Then I went to go to special, selected both rows identifying the blanks and deleted them.

    Real time saver for me.

    Hope all goes well with your sister.

    Al

    ---------- Post added at 12:36 PM ---------- Previous post was at 12:33 PM ----------

    Thanks for weighing in. NBCV's suggestion worked real well and taught me more about filtering!

    Al

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1