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

Extracting data with non unique values

M

Milton Wimpenny

New Member
Hi everyone,

First time poster, thanks for your time to review

I have a sheet with 5 separate tables (leagues) with 17 columns of data being details of planned weekly tennis matches.

I want to create a table of the 17 columns of data where the first column of my tables (date) matches a date input in cell D2. Therefore giving the players an area to reference details of their forthcoming matches

I've tried adapting a number solutions to similar posts without success, any help appreciated. Many thanks, Milton
 
Milton Wimpenny said:
Hi everyone,

First time poster, thanks for your time to review

I have a sheet with 5 separate tables (leagues) with 17 columns of data being details of planned weekly tennis matches.

I want to create a table of the 17 columns of data where the first column of my tables (date) matches a date input in cell D2. Therefore giving the players an area to reference details of their forthcoming matches

I've tried adapting a number solutions to similar posts without success, any help appreciated. Many thanks, Milton
Click to expand...

Hi Milton,

Welcome to Chandoo.org .... :)

Kindly post your sample files and sample output answer what you are expecting.
 
Thanks Syed, I've uploaded the sheet. I want to populate the area A5:R16 with the data in the tables underneath where the date = cell D2.

Kind regards
Milton
 

Attachments

  • Tennis Fixtures.xlsx
    77.4 KB · Views: 8
hii,

is this looking for ?please check

=INDEX(B$22:B$189,AGGREGATE(15,6,(ROW($B$22:$B$189)-ROW($B$22)+1)/(($B$22:$B$189=$E$2)*($B$22:$B$189<>"")),ROWS($W$5:W5)))

Thanks
Rahul shewale
 

Attachments

  • Tennis Fixtures.xlsx
    142.3 KB · Views: 6
Thanks for looking at this Rahul. As expected the solution will use functions I'm not familiar with. When I put this formula in cell A5 it returns #NUM, is it something I'm doing wrong.

Thanks for your patience
Milton
 
hii,

Please check

=IFERROR(INDEX(B$22:B$158,AGGREGATE(15,6,(ROW($B$22:$B$158)-ROW($B$22)+1)/(($B$22:$B$158=$E$2)*($B$22:$B$158<>"")),ROWS($W$5:W5))),"")


Note :change reference in red as you need.
Thanks
rahul shewale
 

Attachments

  • Tennis Fixtures.xlsx
    142.5 KB · Views: 2
Excellent! Many thanks Rahul, with a little bit of tweaking to my ranges (I wonder if I made changes after the upload) It works perfectly
=IFERROR(INDEX(A$26:A$193,AGGREGATE(15,6,(ROW($A$26:$A$193)-ROW($A$26)+1)/(($A$26:$A$193=$D$2)*($A$26:$A$193<>"")),ROWS($W$5:W5))),"")

Thanks again
Milton
 
You must log in or register to reply here.
Back
Top