+ Reply to Thread
Results 1 to 4 of 4

Identifying unique data from multiple columns/rows

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    13

    Identifying unique data from multiple columns/rows

    Hi

    just wondering if anyone knows how to do this?

    I have a sheet with multiple columns of data (and about 5000 rows), of suppliers. Many of these supplier names are repeated many times across about 50000 cells of data. I would like to generate a list of all the unique supplier names across all these columns/rows. I have tried using remove duplicates, but that only works for each column, and of course you can still have the same name repeated in multiple columns. I was just
    wondering if there was a quick way of selecting a range and returning all the unique names in that range?

    Any help most appreciated.

    Thanks

  2. #2
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    499

    Re: Identifying unique data from multiple columns/rows

    plz share a file

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,591

    Re: Identifying unique data from multiple columns/rows

    You can do this with formulae, however you might find it easier (given volume) to use following steps (2016):

    1. on your sheet with the raw data go to Data tab on Ribbon, select "From Table"
    2. select your table range as appropriate
    3. in resulting window, where you should now see your table embedded in a new dialog, hold shift and select final column (all columns should now be selected)
    4. go to transform tab and select Unpivot Columns
    5. select first column and right click, "remove"
    6. right click on remaining column and select "remove duplicates"
    7. click "X" to close {Keep changes}

    a new table will generate with just the unique values.
    Last edited by XLent; 02-12-2019 at 06:01 AM. Reason: removed superfluous step

  4. #4
    Registered User
    Join Date
    02-05-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    13

    Re: Identifying unique data from multiple columns/rows

    Thanks - that worked perfectly.

+ 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. Replies: 1
    Last Post: 03-28-2017, 03:50 AM
  2. Replies: 6
    Last Post: 05-30-2016, 01:59 PM
  3. [SOLVED] Identifying duplicate data using multiple columns
    By DuckMan72 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2013, 05:28 PM
  4. Replies: 4
    Last Post: 03-07-2013, 11:42 AM
  5. Replies: 4
    Last Post: 01-16-2013, 10:06 PM
  6. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  7. Identifying Unique Rows
    By Kestrel1492 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2005, 12:56 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