How to show all entries that meet a certain criteria in a seperate worksheet

[Gooford]

Hi,

I have been working with personal record data in multiple workbooks, using sumif, index, vlookup etc to show various required info.

What I now need to do is take all the records of people aged between 16-18 and list this in a new worksheet.

I can get all the records I need in a pivot table but it needs to be something I can add columns to in order to gather further info.

Any help would be much appreciated.

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Gooford]

Hi,

I have attached an example which shows one way which I can think of showing all the info for the records who are between 16 and 18.

However this means I will have big gaps between rows in the tables, is there a tidier way of doing this. Something like an Access query would be good, but unfortunately the original sheet (similar to BK1) has lots of gaps which I think doesnt work for data tables and queries??

I would like the final result to show all records between 16 and 18 automatically sorted in order of age with no gaps.

[NBVC]

See attached:

I added in your BK2 sheet, Row 1:

Start Date: Hard coded (allows for easy changes)
End Date: Hard Coded (allows for easy changes)
Total Count: =MAX('BK1'!$H:$H)

In H2, of BK1,

=IF(A2="","",COUNTIF(D$2:D2,">="&'BK2'!$B$1)-COUNTIF('BK1'!D$2:D2,">"&'BK2'!$D$1))

copied down as far as you want...

Then in A3 of Bk2:

=IF(ROWS($A$2:$A2)>$F$1,"",INDEX('BK1'!A:A,MATCH(ROWS($A$2:$A2),'BK1'!$H:$H,0)))

copied down and across as far as necessary...

[Gooford]

Thats great, I'll scratch my head a bit and then give you some feedback. Thanks very much.

[Gooford]

Hi,

Just to help me understand...





COUNTIF(D$2:D2,">="&'BK2'!$B$1)-COUNTIF('BK1'!D$2:D2,">"&'BK2'

I think this counting the number of records in D2 (can only be 1 or 0) that are greater than or = to 16 ($B$1) and subtracting the number of records that are greater than 18 ($D$1).

Then this number is being carried forward to the next cell as running total (which is why D$2 is fixed).



Then A3 in BK2
IF(ROWS($A$2:$A2)>$F$1,"",INDEX('BK1'!A:A,MATCH(ROWS($A$2:$A2),'BK1'!$H:$H,0)))

In Bk2 Column A, if there is an entry match it with BK1 and display what is in the corresponding cell in H. Or does the use of ROWS mean sort the information into this number of rows, as long as they excede the number in column H (below)

=MAX('BK1'!$H:$H)
$F$1 Bk2 then looks for the largest number in columns H


Hopefully thats vaguely near whats going on, would you mind letting me know?

Thanks again

[NBVC]

Pretty much

The max formula is used to determine how many records will be extracted... and that is used in the A3 formula... where it says IF(ROWS($A$2:$A2)>$F$1,"",...

so that a blank is returned instead of an error when you are in a row number that is greater than the number of records to be extracted...

[Gooford]

dont excede that rows in Column H that should say

[ticky7ock]

basketball rosters example.xlsx

NBVC,
Thank you for this post. I believe this is exactly what I need, but I can't quite replicate what you did for Gooford.

What I would like to do in the attached document is list all players from the "Players" tab in the corresponding team's roster tab. The teams need to be able to find the players regardless of their order and list all the data in the row, if possible. I feel like it is the same basic concept as Gooford's example so I thought you may be able to help.

Thanks!

[arlu1201]

ticky7ock,

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[ticky7ock]

Thank you for your reply. I'm sorry - I will make sure I double check the rules.