+ Reply to Thread
Results 1 to 5 of 5

Identifying unique data from multiple columns/rows without Excel 2016 Get and Tranform

  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 without Excel 2016 Get and Tranform

    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?

    I am using Excel 2010, so don't have the Get and Transform options on the Data ribbon

    Many thanks

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,441

    Re: Identifying unique data from multiple columns/rows without Excel 2016 Get and Tranform

    There is also an excel 2010 power query addin that can work for you
    https://www.microsoft.com/en-US/down....aspx?id=39379

    Also for undoubling suppliernames without exact match there is a fuzzy lookup addin
    https://www.microsoft.com/en-us/down....aspx?id=15011

  3. #3
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,565

    Re: Identifying unique data from multiple columns/rows without Excel 2016 Get and Tranform

    Is the attached what you want to do.
    it uses vba/macro and can be adjusted to work on any size sheet/array.
    torachan.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,565

    Re: Identifying unique data from multiple columns/rows without Excel 2016 Get and Tranform

    code correction
    I had removed headers without moving the data up a row.
    torachan.
    Last edited by torachan; 02-14-2019 at 08:41 AM. Reason: a 77 year olds brain freeze

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

    Re: Identifying unique data from multiple columns/rows without Excel 2016 Get and Tranform

    Cheers thanks - just trying to edit the range now. Thanks for your help.

+ 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] Identifying unique data from multiple columns/rows
    By Paddymaw in forum Excel General
    Replies: 3
    Last Post: 02-12-2019, 09:08 AM
  2. Excel 2016 transposing data in rows and columns
    By tjvcaio in forum Excel General
    Replies: 8
    Last Post: 04-06-2018, 03:34 PM
  3. Replies: 6
    Last Post: 05-30-2016, 01:59 PM
  4. Replies: 4
    Last Post: 03-07-2013, 11:42 AM
  5. Duplicate rows, delete columns w/same data, combine columns w/unique data, Mac Excel 2011
    By msmcoin in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-03-2013, 02:10 PM
  6. Replies: 4
    Last Post: 01-16-2013, 10:06 PM
  7. Identifying unique days in excel, with multiple identifiers
    By PosiJoel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-17-2012, 06:33 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