Excel 2002, formula to find/sort number of times a value appears

Forums › Microsoft Windows › Excel 2002, formula to find/sort number of times a value appears

rscole86

4980 posts

Uber Geek

Moderator

Trusted

Lifetime subscriber

#64472 16-Jul-2010 08:11

I have a spreadsheet with approx 1000 entries, and growing every day.

From column E, I want to be able to rank/sort the number of times a random value appears.
Every value in column E is either 6 characters, or 6 characters [space] / [space] 6 characters. The cell could also be blank.
Eg,

123ghf or 123ghf / 12ggby

Ideally I would like to have this 'sorted' within the original table, as the outcomes of each value in column E is in column L. And its this direct relationship that is really important.

I hope this makes sense?

Cheers!

alasta

6709 posts

Uber Geek

Trusted

Subscriber

#352219 16-Jul-2010 10:09

From my understanding of what you've described, I would have thought that it would be a simple COUNTIF function. e.g.

= countif(E1,E:E)

You could then copy that formula down and use the normal sorting feature.

If there's something that I'm missing here then you may want to try and post a brief sample of your data and how it should ultimately look, as it's hard to visualise based on your description.

NonprayingMantis

6434 posts

Uber Geek

#352221 16-Jul-2010 10:13

I don;t quite understand your problem, but would the COUNTIF formula work?

=COUNTIF(range,criteria)

e.g. in cell L1 you might have

=COUNTIF(E:E,E1) and drag that down to fill colum L

with this, each cell in colum L will look at it's corresponding cell in colum E and count how many times the value in that E cell appears in the whole of column E.

this might create problems because if you want to find "ABCDE" and column E has a cell contain "ABCDE/FGRDF" it won't recognise that as one, so you might need to include a couple of hidden colums where you split the cells out (use data-> text to colums delimited with '/' as the spacer) and then apply the countif formula to both of those columns instead of column E.

if you want it ranked then you could apply autofilter at the top, then sort the counting column alphabetically.

rscole86

4980 posts

Uber Geek

Moderator

Trusted

Lifetime subscriber

#353204 19-Jul-2010 07:23

Thanks Guys, Countif does solve part of the problem. I should have probably mentioned my excel knowledge is basic.

But as NonprayingMantis pointed out I cannot include the data from any cells which contain two variables. How would I seperate them, and then use COUNTIF on the two columns and still be able to ranking them based on the results of the COUNTIF?

If I have to exclude any of the cells with 2 variables, thats fine, but if its possible then that would be ideal.

cokemaster

Exited
4930 posts

Uber Geek

Retired Mod

Trusted

Lifetime subscriber

#353207 19-Jul-2010 07:34

pivot-tables might help :)

(yes they are godlike)

webhosting

Loose lips may sink ships - Be smart - Don't post internal/commercially sensitive or confidential information!

NonprayingMantis

6434 posts

Uber Geek

#353402 19-Jul-2010 14:02

rscole86: Thanks Guys, Countif does solve part of the problem. I should have probably mentioned my excel knowledge is basic.

But as NonprayingMantis pointed out I cannot include the data from any cells which contain two variables. How would I seperate them, and then use COUNTIF on the two columns and still be able to ranking them based on the results of the COUNTIF?

If I have to exclude any of the cells with 2 variables, thats fine, but if its possible then that would be ideal.

do yo know how to use text-to-columns?

I'm not sure where it is on Excel 2007, but if you are using 2003 it is in the data menu.

What you need to do is insert 2 new columns next to E (select column F and G-> right click-> insert should do it)
copy the data from column E into column F, leaving G blank
then select column E where the values are. data menu - -> text to columns -> have the 'delimited' box selected and hit next. it will then ask you what delimiters you want to use (delimiter is the thing that will split the value out. in your case you want to use 'space' and '/' (which you will have to type in the 'other' box sinc eit isn;t a default option) below that it should show you a preview of what your data looks like

hopefully it should show no diference for the cells containing only one value, and the ones containg acvbc/dferdd   should be showing as acvbv   and dferdd   either side of a solid line.

once that is done click finish and you should now find that column F contains a whole bunch of values, and column G contains values whereever you previoulsy had one that had a / in it.

you will now need two countif forumlas, one with criteria of the value in column F and one for the value in column G. make sure you select both column F and G when selecting the range though.

so, if cell E10 contained

acbde/fghij

you should now have acbde in  F10 and   fghij in G10

then the first Countif formula will be =COUNTIF(F:G,F10)   and will tell you how many times abcde appears in both columns F and G,   the second COUNTIF formula will be =COUNTIF(F:G,G10) and will tell you how many times fghij appears on both columns F and G.

(good tip: If you want to hide columns F and G highlight them both and use the shortcut Alt+D, G, G   then you can easily hide and unhide them using the litle plus sign whenever you like.)

rscole86

4980 posts

Uber Geek

Moderator

Trusted

Lifetime subscriber

#353408 19-Jul-2010 14:10

Thanks for that, Ill try it out tomorrow, as im just about finished for the day.

Cokemaster, I had seen that pivot tables might solve this, but was hoping for an easier answer ;)