+ Reply to Thread
Results 1 to 6 of 6

Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

  1. 02-13-2021, 03:44 PM #1
    bjnockle
    bjnockle is offline
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,012

    Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

    Looking for formula (non-array preferred and ignoring blanks) to look into sheet named Okay, sort column A and column B alphabetically and extract values in this sheet (column A:K of sheet named Okay) into column A to column K of sheet named Here_1. Desired outcome is in sheet named Here_1 in column A to column K.

    This formula 
    Please Login or Register  to view this content.
    works for column A extraction in sheet named Here_1, but cannot get it to work for column B:K in sheet named Here_1.

    See attached sample file.

    Formula should ignore blanks (non-array preferred).

    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-14-2021, 05:09 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

    Use my previous formula in column A, and then this, copied across and down in column B. Reformat the time columns as desired.

    =INDEX(Okay!B:B,AGGREGATE(15,6,ROW(Okay!$A$2:$A$50)/(Okay!$A$2:$A$50=$A2),COUNTIF($A2:$A2,$A2)))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 02-14-2021, 10:31 AM #3
    bjnockle
    bjnockle is offline
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,012

    Re: Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

    Glenn Kennedy: The proposed formula is not properly pulling correct values (C12:C15 values are extracted incorrectly) into column C. Please help fix. See attached sample file.

    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-14-2021, 10:38 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

    Well spotted!!

    a missing $

    =INDEX(Okay!B:B,AGGREGATE(15,6,ROW(Okay!$A$2:$A$50)/(Okay!$A$2:$A$50=$A2),COUNTIF($A$2:$A2,$A2)))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-22-2021, 12:26 AM #5
    bjnockle
    bjnockle is offline
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,012

    Re: Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

    Gleen: As always, outstanding solution. Thanks a lot.
    Reply With QuoteRegister To Reply
  6. 02-22-2021, 03:24 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Extract Values into Column A to Column K of Sheet Named Here_1 ignoring blanks

    You're welcome!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract Values into Column A to Column I of Sheet Named Here ignoring blanks
    By bjnockle in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-07-2021, 09:12 AM
  2. [SOLVED] Extract Values into Column B of Sheet Named Okay
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2021, 07:42 PM
  3. [SOLVED] Extract Values in a Column Excluding Blanks
    By bjnockle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-10-2020, 01:32 PM
  4. Populate column from range of another sheet, ignoring blanks.
    By dv17 in forum Excel General
    Replies: 4
    Last Post: 08-15-2017, 05:15 PM
  5. sum corresponding column ignoring blanks
    By ashishmehraitc09 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-24-2017, 07:38 AM
  6. [SOLVED] How to copy a column data in another column, ignoring the blanks
    By Maxjprg in forum Excel General
    Replies: 5
    Last Post: 09-15-2016, 03:31 AM
  7. Macro to sum values in one column based on data in another colum while ignoring blanks
    By The Machinist in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-15-2012, 03:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1