Microsoft Excel - remove unwanted data from a list

Asked By shekhar on 27-Jan-12 06:33 AM

Hi

I have a list in excel of names and underneath that the customers of the sales person. I have a formula which shows the top 5 customers that each sales person has.

If the person "Shekhar" has only 4 customers it appears as below. I dont want to see the #N/A. Is there a formula to hide or make the cells white. If there are less than 5 customers this #N/A appears.
Name Shekhar

Customers
ABC
XYZ
FDO
CHI
#N/A

Can someone help with excel formula to get rid of unwanted data.

regards

shekhar
Jitendra Faye replied to shekhar on 27-Jan-12 06:47 AM
Try like this-

Private Sub Command2_Click()
Combo1.RemoveItem (0)
End Sub
D Company replied to shekhar on 27-Jan-12 06:51 AM
Hello Friend,

give a try to this

=if(A2<>clean(A2),"Possible Special Characters","")

else

The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.). It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.

http://www.excelbanter.com/showthread.php?t=95313

http://office.microsoft.com/en-us/excel-help/remove-characters-from-text-HP003056120.aspx

Hope this will help you!!
Regards
D
shekhar replied to D Company on 27-Jan-12 07:09 AM

Hi


My data looks somewhat like below;
A                B          C      D      E        F

#N/A                          
Volume (mln gal) Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD
Plan #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Actuals 0 0 0 0 0 0 0 0 0 0 0 0 0

If A1 (which comes as a result of a formula) is #N/A then i dont want to see the data underneath.

regards

shekhar

Donald Ross replied to D Company on 27-Jan-12 07:21 AM
Hello,

Shekhar if your cell is showing and N/A then I am assuming that you already have a formula in that cell and there is no data for it to get about your salesman or the formula is looking for a Text value where there is a numeric value or visa versa.

I use =if(cell="","",function)

meaning if the original formula is looking at D5 for data and it is not there, then you are just adding infront of your formula this to say if d5 = nothing "", then do nothing "", else do this formula.

one more example lets say the formula in cell A5 is =3/D5 but there is no data in D5, A5 then returns a #DIV/0! error
you still want the formula 3/D5 but not the error you would say in A5 =if(D5="","",3/D5)
so if there is nothing in D5 do nothing else 3/D5

Cheers Don

shekhar replied to Donald Ross on 27-Jan-12 09:25 AM
Sample.zip

Hi

I have attached a file for your reference. Yes i do have a formula that references the data below.

In the attached file if i select the name as Shekhar, i should not see any data in the 4th row as they are all #N/A's.
I want to see only data till row 3. But for Don, i want to see row 4 as this has data.

Also i dont want to see anything underneath row 3, if i select Shekhar

regards

shekhar

John D replied to shekhar on 27-Jan-12 09:33 AM
Hi
Place this in B4 and do the same with the others.
=IF(ISNA(VLOOKUP(C4&A15,P14:Q20,2,0)),"",VLOOKUP(C4&A15,P14:Q20,2,0))
HTH
John
John D replied to John D on 27-Jan-12 09:34 AM
OOPS
Place this in B15 and do the same with the others.
NOT B4
John
Donald Ross replied to shekhar on 27-Jan-12 09:57 AM
Shekhar,

Thank you for uploading the sample file. 

Like I guessed you already had a formula in the cell causing the N/A# value so you needed to add an IF statement.

John is correct in his formula, =if(ISNA(   again what it is doing is asking if there is nothing there or an N/A then do nothing, else do the other. 

In excel you will find there are many ways to make something happen

Have fun and post again soon.


Don
shekhar replied to Donald Ross on 27-Jan-12 11:21 AM
Hi

Thanks for all your replies.

Yes IF(ISNA does work.  But i also have some data(not formula driven) like the month names and other data like actuals.

4

#N/A    
  Jan Feb Mar
Actuals #N/A #N/A #N/A

So what i am looking for is, if against 4 there is #N/A all the data beneath that should appear as blank or simply white.
Sorry if i was not clear with my earlier question.

regards
shekhar
kalpana aparnathi replied to shekhar on 27-Jan-12 01:58 PM
Here's the fastest way to do it.
  1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
  2. Now, select the cells in one column from the top of your list to the bottom.
  3. Make sure that all the blank cells in this selected range are the rows you want to delete.
  4. Press the F5 key on your keyboard (or select Edit, Goto).
  5. Click the Special button.
  6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
  7. Now choose Edit, Delete, select the Entire Row option and click OK.
delete blank rows in excel

If you work with large lists of data in Excel, this tip will save you a lot of time. If you don't work with large lists
wally eye replied to shekhar on 27-Jan-12 04:45 PM
I would have to assume the data in the table is coming from a formula as well, you can just wrap that formula in the ISNA function like you did A1.  Or, in 2007+, IfError:

=IfError(myformula,"")

which will return a blank if myformula results in an error.
Donald Ross replied to shekhar on 27-Jan-12 08:45 PM
I agree with Wally eye, if you have an error or the #N/A in the cell then it is most probably a formula driven error, check again and either use the basic =IF( statement or one of the other +if(INSA or =IFerror if you are still having issues please upload the actual file you are working with and let one of us help. 

Don

Sri K replied to shekhar on 28-Jan-12 01:48 AM
When data is imported or copied into an Excel spreadsheet unwanted characters or words can sometimes be included with the new data.

Excel has several functions that can be used to truncate or remove such unwanted characters. Which function you use depends upon where the unwanted characters are located in the cell.

Check this link
http://spreadsheets.about.com/od/Text-Functions/ss/2011-01-31-excel-2010-truncate-text-strings-left-function.htm
shekhar replied to Donald Ross on 30-Jan-12 12:37 PM
Hi Don

Here is the file that i sent earlier. Sample.zip

If i select Shekhar in name , in Cell B15 i have #N/A,  i don't want to see any rows from  B15, they should be blank. 
B15 comes out of a formula.

If i select Don in name i should see all the names with data.
Hope this is clear.

regards

shekhar