+ Reply to Thread
Results 1 to 9 of 9

Extract every 2nd row data based value not blank and criteria in column headers

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

    Extract every 2nd row data based value not blank and criteria in column headers

    Attached is a sample with desired results. Basically, I am trying to extract the second row that is not blank, based on header criteria in order. If value is not blank, then return the row above as well, and the row header.
    Hopefully, someone can point me to a solution.
    Attached Files Attached Files
    Click the * to say thanks.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    Does your example match reality?
    can a time be repeated? None are in the example
    Is it only sorted on start time? End time has no bearing on the order
    In reality how many rows will it apply to?

    Not that I have access to it, it seems that power pivot would sort this out, I am sure Ali or someone else will have a view!

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    Time is sorted base on start-time range (9:30,11:30,...)

    In C2:

    Please Login or Register  to view this content.
    D2:
    Please Login or Register  to view this content.
    E2:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT

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

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    Does your example match reality?
    can a time be repeated? None are in the example
    Is it only sorted on start time? End time has no bearing on the order
    In reality how many rows will it apply to?
    It matches the reality, but not entirely. It applies to about 100 rows, but is part of a calendar and this is only for one month. Under it I will have October, November and so forth. I will use a macro to change the range of the formula when the current month has passed.
    A start time will never be repeated, and the order is based only on start time, end time doesn't matter as they will never be crossed.

    @bebo, thank you for your solution, but B2 will be a dropdown and whenever it will be changed, the formula should automatically return the list of times per day and I cannot use a static range as you have here: Data!$H$3:$H$21. Basically if I change the date from 7th of Sep to 25th of Sep, then it should return those time stamps with all the details.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    OK, You are right, Data!$H$3:$H$21 range is hard core range, I change it to dynamic range:
    INDEX(Data!$B$3:$AE$21,,MATCH($B$2,Data!$B$1:$AE$1,0))
    then it should works

    See attachment
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    Please try at
    C2:D2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    E2
    =IFERROR(INDEX(Data!$A:$A,MOD(AGGREGATE(15,6,INT(SUBSTITUTE(LEFT(INDEX(Data!$B$2:$AE$99,,MATCH($B$2,Data!$B$1:$AE$1,)),5),"-",)*24000)*10^4+ROW(Data!$A$2:$A$99),ROWS(C$2:C2)),10^4)),"")
    Attached Files Attached Files

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

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    @bebo and @Bo_ry, let me start by thanking you as both solutions work as desired.
    I'm hopping that you could help me out with this as well. If my range is calendar based, so I will have the data copied below, and a new row for the days of the month as per the sample, by using the helper column which is the start of the month, would be possible to bring the data by changing the month instead of having to change the ranges in the formula?
    Thank you in advance for all the help you can provide
    Attached Files Attached Files

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    Please try at
    C2:D2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Formula can be shorter with volatile offset.
    Attached Files Attached Files

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

    Re: Extract every 2nd row data based value not blank and criteria in column headers

    Thank you Bo_ry. Perfection!

+ 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] Extract multiple row headers and column headers if criteria is met in multiple columns
    By PaulM100 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-20-2019, 04:56 AM
  2. [SOLVED] How to sum columns based on two column headers as criteria
    By MyStix01 in forum Excel General
    Replies: 3
    Last Post: 11-17-2018, 11:20 AM
  3. [SOLVED] complex if with index & match formula based on criteria in a column to extract data
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2018, 01:06 PM
  4. Extract and Merge Data based on Column Headers
    By Krix in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2017, 02:08 PM
  5. Replies: 6
    Last Post: 12-13-2016, 03:06 PM
  6. Replies: 0
    Last Post: 06-04-2015, 07:57 AM
  7. [SOLVED] How do I: Extract column headers based on lowest number in sequence
    By WolfJack in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2006, 04:30 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