+ Reply to Thread
Results 1 to 11 of 11

Remove Duplicate Values Only - Not Row - No Formula?!

  1. #1
    Registered User
    Join Date
    07-11-2011
    Location
    England
    MS-Off Ver
    Office 2019
    Posts
    58

    Remove Duplicate Values Only - Not Row - No Formula?!

    Morning guys,

    I have a list of thousands of records whereby each line is itemised. I'm trying to count the number of unique values for reporting purposes.

    At the moment, I'd like to remove duplicate values in a column, but NOT remove the row. ie

    Job 1 - item 1
    Job 1 - item 2
    Job 2 - item 1
    Job 3 - item 1

    I'd like (preferably) a filter/function to show;

    Job 1 - item 1
    - item 2
    Job 2 - item 1
    Job 3 - item 1

    So that when I count the number of jobs, it shows 3, not 4.

    I will need to run this monthly and the data is straight out of access so I'd rather not insert columns for formulae in the middle of my worksheet. Unless in my totals I can allow for unique values in an =COUNTA option?

    Help! Thanks

  2. #2
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Hi Martin,

    I have sorted the list using a helper column. Please check if it meets your requirements.
    Attached Files Attached Files
    Last edited by jeffreybrown; 09-05-2016 at 11:30 AM. Reason: Removed full quote to clean up thread

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Hi,

    One way would be a pivot table. Use the PT Options to supress Grand totals for columns and Sub total by Job - see attached example
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    07-11-2011
    Location
    England
    MS-Off Ver
    Office 2019
    Posts
    58

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Thanks, sorry, I should be clearer;

    See attached. I'm really trying to avoid a helper column...I can do it with one, but for speed I didnt know if there was a function (like conditional formatting or remove duplicates - but that removes a full row, I only want to remove the dupe cells and keep all other info)

    Once the info is de-duped, i need to run a count function on the column so I'm counting unique values in a column. If this can be done in the middle of a formula...perfect!
    Attached Files Attached Files
    Last edited by MartinGTC; 09-05-2016 at 05:22 AM.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Highlight all the cells in column A, starting with A1, then click on Conditional Formatting | New Rule | Use a Formula... , then put this formula in the dialogue box:

    =COUNTIF(A$1:A1,A1)>1

    Click on the Format button, then choose white for the foreground colour, then OK your way out.

    Any duplicates will now appear to be blank (although they are still there).

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    07-11-2011
    Location
    England
    MS-Off Ver
    Office 2019
    Posts
    58

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Any way to make this as an actual blank ("") so that it is affected by another count formula? I have a simple =COUNTA(A:A) formula, but lots of these are duplicates so my 2000+ records aren't actually 2000+ jobs

  7. #7
    Forum Contributor
    Join Date
    07-12-2015
    Location
    Dubai,UAE
    MS-Off Ver
    Office 365
    Posts
    414

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Quote Originally Posted by MartinGTC View Post
    Any way to make this as an actual blank ("") so that it is affected by another count formula? I have a simple =COUNTA(A:A) formula, but lots of these are duplicates so my 2000+ records aren't actually 2000+ jobs
    Hi Martin,

    PLease have a look
    Attached Files Attached Files

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Quote Originally Posted by MartinGTC View Post
    Thanks, sorry, I should be clearer;

    See attached. I'm really trying to avoid a helper column...I can do it with one, but for speed I didnt know if there was a function (like conditional formatting or remove duplicates - but that removes a full row, I only want to remove the dupe cells and keep all other info)

    Once the info is de-duped, i need to run a count function on the column so I'm counting unique values in a column. If this can be done in the middle of a formula...perfect!
    Did you try the Pivot Table approach I mentioned in #3?

  9. #9
    Registered User
    Join Date
    07-11-2011
    Location
    England
    MS-Off Ver
    Office 2019
    Posts
    58

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    I'm not entirely au fait with pivot tables just yet...I've had a quick play but they look confusing! I think I may have the answer with a helper column though (i tried not to!)

    ...although I'm good at notblank commands now haha!

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    It sounds like you want to count unique values, so instead of your COUNTA(A:A) you should use this formula instead (in your sample file):

    =SUMPRODUCT((A1:A4 <> "")/COUNTIF(A1:A4, A1:A4))

    If you have 2000 records in your real file then you should change the 4 to 2000 (three times).

    Hope this helps.

    Pete

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Remove Duplicate Values Only - Not Row - No Formula?!

    Quote Originally Posted by MartinGTC View Post
    I'm not entirely au fait with pivot tables just yet...I've had a quick play but they look confusing! I think I may have the answer with a helper column though (i tried not to!)

    ...although I'm good at notblank commands now haha!
    They may look confusing but well worth spending half an hour playing around with them. They'll pay dividends over your Excel lifetime. They do all the work of summarising and analysing, stuff that you'd otherwise need to achieve with combinations of lookup, index, match, countif, sumif and many others but which you can do in seconds with a Pivot Table.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to sum duplicate values then remove the duplicate rows?
    By terry3218 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-12-2015, 02:38 AM
  2. Formula or code to remove duplicate text or values within a cell
    By winmaxservices1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-22-2014, 06:16 AM
  3. [SOLVED] Remove duplicate values from Listbox
    By TPDave in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-15-2014, 05:21 AM
  4. [SOLVED] Trying to Remove Duplicate Values of Last Name List
    By sonicexcel in forum Excel General
    Replies: 3
    Last Post: 04-24-2014, 11:01 PM
  5. Excel 2007 : Remove Duplicate Values from top 5
    By loknath in forum Excel General
    Replies: 1
    Last Post: 11-08-2011, 09:46 AM
  6. How to remove formula from creating duplicate values?
    By Climaxgp in forum Excel General
    Replies: 3
    Last Post: 12-14-2009, 03:04 AM
  7. is there a formula that remove duplicate values from a range
    By Martin R in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2006, 08:10 AM

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