Excel spreadsheet question

Occasional Visitor

I have a spreadsheet with 4 columns: First Name, Last Name, Level, and Account Y/N. The sheet has about 100 rows of data that populate these columns. The level can be DS1-1, DS1-2, or DS1-3. I would like sheet 2 to consist of all rows that contain DS1-1, sheet 3 to consist of all rows that contain DS1-2, and sheet 4 to consist of all rows that contain DS1-3. What would be formula be to make this happen and where would I insert it?

1 Reply

@Michael_Ohmer 

In any case that's better to use Excel Tables and the solution depends on Excel version you use.

If you are on 365 and without Table, you may name the source as Range, header could be returned as

=INDEX(Range,1,0)

and the data as

=FILTER(Range,INDEX(Range,0,3)="DS1-1")

 

Power Query is another possible option. Could be done as well with lot of legacy formulas based on AGGREGATE(), VBA is always an option. Again, depends on what do you use.