#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Cleaning Data

I have a large data dump of financials that are 6 columns wide and thousands
of rows deep and is in one tab of a workbook. The data is broken up into
groups of essential data and then separated by empty space and non-essential
text. All of the data that I wish to use for calculation has a first column
that starts with a date like 1/2006. The "separator" rows do not start with
dates, they are either empty or start with text.

Without having to do it manually, I want to:

1) Count all the rows that start with dates.
2) Delete all the rows that do not.


Thanks,

Mark
  #2   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Cleaning Data

Using a copy of your date to test, this formula in a helper column, copied
down will return a 1 in all rows in which the value in column A is a number
(date) rather than empty or text. Sort on this helper column and delete all
the rows with empty results in this column. this will leave you only with
those rows with your dates in column A.........then this formula will count
how many there are.....

=COUNT(A:A)

hth
Vaya con Dios,
Chuck, CABGx3


"AMMark" wrote:

I have a large data dump of financials that are 6 columns wide and thousands
of rows deep and is in one tab of a workbook. The data is broken up into
groups of essential data and then separated by empty space and non-essential
text. All of the data that I wish to use for calculation has a first column
that starts with a date like 1/2006. The "separator" rows do not start with
dates, they are either empty or start with text.

Without having to do it manually, I want to:

1) Count all the rows that start with dates.
2) Delete all the rows that do not.


Thanks,

Mark

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Sort pages? David Excel Discussion (Misc queries) 15 May 13th 05 11:33 PM
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM
Inconsistant Data Cleaning gallegos1580 Excel Discussion (Misc queries) 1 January 10th 05 09:19 PM


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"