+ Reply to Thread
Results 1 to 6 of 6

Trying to select and transpose row based on date

  1. #1
    Registered User
    Join Date
    03-27-2016
    Location
    Akansas
    MS-Off Ver
    2013
    Posts
    13

    Trying to select and transpose row based on date

    I am scratching my head here as I am trying to create a Schedule based off the expected start date from another tab in my work book. I was trying the Xlookup formula but cannot seem to get it to work. What I want to do is if the expected start date matches the date on the schedule I want to paste the job number Field Sup Copper/Fiber Address and City on the date shown in Schedule. there maybe more than one job starting on the same dayCapture.JPG
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by stkachuk07; 04-14-2021 at 08:43 AM. Reason: Added Files to post

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Trying to select and transpose row based on date

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

    Have you tried Excel's Filtering functionality?

    Or a Pivot Table which could avoid any formulae altogether.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-27-2016
    Location
    Akansas
    MS-Off Ver
    2013
    Posts
    13

    Re: Trying to select and transpose row based on date

    Richard,
    Thanks for the input. I had actually forgot to cluck upload file. I have been scratching my head. I have tried using the following formula Transpose(XLOOKUP(E4,Projects,Projects[[Job Number:Field Sup]:[Copper / Fiber]:[Address:City]],,0,2) It actually worked at one point and gave me the job numbers but I need it to show the items in the formula transposed according to dates. there may be one or more job per date

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Trying to select and transpose row based on date

    Try the following in cell B5 of the Schedule sheet:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Once activated drag the fill handle over to cell H5 and then, while B5:H5 are still selected, drag the fill handle down to cell H27.
    Note that the values in F4:F6 on the Projects sheet are text while the values in B4:H4 on the Schedule sheet are actual dates.
    Note that XLOOKUP is not supported by the 2013 edition of Excel, please update your MS-Off Ver (I imagine you are using the 365 edition).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-27-2016
    Location
    Akansas
    MS-Off Ver
    2013
    Posts
    13

    Re: Trying to select and transpose row based on date

    Works Perfectly... How would i Write it to include Address and City

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Trying to select and transpose row based on date

    Paste the following into cell B5 and then follow the same procedure for dragging as in post #4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ 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] How to select the transpose area based on cell value
    By Ipinho100 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2021, 04:54 AM
  2. Replies: 2
    Last Post: 08-10-2020, 03:42 AM
  3. VBA - Select tab based on date
    By BC TIPPING in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2017, 09:36 PM
  4. [SOLVED] Transpose Row to Column based on Date
    By neo4ride in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2015, 09:25 AM
  5. [SOLVED] Select cells based on date
    By Tortus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2015, 12:10 PM
  6. Select column based on date
    By miss_gee_gee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2014, 09:46 PM
  7. Excel 2007 : Transpose records based on date
    By aldek in forum Excel General
    Replies: 5
    Last Post: 11-03-2011, 12:35 PM

Tags for this Thread

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