Excel Help

kicks66

kicks66

kicks66

Associate
Joined
18 Jul 2004
Posts
1,866
ive got a load of data with hyphens - in it, is there a way to get it to just display text characters?

also, is there a way to get excel to count all cells that are similar or the same as each other without a set criteria (ie cat, bat, bat, mat, rat would be grouped as 1 cat, 2 bat, 1 mat, 1 rat)
 
kicks66 said:
also, is there a way to get excel to count all cells that are similar or the same as each other without a set criteria (ie cat, bat, bat, mat, rat would be grouped as 1 cat, 2 bat, 1 mat, 1 rat)
Click to expand...


Oooh, you would have to do a pivot table/chart/report thingy for that I think.
 
If you type "count unique" into Excel's help, then choose "Count the occurence of unique entries" you can follow the help and have a go. I am using version 2003 by the way.
 
As for your first question, you want to remove the hyphens?
You should be able to select the cells you want the hyphen removing from and do a Find/Replace. Find - but leave the replace with box empty.
 
ive got the hyphens removed and stuff

ill try your other suggestion for the data, the pivot table didnt seem to agree with me :/
 
select the data you want to pivot, ensuring that it has labels at the top of the colums. Click 'pivot tables/charts' on the 'data' menu. Click next/next/finish and the pivot chart should open in a new sheet. Then it's basically just a case of dragging and dropping the column headings into the format you want; excel will then calculate the totals for you.


Slightly different depending on which version of excel you're using though.
 
ive got another dilemna. ive soted my original thing out. ive got a vlookup that will sometimes lookup a value when it doesnt exist, which will then show as #N/A. is there anyway i can get it so that it will just show a blank space?
 
You need to use an IF statement, and ISERROR - for example :-

=IF(ISERROR(VLOOKUP(27,test,2,0)),"",VLOOKUP(27,test,2,0))

The above looks in the range 'test', to find the value 27, and then returns the corresponding value in the 2nd column - you must have that bit sorted if you have your vlookup working ok! So all you need to do is what ive done above - put your vlookup in the brackets, and Excel will check if the returned value is n/a (by using ISERROR), and if it is, leaves the cell blank by using "".If vlookup doesnt return an error, it just performs the lookup as usual.

I think that will work ok, but am quoting from memory, so apologies if it doesnt work without a tweak - but hopefully it will at least provide a pointer as to how to make it work. Hope that helps! :)
 
You must log in or register to reply here.
Back
Top Bottom