+ Reply to Thread
Results 1 to 2 of 2

condense column into unique discrete cells

  1. #1
    Registered User
    Join Date
    04-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    condense column into unique discrete cells

    i have a multi-worksheet file that i'm working with that includes several report worksheets and one data worksheet. I need to be able to copy-paste a large amount of exported data into the data worksheet and have it auto-populate onto the other worksheets. because of this, i need a formula that can be put solely on the destination sheets and not on the data sheet. i cannot use a macro as they're restricted on my client's machine. it must be a formula.

    column B in the data worksheet is a list of names with multiple duplicates and blanks. i can remove the blanks and alphabetize if required pre-entry. here's an example:

    Last Name, First Name:
    WXXXXXX, CHRISTOPHER
    WXXXXXX, CHRISTOPHER
    WXXXXXX, CHRISTOPHER
    WXXXXXX, CHRISTOPHER
    WXXXXXX, CHRISTOPHER
    WXXXXXX, CHRISTOPHER

    WYYYYYY, REBECCA
    WYYYYYY, REBECCA

    TXXXXXX, YI-WEN

    i need this list to pull as follows:
    WXXXXXX, CHRISTOPHER
    WYYYYYY, REBECCA
    TXXXXXX, YI-WEN

    etc. the list will continue to grow as things get more complex. i think a pivot table may work, but i'd have to build seperate tables per month. a formula would be much easier.

    i've tried using some match-based formulas i've found on the web, but they simply don't pull correctly cross-worksheet. assume that column B on the data worksheet is the names, starting at B3 and continuing indefinitely. also please assume that the output starts at B11 and continues for thirty lines.

    is this possible? i think i spent two hours this morning trying to beat square formulas into my round requirement hole, but i just can't seem to get them to work.

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: condense column into unique discrete cells

    Have a look at the attachment.
    The "iferror" part of the formula only works in Excel 2007/2010.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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