splitting worksheet by column entries
 

Hi,

I have a huge excel file (about 2000+ rows x 28 columns). Column A
contains two types of values - one is all numbers - like: 74925256, the
other type starts with an alphabet: like DW41724AB-ZR2H. Is there a way
I can 'split' this worksheet into two new worksheets on the same file,
so that one of the new sheets contains ALL the data for column entries
with the numerical values, and the other contains all the rows for
column entries beginning with the alphabets?

Thanks,

Joe.

splitting worksheet by column entries
 

Hi, Joe-
To make sure I understand: column A contains either a completely
numeric entry or a mix of alpha / numerics, but not both in the same
cell. Correct?

If yes, you try this process to create two separate worksheets:
1. Make a backup copy of your data! Don't risk a catastrophic loss of
data. Try this process on the BACKUP copy to make sure it works; if
you like the results then move forward from there; if not you can
revert to the saved copy.
2. Insert a "helper" column, and number each row using the Fill Series
function in Excel. This will allow you to sort the data back into its
current order. Let's assume your helper column is column A; your
numeric / alpha-numeric entries are now column B.
3. Sort the spreadsheet on column B. This will separate all the
numeric entries from the alpha-numeric entries.
4. Make a copy of this spreadsheet: right-click on the tab name,
select Move or Copy, click the Create Copy box, and choose a relative
location in the workbook for the new tab.
5. On the tab that will hold only numeric entries, delete the
alpha-numeric entries.
6. On the tab that will hold only alpha-numeric entries, delete the
numeric entries.
7. Sort each spreadsheet on the "helper" column, then delete the
helper column.

The data is now separated and back in its original order.

splitting worksheet by column entries
 

You can do what you're asking by using an Advanced Filter.

Try this:

Assumptions:
Your original data is in Sheet1
The column containing mixed data types has PartID as the column heading.
The numeric PartID's will be extracted to Sheet2
The non-numeric PartID's will be extracted to Sheet3
The criteria for extracting will be on Sheet4

Using Sheet1:
Select your data range
Type rngSource in the Name Box just above Col_A to name that range

Using Sheet4:
A1: Crit
A2: =ISNUMBER(PartID)
Select A1:A2
Type rngCrit in the Name Box just above Col_A to name that range

Using Sheet2:
Copy all of the Sheet1 data column headings to Sheet2 (PartID, amount, etc)
Select the Sheet2 column headings and 1 row below them
DataFilterAdvanced Filter (ignore the warning and click OK)
Check: Copy to another location
List Range: (press the F3 key and select rngSource)
Criteria range: (press the F3 key and select rngCrit)
Copy To: Select the Sheet2 column headings you copied from Sheet1
Click the [OK] button to extract the numeric PartID data to Sheet2

Using Sheet4:
A2: =NOT(ISNUMBER(PartID))

Using Sheet3:
Copy all of the Sheet1 data column headings to Sheet3 (PartID, amount, etc)
Select the Sheet3 column headings and 1 row below them
DataFilterAdvanced Filter (ignore the warning and click OK)
Check: Copy to another location
List Range: (press the F3 key and select rngSource)
Criteria range: (press the F3 key and select rngCrit)
Copy To: Select the Sheet3 column headings you copied from Sheet1
Click the [OK] button to extract the non-numeric PartID data to Sheet3

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Joe" wrote:

Hi,

I have a huge excel file (about 2000+ rows x 28 columns). Column A
contains two types of values - one is all numbers - like: 74925256, the
other type starts with an alphabet: like DW41724AB-ZR2H. Is there a way
I can 'split' this worksheet into two new worksheets on the same file,
so that one of the new sheets contains ALL the data for column entries
with the numerical values, and the other contains all the rows for
column entries beginning with the alphabets?

Thanks,

Joe.

splitting worksheet by column entries
 

Thanks, Dave. In fact, I was wondering if I can take this to the next
level, and create a macro for the whole process. (I get an updated copy
of this humungous worksheet every day; so a macro with a shortcut key
seems ideal) However, I'm kinda new to VB programming, so I'm not
totally familiar with all the nuances of mcreating macros. But now that
I have the logic (for the manual process), is it feasible to build that
macro ?

Thanks,

Joe.

splitting worksheet by column entries
 

Nope ..that didnt work. :(

Does this have something to do with the fact that I'm using Excel 97 ?

-Joe.

splitting worksheet by column entries
 

I can't remember if XL97 has an issue with that method or not....

You might try starting on Sheet2 when you use the Advanced Filter and select
the ranges on the other sheets, instead of using the range names.

If that doesn't work, then...XL97 may have a problem referring to other
sheets with the Advanced Filter.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Joe" wrote:

Nope ..that didnt work. :(

Does this have something to do with the fact that I'm using Excel 97 ?

-Joe.