+ Reply to Thread
Results 1 to 3 of 3

Return values into one column (below each other) based on criteria in another column

Hybrid View

  1. #1
    Registered User
    Join Date
    05-05-2011
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    9

    Return values into one column (below each other) based on criteria in another column

    I have a list clock times from a bio metric clocking system. The list is sorted alphabetical. I run down the list to see who has not clocked and put down the person's name and surname. I however need to get the names below one another without having to sort.

    Biometric.jpg

    Employee Clocked (Y/N)

    Anna Joubert Y
    Anton Kruger Y
    Avhashoni Tshifaro Y
    Bongekile Mtyali Y
    Brighton Chinhaba Y
    Chand Kamya N
    Charllene Tsamba Y
    Conrad Bonorchis Y
    Dalikhaya Ntloko Y
    Duduzile Segooa Y
    Dzulani Rampfumedzi N
    Elizna Labuschagne Y
    Irfan Syed Y
    Isaac Muranganwa Y
    Jan Labuschagne N

    Result should be:
    Chand Kamya
    Dzulani Rampfumedzi
    Jan Labuschagne
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Return values into one column (below each other) based on criteria in another column

    Hi

    in F3 to be copied across


    =IFERROR(INDEX(C$3:C$29,AGGREGATE(15,6,ROW($3:$29)-2/(C$3:C$29<>""),ROWS($1:1))),"")

    or exploiting "N" in column B:


    =IFERROR(INDEX(C$3:C$29,AGGREGATE(15,6,ROW($3:$29)-2/($B$3:$B$29="N"),ROWS($1:1))),"")


    Regards
    Last edited by canapone; 04-24-2019 at 05:32 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    05-05-2011
    Location
    Pretoria
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    9

    Re: Return values into one column (below each other) based on criteria in another column

    Thank you so much. Works like a charm!!!!

+ 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: 3
    Last Post: 05-12-2018, 05:07 PM
  2. Replies: 6
    Last Post: 05-11-2018, 03:55 AM
  3. Return 3 values from column based on random value but with exlusion criteria
    By anxiolydiot in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-29-2015, 07:32 AM
  4. Replies: 6
    Last Post: 07-24-2015, 11:40 PM
  5. Return column values based on criteria
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2014, 10:55 AM
  6. Return column header based on column criteria and number value
    By bwill22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2014, 07:33 PM
  7. Replies: 19
    Last Post: 04-17-2013, 09:51 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