+ Reply to Thread
Results 1 to 12 of 12

Lookup value copy into the corresponding column in date order

  1. #1
    Registered User
    Join Date
    04-27-2016
    Location
    Saltburn
    MS-Off Ver
    2010
    Posts
    17

    Lookup value copy into the corresponding column in date order

    Hi,

    I need to be able to sort through a vast amount of data and extract cells to copy under the relevant column heading. To assist in incident trend capture I wish to search through a date sorted list of incident events, then pull the events that match hostnames under the column for that hostname, in date order.

    The range to search is on sheet2

    Please see the attached spreadsheet
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Lookup value copy into the corresponding column in date order

    Hi there. try this Array Formula in B2.

    =INDEX(Sheet2!$A$1:$A$20,MATCH(1,(Sheet1!$A2=TEXT(Sheet2!$A$1:$A$20,"mmmm"))*ISNUMBER((FIND(B$1,Sheet2!$B$1:$B$20))),0))

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    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.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Lookup value copy into the corresponding column in date order

    do you specifically require backwards compatability with Excel 97-2003???

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup value copy into the corresponding column in date order

    Do you want to return the dates to Sheet1 or the incident text?

    You can use this array* formula in B2 to get the incidents:

    =IFERROR(INDEX(Sheet2!$B$1:$B$20,SMALL(IF(ISNUMBER(SEARCH(B$1,Sheet2!$B$1:$B$20)),ROW($A$1:$A$20)),ROWS($1:1))),"")

    or this one to return the dates:

    =IFERROR(INDEX(Sheet2!$A$1:$A$20,SMALL(IF(ISNUMBER(SEARCH(B$1,Sheet2!$B$1:$B$20)),ROW($A$1:$A$20)),ROWS($1:1))),"")

    * NOTE that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

    The formulae will list the items under the appropriate headings, all bunched up, so the months in column A are not needed.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    04-27-2016
    Location
    Saltburn
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup value copy into the corresponding column in date order

    Both, thanks for the quick response. Not sure I was clear enough.

    I wish to return the Incident text on sheet2 (containing the matching hostname) to the relevant column (each column title is a unique hostname) on sheet 1. I would like it to be sorted in date order (against a row on the left but do not need the dates copied from sheet2. changed spreadsheet to reflect terrible wording!)

  6. #6
    Registered User
    Join Date
    04-27-2016
    Location
    Saltburn
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup value copy into the corresponding column in date order

    Both, thanks for the quick response. Not sure I was clear enough.

    I wish to return the Incident text on sheet2 (containing the matching hostname) to the relevant column (each column title is a unique hostname) on sheet 1. I would like it to be sorted in date order (against a row on the left but do not need the dates copied from sheet2. changed spreadsheet to reflect terrible wording!)

    It only needs to work on excel 2010 - couldn't upload other format.
    Attached Files Attached Files

  7. #7
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Lookup value copy into the corresponding column in date order

    Hi
    Here is a formula (non array formula) that uses the function AGREGGATE that is not compatible to previous versions Excel2007.
    It lists for each server all incident dates
    =IFERROR(AGGREGATE(15,6,Sheet2!$A$1:$A$20/(FIND(Sheet1!B$1,Sheet2!$B$1:$B$20)>0),ROWS($A$1:$A1)),"")
    See the file
    Attached Files Attached Files

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup value copy into the corresponding column in date order

    Yes, I was thinking that you might have more than 1 entry per month, but your latest file shows another dilemma - you might have a varying max number in each month.

    I'm just about to go out, but I'll come back to it later (unless someone else chips in).

    Pete

  9. #9
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Lookup value copy into the corresponding column in date order

    Quote Originally Posted by Pete_UK View Post
    Yes, I was thinking that you might have more than 1 entry per month, but your latest file shows another dilemma - you might have a varying max number in each month.

    I'm just about to go out, but I'll come back to it later (unless someone else chips in).

    Pete
    Hi Pete,
    My proposal formula list all incident dates for each server.

  10. #10
    Registered User
    Join Date
    04-27-2016
    Location
    Saltburn
    MS-Off Ver
    2010
    Posts
    17

    Re: Lookup value copy into the corresponding column in date order

    Hi Jose,

    I'm not so interested in listing the dates, but rather aligning a cell that references a hostname to a column for that hostname. In date order but doesn't have to display the exact date, just tied into a month. See the latest spreadsheet for an example.

    Thanks

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Lookup value copy into the corresponding column in date order

    I daresay there's a way to do this exactly as required, but I can't think of it. Here's an alternative suggestion - which permits (as set up - can be adjusted) a maximum of 10 incidents per server per month.


    Without referring to the sheet, this array formula will mean nothing, but we're encouraged to post formulae:

    =IFERROR(INDEX(Sheet2!$B:$B,SMALL(IF(TEXT(Sheet2!$A$1:$A$28,"mmmm")=Sheet1!$A2,IF(ISNUMBER(FIND(Sheet1!B$1,Sheet2!$B$1:$B$28)),ROW(Sheet2!$B$1:$B$28))),MOD((ROWS($1:1)-1),10)+1)),"")
    Attached Files Attached Files

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup value copy into the corresponding column in date order

    Hi, I'm back...

    I would have suggested something like Glenn's solution, i.e. have a fixed number of rows for each month in Sheet1.

    If you want to bunch them all up so that you do not see a lot of empty rows, then you could use this formula in H2, for example:

    =COUNTIF(B2:F2,"?*")

    and copy this down to the bottom of your lists - this just counts the number of non-blank entries in columns B to F on each row. Then you can apply autofilter to column H and deselect zero from the filter drop-down.

    Hope this helps.

    Pete

+ 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: 4
    Last Post: 12-02-2014, 03:46 AM
  2. Replies: 1
    Last Post: 12-01-2014, 03:01 PM
  3. [SOLVED] Lookup and display values in order based on different date criteria.
    By sandman85 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2014, 02:57 AM
  4. [SOLVED] Not returning a date in a lookup when dates are out of order
    By nappy1001 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-10-2014, 11:23 AM
  5. Lookup and copy last months order BUT have similar names
    By king10001 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2013, 05:39 AM
  6. Copy Data to a New Sheet with Date Order
    By deathdream in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-19-2013, 02:35 AM
  7. Lookup values in a column and display them in order with no gaps
    By Snaggle22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2005, 07:07 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