Closed Thread
Results 1 to 17 of 17

Multiple lookups ordered by timestamp

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Multiple lookups ordered by timestamp

    Hi,

    I have two tables, where one has the rawdata ordered in a vertical fashion and the other shall contain the results ordered in a horizontal fashion.

    The problem can be described as follows:

    Transfer results from Table 1 to Table 2 by:

    For each row in Table2, match either of Phase1, Phase2 and Phase3 with the column Phase1, 2, 3 in Table1.
    For each column in Table2, match the Codes with the Codes in Table1.
    For each coulmn in Table2, match the Order number for the given Code with the timestamp in columns Created On and Created At in Table1.
    The oldest timestamp shall give the lowest order number for the given code.
    If there are duplicate timestamps for a given match, both shall be counted with and the first occurrence shall return the first occurring result value and the next one(s) shall return the succeeding result value(s).

    Could you please have a look at the attached workbook and see if this can be solved?

    Best regards,
    Marbleking
    Attached Files Attached Files

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

    Re: Multiple lookups ordered by timestamp

    Please try at J4

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

  3. #3
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    39

    Re: Multiple lookups ordered by timestamp

    You need an Array formula using nested Index Small If Row.
    =IFERROR(INDEX($E$1:$E$27,SMALL(IF((($A$4:$A$27=$G4)+($A$4:$A$27=$H4)+($A$4:$A$27=$I4))*($B$4:$B$27=J$3),ROW($A$4:$A$27)),J$2)),"")

    remember to press Ctrl Shift Enter to complete the formula.

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

    Re: Multiple lookups ordered by timestamp

    Another one

    =IFERROR(INDEX($E:$E,AGGREGATE(15,6,ROW($E$4:$E$27)/MMULT(($G4:$I4=$A$4:$A$27)*($B$4:$B$27=J$3),{1;1;1}),J$2)),"")
    Attached Files Attached Files

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

    Re: Multiple lookups ordered by timestamp

    @Bo_Ry
    I like your AGGRERATE much, but anyway, your solution does not take time stamp in to account
    I am working on this and come back soon

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,988

    Re: Multiple lookups ordered by timestamp

    It quite like a monster, but it works:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    These are some very fascinating solutions, y'all! I like the AGGREGATE version too, and that the timestamp was included. Thus, the function will be robust if the columns in Table 1 are sorted differently. Bebo021999's expanded solution seems to be very close, but it actually returns the last result first for each of the Code values. I tried to change from "14 - Large" to "15 - Small" in the AGGREGATE function, but this only resulted in returning wrong results. Could this be looked at?

    Also, I unfortunately forgot one important element to consider: There is going to be another criteria column in Table 1 which contains the values "true" or "false" in text format, and only the rows in Table 1 for which this column says "false" shall be included anywhere in Table 2. I hope this doesn't complicate too much.

    Thanks for your contributions so far.

    Regards,
    Marbleking

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,988

    Re: Multiple lookups ordered by timestamp

    Pls update the sample with new case of sorting table 1 and new column of true/false

  9. #9
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    Hi! Attached you'll find the updated workbook including comments! Regards, Marbleking
    Attached Files Attached Files

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

    Re: Multiple lookups ordered by timestamp

    Please try at L4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    Thanks Bo_Ry! This looks great; could you please also include the date in the timestamp, as well as time?

    Also, if you could update the formula from your first reply in cell L3 for fetching the Codes, such that only Codes from Table 1 for which Phase numbers can be found in Table 2 and for which also the Criterion in Table 1 is "false" will be included, and then sorted across the header row:

    Please Login or Register  to view this content.
    Regards,
    Marbleking
    Last edited by Marbleking; 08-07-2020 at 05:21 AM.

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

    Re: Multiple lookups ordered by timestamp

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


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

    Confirm by Ctrl+Shift+Enter
    or COMMAND+RETURN for Mac
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    Thanks, Bo_Ry!

    Having looked more closely at the dummy data that I pasted in Table 2, I now see that I made an error: The values in range S8:S11 should have been moved to R8:R11 when I updated the file with the true/false criteria and removed some values for rows that I marked "true".

    I therefore tried to come up with a solution myself, and think I have found one. Unfortunately the IF statement sometimes produces and array of values that starts with FALSE, which doesn't work well with the AGGREGATE function. I therefore have to multiply with 1 to turn it into a zero, but that creates a problem with #SPILL! errors in cases where the array doesn't start with FALSE. To handle this I found out that I could use the SWITCH function to turn zero into one if the whole row_num expression in the INDEX formula turns out to return zero. Long story short; the formula that started out quite neat, suddenly turned out to be quite large. Perhaps some of you would like to have a look at it and see if it can be improved?

    I also looked at Bo_Ry's formula in cell L3, and it doesn't remove Code values which isn't being used by phase numbers that occur in Table 2. Could this be solved?

    Here is the formula I wrote in cell L4 (without the SWITCH statement):

    Please Login or Register  to view this content.
    And here it is, including the SWITCH statement:

    Please Login or Register  to view this content.
    Best regards,
    Marbleking
    Attached Files Attached Files

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

    Re: Multiple lookups ordered by timestamp

    This will be my last try.

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


    L4
    =IFERROR(INDEX($F:$F,MOD(AGGREGATE(15,6,ROW($E$4:$E$34)+ROUND(($D$4:$D$34+$E$4:$E$34)*10^9,-6)/MMULT(($I4:$K4=$A$4:$A$34)*($B$4:$B$34=L$3),{1;1;1})/($C$4:$C$34="false"),L$2),1000)),"")
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    Much appreciated, Bo_ry! And very elegant.

  16. #16
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    EDIT: Please refer to the latest post for updated information.
    Last edited by Marbleking; 08-20-2020 at 02:25 PM.

  17. #17
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Multiple lookups ordered by timestamp

    Hi,

    I have reworked Bo_Ry's formula to work out a solution for this problem that requires the use of a lot of "helper" tables. The solution will not be practically feasible for bigger data sets and I was wondering if someone could have a look and see if it was possible to create formulas that don't require the use of the very cumbersome helper tables.

    I am looking to keep Table 8 and Table 10 with mutually independent formulas, in addition to the raw data in Table 1.

    Regards,
    Marbleking

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dynamic ordered list, pulling from multiple sheets
    By ostie in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-21-2019, 04:11 PM
  2. [SOLVED] find duplicate cases across multiple columns that are randomly ordered
    By kh444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2018, 07:24 AM
  3. [SOLVED] Extract ordered values for multiple repetitions of an item?
    By Dreamweaver8882004 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-27-2018, 02:05 PM
  4. Create a summary report of ordered items from multiple worksheets help
    By scsuflyboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2017, 10:23 AM
  5. [SOLVED] Count if there is a new order/ sum if there are multiple items being ordered by one person
    By sweetlakia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2016, 02:50 PM
  6. Referencing Multiple Lists and Static Cells Ordered
    By portokie in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-14-2012, 05:48 PM
  7. More than Multiple Lookups: Conditional Multiple Lookups
    By mohitspamz in forum Excel General
    Replies: 6
    Last Post: 11-01-2009, 03:32 AM

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