Auto re-sorting of data- HELP - Is this possible ??

[David Brereton]

Ok. Is it possible to run a programme etc to do this ...
If I receive information in an EXCEL format but then need to split this
information out into seperate EXCEL sheets. IE The main sheet will have in
column A , maybe 500 entries. This information is sorted into order and I
require all numbers the same to be taken out onto a sheet of their own. EG.
Col A Col B
11 aa
11 bb
11 cc
12 dd
12 ee
13 ffff
13 ggg
13 hhh
13 iiiii

I need to end up with all the rows with 11 on one sheet , all with 12 on the
next sheet and all with 13 on another sheet.
This maybe impossible , but if you dont ask ...

Thanks in advance.

 

[Pete_UK]

If you are prepared to do this manually, then you can apply Autofilter
to column A, choose a value from the filter pull-down, then highlight
all the visible data and copy/paste to a new sheet. Repeat this for
the other values - no need to sort the data. If you do not have many
different values in column A then you can accomplish this quite
quickly.

You could automate the process by using a macro, first sorting the
data as you suggest and then selecting each block of numbers in turn
and then copying that data to a new sheet - all done with a single key
combination or by clicking a button.

Hope this helps.

Pete

 

[Dave Peterson]

I would do my best to keep the data on a single worksheet and use filters to see
what I want.

But if you must...

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[David Brereton]

FANTASTIC - Thank you.
To add onto this , is it possible to split the info out into seperate
workbooks as I want to save the filtered data as CSV files and doing this
only saves one active worksheet.

 

[Ron de Bruin]

Hi David

See the workbook example here
http://www.rondebruin.nl/copy5.htm#workbook