+ Reply to Thread
Results 1 to 4 of 4

Need to delete duplicate of a column B based on column A and keep Column B if unique value

  1. #1
    Registered User
    Join Date
    08-23-2017
    Location
    London
    MS-Off Ver
    2017
    Posts
    2

    Question Need to delete duplicate of a column B based on column A and keep Column B if unique value

    Hi Friends,

    Am new to this forum, hoping to get some urgent help from THE EXPERTS

    I have spreadsheet a million record, 50 columns.
    Which 1) I have sorted Column B Low to high, and Column C Latest to Oldest
    Now i want to keep the latest time stamp(even if its repetitive N number of times, i would like to keep it like even once or even N times). But delete rest of the dates for that particular duplicated Column B value and expand the current selection.

    I hope i could get some help. Many Thanks.

    Illustrated an EXAMPLE

    A B C
    1 1111 12 Aug 2017 19:45:43:20
    2 1111 12 Aug 2017 19:45:43:20
    3 1111 12 Aug 2017 19:45:43:20
    4 1111 12 Aug 2017 09:45:48:20
    5 1111 12 Aug 2017 09:45:48:20
    6 2222 17 Aug 2017 21:25:43:20
    7 2222 17 Aug 2017 21:25:43:20
    8 2222 11 Aug 2017 21:25:43:22
    9 3333 20 Aug 2017 19:45:43:20
    10 3333 20 Aug 2017 19:45:43:20
    11 3333 20 Aug 2017 19:45:43:20
    12 3333 20 Aug 2017 19:45:43:20
    13 3333 20 Aug 2017 19:45:43:20
    14 4444 25 Aug 2017 19:45:43:20
    15 4444 21 Aug 2017 19:45:43:20


    From above i want to keep 1,2,3,6,7,9,10,11,12,13,14
    and delete 4,5,8,15 and also expand the current selection

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Need to delete duplicate of a column B based on column A and keep Column B if unique v

    Hi EP, welcome to the forum! With so many records, the sort is CRITICAL as it reduces the need for in-formula comparisons.
    Paste this in D2 and copy down:
    Please Login or Register  to view this content.
    Now select column D, press CTRL+C, then Paste VALUES. Finally, sort on column D to move the Blanks to the bottom, then delete them.
    Row\Col
    A
    B
    C
    D
    1
    # Code Time Stamp New
    2
    1
    1111
    12 Aug 2017 19:45:43:20
    1111
    3
    2
    1111
    12 Aug 2017 19:45:43:20
    1111
    4
    3
    1111
    12 Aug 2017 19:45:43:20
    1111
    5
    4
    1111
    12 Aug 2017 09:45:48:20
    6
    5
    1111
    12 Aug 2017 09:45:48:20
    7
    6
    2222
    17 Aug 2017 21:25:43:20
    2222
    8
    7
    2222
    17 Aug 2017 21:25:43:20
    2222
    9
    8
    2222
    11 Aug 2017 21:25:43:22
    10
    9
    3333
    20 Aug 2017 19:45:43:20
    3333
    11
    10
    3333
    20 Aug 2017 19:45:43:20
    3333
    12
    11
    3333
    20 Aug 2017 19:45:43:20
    3333
    13
    12
    3333
    20 Aug 2017 19:45:43:20
    3333
    14
    13
    3333
    20 Aug 2017 19:45:43:20
    3333
    15
    14
    4444
    25 Aug 2017 19:45:43:20
    4444
    16
    15
    4444
    21 Aug 2017 19:45:43:20


    Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee
    Last edited by leelnich; 08-23-2017 at 08:06 PM.

  3. #3
    Registered User
    Join Date
    08-23-2017
    Location
    London
    MS-Off Ver
    2017
    Posts
    2

    Re: Need to delete duplicate of a column B based on column A and keep Column B if unique v

    HI, Many thanks 4 ur quick reply. But didnt work. Any other views would be highly appreciated.

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Need to delete duplicate of a column B based on column A and keep Column B if unique v

    Quote Originally Posted by enigmaprince View Post
    HI, Many thanks 4 ur quick reply. But didnt work...
    Please define "didn't work", perhaps I misunderstood your requirements.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

+ 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. Highlight duplicate two column and two column unique value
    By mahmudayaz in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 01-26-2017, 02:58 AM
  2. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2016, 04:36 PM
  3. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2016, 02:06 PM
  4. Replies: 2
    Last Post: 11-11-2015, 10:25 AM
  5. Replies: 0
    Last Post: 11-10-2015, 05:59 PM
  6. [SOLVED] Delete entire Rows if Column value is unique OR Duplicate is less than three
    By analystbank in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2014, 12:09 PM
  7. [SOLVED] Macro to count unique values in a column, enter it in next column, then delete duplicates
    By pmorisse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2013, 03:27 PM

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