Deleting based on IF formula code question

I'd sure appreciate some help on this. I have a work sheet titled "Filtered" upon which data through a macro on another sheet puts account numbers in column A1 down to A3500 and data affiliated with those same account numbers in Column B1 down to B3500. Due to the amount of accounts I need a macro or code that would check all of the individual cells From A1 thru A3500 and if the individual cell is either empty or contains less than four digits (numbers or letters) the entire row will delete or possibly move to the bottom of the existing data fields. (Note: not all 3500 cells may be filled as I've left a buffer for additional accounts, etc) Currently I'm not sure what would be the better option. Any suggestions would be greatly appreciated!

Hi

Rather than deleting, how about extracting a list of the offending account numbers and putting them to another sheet as an error report??? Could do this either with a macro or functions.

rylo

I'd be interested in knowing how to do what you recommend in either fashion; ie. macro or as a function. I like where you are going with the idea, I just can't (with my limited excel abilities) think how to do it. Any assistance would be appreciated!

thanks again!

Hi

Say your data is on sheet1, column A.

Option 1:
In sheet2 array enter (shift, ctrl, enter) the formulas
A1: =IF(ROW()>SUMPRODUCT(--(LEN(Sheet1!$A$1:$A$3500)<4)),"",INDEX(Sheet1!A:A,SMALL(IF(LEN(Sheet1!$A$1:$A$3500)<4,ROW(Sheet1!$A$1:$A$3500),""),ROW())))
B1: =IF(ROW()>SUMPRODUCT(--(LEN(Sheet1!$A$1:$A$22)<4)),"",SMALL(IF(LEN(Sheet1!$A$1:$A$22)<4,ROW(Sheet1!$A$1:$A$22),""),ROW()))
Copy them down as required. This will give a list of the ids and the row on which they appear. If you have a lot of blank rows at the bottom, then they will appear. Also blank rows will appear in this list as 0.

Option 2: Instead of a macro, on sheet 1 create a column that determines if the length of the ID is correct. Something like
=IF(LEN(A1)<4,"yes","")

Depending on your setup, you could then use an autofilter to show those items that have yes. Up to you how you then process them.

Option 3:
The code below will output a list of the account numbers and their row to sheet3.

Please Login or Register  to view this content.

rylo

Thanks rylo! I appreciate your very good ideas & help with the formulas and code, etc.