Multiple values returned based on criteria

[marc01]

Hi all,

I am looking for a point in the right direction with regards to speeding up the process of filtering, copying, and pasting some raw data that I have.

I have a table with all my raw data (based on observations from a tennis match), and at the moment I want to, for example, copy all the data in a certain column when Roger Federer was serving, and his 1st serve went in, and he won the point. I then copy the remaining data in my column of interest and paste it into a new table on another sheet. At the moment I am having to manually filter through the table by selecting the heading and then the results I want to see. As you can imagine this takes some time.

How would I go about speeding this process up using excel formula?

Many thanks in advance,

Marc

 

[mohsan_akhtar]

It would be possible to do in an array formula on another sheet, but could become chunky/cumbersome over time.
Why are you looking to use formula an not automate the process via VBA (macro's)?

If you have no coding experience, recording a macro to complete the above would still work relatively well, as doesn't look like you want anything too extravagant done.

 

[marc01]

Many thanks for the quick reply.

I don't have any experience with VBA's unfortunately (something I want to learn one day!)

I am using the raw data to help produce a report, and because the report is already complete in theory it should not get any more complicated. Just trying to speed up the process of putting the correct data in the correct place. I suspect it would be a formula that uses IF, INDEX and MATCH.....but not sure if anything else?

 

[marc01]

sorry forgot to say that the number of rows of raw data will be different for every match so I need something that will allow for this. Was thinking that with a formula I would either drag down as far as I needed, or set the array to 500 rows for example as I won't ever have any more than this amount of raw data