1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting date, and then enter values using sorted date from two tables

Discussion in 'Ask an Excel Question' started by JAlandhar 201718, Jul 12, 2018.

  1. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    hello all,

    I need a help, as I have two different tables with multiple values in it. What I want is to combine both table[date] to one and then sort the date, then on the other sheet I need to lookup for the sorted date. and lookup for the other values. I'm uploading a Picture of two tables.
    11.JPG

    I tried Sorting date using

    =INDEX($L$23:$L$70,MATCH(ROWS($L$23:L28),COUNTIF($L$23:$L$70,"<="&$L$23:$L$70),0))

    but in result on empty cells I get error of "#N/A", please help me to remove that too

    This pic is of result as I want but It consist of errors.
    12.JPG
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,113
    Nest it in =IFERROR().
    EX: =IFERROR(YOUR FORMULA,"")
  3. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    can you please help me to vlookup the other values, as if A29 is " ",then the result must be nil,else vlookup value from the two tables.
  4. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Please check the attached file

    Attached Files:

  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    1] In "Date" column, M19 array formula (confirmed enter with Ctrl+Shift+Enter) copied down :

    =IFERROR(SMALL(IF(CHOOSE({1,2},Table1[VOU_DATE],Table2[VOU_DATE])>0,CHOOSE({1,2},Table1[VOU_DATE],Table2[VOU_DATE])),ROWS($1:1)),"")

    2] In "Vouno" column, N19 formula copied down :

    =IF($M16="","",IFERROR(INDEX(Table1[VOUNO],MATCH($M16,Table1[VOU_DATE],0)),IFERROR(INDEX(Table2[VOUNO],MATCH($M16,Table2[VOU_DATE],0)),"")))

    3] In "Narr1" column, O19 formula copied down :

    =IF($M16="","",IFERROR(INDEX(Table1[NARR1],MATCH($M16,Table1[VOU_DATE],0)),IFERROR(INDEX(Table2[NARR1],MATCH($M16,Table2[VOU_DATE],0)),"")))

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  6. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    @bosco_yip I need one more help, regarding search box in excel, value must change according to the input value in the box

    Attached Files:

  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    1] Try to enter "A" or "AM" in the Search Box of cell D3 for testing

    2] 2 helper columns are added in Col N and Col O being for the Lookup range of the Validation list

    3] See attached file, and check the formula used in the D4 Validation list

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  8. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70

    sir,

    i need some litttle more help in this case:

    as before your formula, I was using

    =IFERROR(INDEX($J$23:$J$66,MATCH(ROWS($J$23:J26),COUNTIF($J$23:$J$66,"<="&$J$23:$J$66),0)),"")

    I was using this formula,

    I'm getting error in your formula and mine formula too, as if there are more entries with same date, I cannot sort them or those cell become empty

    111111.PNG


    Please find the attached file, I made some changes in old file.. i need that update

    Please help me

    Attached Files:

  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Hi,

    I can't find any formula mistake in your attached file

    Regards
    Bosco
  10. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    I haven't attached the formula, I need to attach please check the jpg file what error I,m getting.

    As there are multiple same dates in the table, but only one is visible on the result panel, I need all of them to be visible

    Please see it's a request
    Last edited by a moderator: Jul 23, 2018
  11. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Sir Bosco,

    Please see the attached file, there is same narration in result, I need that to get changed too.

    Please check the file.[what I was trying to say]

    Attached Files:

  12. Haz

    Haz Active Member

    Messages:
    114
    Why not sort by VOUNO instead?

    Attached Files:

  13. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Sir sometimes, the new datea are posted first and some of the entries are to be posted in back dates, So I need date wise
  14. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Can y
    Sir can you help me for this thing, like first to be sorted by vou no, then again it must be sorted by dates.. as it must be easy

    Coz the 30/06/2018 is only posted quaterly in the whole sheet, now the next date will be 30/09/2018

    And rest of the dates will be regular
  15. Haz

    Haz Active Member

    Messages:
    114
    Check the attached file.

    Attached Files:

  16. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Sir,
    I cannot use this formula for date

    =IFERROR(SMALL(IF(CHOOSE({1,2},Table1[VOU_DATE],Table2[VOU_DATE])>0,CHOOSE({1,2},Table1[VOU_DATE],Table2[VOU_DATE])),ROWS($1:1)),"")

    even after I use CTRL + SHIFT + ENTER

    No result is there only blank

    Uploading the screenshot
    A5:H25 is TABLE [VOU_LED}
    J5:Q19 is TABLE [ VOU_MCODE]

    Attached Files:

    Last edited by a moderator: Jul 25, 2018
  17. Haz

    Haz Active Member

    Messages:
    114
    Check the previous file
    You have to use {1;2} and confirm with CTRL + SHIFT + ENTER
  18. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    It Blank All the cell values :(
    Last edited by a moderator: Jul 26, 2018
  19. JAlandhar 201718

    JAlandhar 201718 Member

    Messages:
    70
    Please find the attached sheet, and help me to solve

    Attached Files:

  20. Haz

    Haz Active Member

    Messages:
    114
    Check the new file.

    Attached Files:

    Last edited by a moderator: Jul 27, 2018

Share This Page