Creating a list from specific criteria on to a new tab

[Mikelambnj]

Is there a formula I can use to do this. Here’s the scenario, on the first tab are say ten columns of data. One column is month. That column looks something like this:

May
June
March
May
Sept
May
June

On a new tab I want to paste those ten columns, but only the rows that have May. I want those rows to be next to each other, not separated by blank rows. I know I could filter the month column and paste to the new tab, but this is operation needs to be many times in the same spreadsheet. I don’t want to keep filtering and pasting over and over. I would rather have tabs that have a formula pointing to the first tab that would accomplish the same thing. Thank you.

[AliGW]

Welcome to the forum.

I don't believe you are really using the ancient Excel 2007 - is it not Excel 365? If so, please update your profile.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Mikelambnj]

Sad, but true. 2007.

[AliGW]

Yes, very sad. So,let's have a look at your sample workbook. It rules out PowerQuery, sadly, and a host of other possibilities.

[Mikelambnj]

Attached is a sample workbook. There really will be thousands of records.

[Mikelambnj]

The reason I want to set this up is because I would be using it each week. I would paste new data on the first tab each week, and want the other tabs to populate automatically.

[JeteMc]

The array entered formula** proposed is:

Please Login or Register  to view this content.

Note that the formula references cell L1 which is populated using a data validation in cell drop down.
The drop down references a list of months that is in column T and which may be moved and/or hidden for aesthetic purposes.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.