• 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...

  • 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

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
 
Please check the attached file
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
 

Attachments

  • demo file(Lookup).xlsx
    14.7 KB · Views: 9
@bosco_yip I need one more help, regarding search box in excel, value must change according to the input value in the box
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
 

Attachments

  • Search on Basis of value(1).xlsx
    18.9 KB · Views: 5
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


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
 

Attachments

  • demo file(Lookup) with addition.xlsx
    14.1 KB · Views: 5
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:
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]
 

Attachments

  • demo file(Lookup) with addition.xlsx
    14.1 KB · Views: 3
Why not sort by VOUNO instead?
 

Attachments

  • demo file(Lookup) with addition (1).xlsx
    14.2 KB · Views: 4
Can y
Why not sort by VOUNO instead?
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
 
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]
 

Attachments

  • Screenshot 2018-07-25 10.59.16.png
    Screenshot 2018-07-25 10.59.16.png
    175.8 KB · Views: 5
Last edited by a moderator:
Check the new file.
 

Attachments

  • 2018-19 - Copy (1).xlsm
    83.5 KB · Views: 8
Last edited by a moderator:
Back
Top