Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Inconsistant Data Cleaning | Excel Discussion (Misc queries) |