Quicker than Auto Filter

rae30

Board Regular
Joined
May 27, 2002
Messages
147
Mr Excel Guru's

If on Sheet 1 I had a data sheet, with 1000 lines of data.

How can I automatically list all rows in Sheet1 that in ColumnA = name of current sheet.

ie sheet2="Smith" list all six Smith occurances
Sheet3="Jackson" list all 15 Jackson occurances
etc.

I know how to use the autofilter, but I was wondering if there is a quicker way to do this, as I could have 10-15 sheets to copy and paste.

Ray

:oops:
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Honestly, Autofilter's pretty quick. But here's an alternate way - create's a bit of chaff to discard (specifically extra WS's) - but it's also reasonably easy:
  1. Set up a Pivot Table with the field you want to split on as the Page field and any field that suits your fancy as your data item. No row items, no column items, i.e. your PT has just one box that's some count/sum for everything
  2. On the PT toolbar (in XL2002 it's on the "PT Button" on the PT Toolbar) find Show Pages... and tell it to Show Pages on your Page field.
  3. Use Ctrl+PgUp/PgDown to hop from page to page and double-click the summary field (should be cell [B4]) on each PT. And voila.
  4. Now just delete the PT sheets. (By holding down the Ctrl key while clicking the tabs to delete and you can kill half-dozen or more at a time
Like I said, a bit of chaff gets produced, but it has the benefit of automatically creating a listing for each unique value in your listing field. While the PT page sheets get a name you'd want, the drill-down sheets don't, so would have to rename them by hand. And if you're comfortable with VBA, you'll notice that this would not be too hard to code into a macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,079
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top