+ Reply to Thread
Results 1 to 4 of 4

Display names listed in multiple columns.

  1. #1
    Registered User
    Join Date
    06-06-2018
    Location
    DC
    MS-Off Ver
    2013
    Posts
    2

    Display names listed in multiple columns.

    Hello,

    I have a giant payroll sheet I need to filter. It lists every paycheck for every employee within a calendar year. So it has over a million rows. It also has three columns for names, last, first and middle initial. I need to create a formula that will look over all three columns and display only once on a different sheet each unique name. For example, my name Robinson, Brent and blank in the middle initial column can occur 20 times, but I need it to only list me once on the other sheet.

    Thank you,

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Display names listed in multiple columns.

    On the Ribbon, Data, Sort & Filter, Advanced and follow prompts for a display of unique records.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-06-2018
    Location
    DC
    MS-Off Ver
    2013
    Posts
    2

    Re: Display names listed in multiple columns.

    Thank you for your quick response.

    Now I am running into a problem.

    I used the following SUMIF formula;

    =SUMIFS(Sheet1!I2:I2235,Sheet1!E2:E2235,A2,Sheet1!F2:F2235,B2,Sheet1!G2:G2235,C2)

    I'm trying to get it to sum the total amount paid by paycheck if the First Name matches the Middle initial matches the last name. The results are returning either $0 or Tens of millions of dollars.

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Display names listed in multiple columns.

    set up a helper column in first sheet like: =TRIM(E2)&" "&TRIM(F2)&" "&TRIM(G2) in H2 for example, assuming that in these columns are your names.
    Copy it over to your second sheet, A2 and remove duplicates.
    Then =SUMIF(Sheet1!I2:I2235,Sheet1!H2:H2235,A2)
    Assuming that I column has numerical values, it should return the summed values for each name.

+ 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. Counting consecutive listed names
    By Allen_dulles in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-02-2016, 01:59 AM
  2. [SOLVED] trying to add numbers that correspond to multiple names listed in column using SUMIFS
    By JoeJones in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-12-2014, 07:25 PM
  3. Replies: 7
    Last Post: 04-23-2012, 02:27 PM
  4. Writing outcomes and then names listed below
    By parachutistbynight in forum Excel General
    Replies: 6
    Last Post: 08-02-2009, 08:53 PM
  5. Macro to display the extra names between 2 columns
    By shuresh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2008, 06:01 AM
  6. [SOLVED] How to create menu for listed names?
    By Eric in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2006, 07:35 PM
  7. [SOLVED] Count If-e store names listed.
    By Jim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2005, 11:05 AM

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