+ Reply to Thread
Results 1 to 6 of 6

Removing duplicate numbers from columns

  1. #1
    Registered User
    Join Date
    03-30-2020
    Location
    Australia
    MS-Off Ver
    16.35
    Posts
    3

    Question Removing duplicate numbers from columns

    Howdy all, noob here.

    And I have a simple enough problem but I cant seem to work it out...

    I am trying to create a list of post codes for online store delivery shipping prices in a 0-50k radius, 50-100k radius & 100-150k radius. I have all the post codes in a 50k radius, a 100k radius and 150k radius in three seperate columns, but I cant find a simple way to remove the 100k radius post codes from the 150k radius, leaving only the 100-150k, and the 50k from the 100k, leaving on the 50-100k.

    Does that make sense and is there anyone out there that can help?
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,476

    Re: Removing duplicate numbers from columns

    Hi Limelight and welcome to the forum,

    I have an answer using some helper formulas and copy and paste. See the attached.
    Remove dup Postal Codes in Radius.xlsx

    Here is the reasoning. If you count all the postal codes in Col A and B, some will equal 2 because they are listed twice. You only want the ones that are listed once, IE only in the 100 and not in 50.
    Same idea for the 150. Count how many codes equal the 150 codes in all columns. If it is only 1 it must be in 150 only.
    Sort those columns by these helpers and copy only codes with a 1 for the count down to the answer.

    Some smart guru will most like come up with VBA or a single formula but this is how I'd do your problem.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-30-2020
    Location
    Australia
    MS-Off Ver
    16.35
    Posts
    3

    Re: Removing duplicate numbers from columns

    Thanks Marvin, good thought process, but as the posts codes aren't in numerical order by distance from us. There are both low and high numbers in each of the 3 radius ranges.

    The first sign this hasn't worked entirely, is there are no 2XXX codes in the 100-150 radius...when in actual fact there should be 6 (as there are 12 on the 150 minus the 6 in 100).

    I might just have to sit here and remove the duplicates manually. Which is fine, until we want to alter the parameters and I have to do it manually all over again... and again.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,476

    Re: Removing duplicate numbers from columns

    Here is a second method because this was a fun problem.

    Remove Dup Postal Code by Radius 2.xlsx

    Now I need to read why I got the problem wrong above. I think both answers above don't use the codes being in order for them to work.
    Tell me where I went wrong... I simply removed the 50 from the 100 and then removed both 50 and 100 from the 150. That is what I think I did at least!

  5. #5
    Registered User
    Join Date
    03-30-2020
    Location
    Australia
    MS-Off Ver
    16.35
    Posts
    3

    Re: Removing duplicate numbers from columns

    Not sure where/why the first attempt went wrong...but it looks like you nailed it on the second go!

    Thanks a bunch

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,476

    Re: Removing duplicate numbers from columns

    Hey Limelight,

    I just checked my first answer with the second and the numbers are exactly the same but the sort on the 150 is a little different. The first answer just didn't sort the 150 numbers from small to large but all the codes are correct and there. You scared me, saying the answer was wrong. I think it is correct but just not sorted.
    Can I get a B+ instead of an A for not sorting the 150 column??

+ 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] Removing duplicate phrases from corresponding columns
    By probuddha in forum Excel General
    Replies: 6
    Last Post: 06-20-2016, 04:02 PM
  2. [SOLVED] Removing rows that contain duplicate information in 3 columns
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-15-2015, 02:33 PM
  3. Removing duplicate rows using only a few columns.
    By HorribleZane13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2015, 09:34 AM
  4. [SOLVED] Removing duplicate columns
    By Rec1ne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-02-2013, 06:04 PM
  5. Removing duplicate in from two columns not in order.
    By bisalk in forum Excel General
    Replies: 5
    Last Post: 10-02-2013, 03:24 AM
  6. Removing Duplicate Values - 2 Columns
    By hpaum99 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-27-2010, 12:00 PM

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