Extract Cell Results for all Cells with Certain Values

Hi,

I'm looking for something that can help me extract all the data for a certain value in a long list of data into a separate column.

For example, in a soccer season the data might look something like this (3 separate columns)

Arsenal | 2 | 1
Manchester United | 0 | 3
Liverpool | 0 | 2
Arsenal | 3 | 1
Everton | 1 | 2

I'm looking for the data to appear in a new columns only for the Arsenal value as:

Arsenal | 2 | 1
Arsenal | 3 | 1

I've managed to do this with formulas but it completely slows the whole spreadsheet down and makes it really difficult to work with.

Is there any VBA I can use to help me please? Unfortunately, I don't have much knowledge of VBA only formulas.

Thanks,
Baron

Have you tried AutoFilter?

I might be missing something but that would filter the data in the column currently holding the data. Whereas I need the data in separate columns.

So you know how to filter...

If you filter the rows, you can copy the result and paste to wherever you want.
Am I missing something?

But I will be doing this for 20 different values on a weekly basis so would prefer if it worked automatically,

OK, then we need more info.

If you upload a sample workbook clearly showing what you have and what you want to have would help.

Ok, I have attached an example. I hope that shows what I need but please let me know if anything requires further explanation.

Yes, that works a treat, thank you so much! There's just one small extra thing I need it to do and that's to sort each column/team by reverse date order, so the latest date first. Would that be possible?

Then this should do

Please Login or Register  to view this content.

Perfect, many thanks jindon!

Hi jindon,

I'm trying to apply your code to another sheet but don't seem to be getting anywhere unfortunately.

I was wondering if you could help me make this new sheet do the same thing please? It has a bit more data but I want to do the same thing with the Home Team & Away Team and date in the Home xG and Away xG columns please.

Many thanks,

Baron

Change to

Please Login or Register  to view this content.

Thanks for the reply, jindon. I tried that but it just seems to stall and nothing happens or can be clicked in Excel. Any ideas?

It is working here and very strange if it doesn't do anything.

It should at least copy something.

I think the issue might be that there is data in columns G-L (G-J are hidden). When I copy just columns A-F (which is the data the code is copying) to another sheet the code works but the copy starts from column H.

Try this with the original sheet.

Please Login or Register  to view this content.

Same issue I'm afraid.

Here's a result from the code.