Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## rscole86

4094 posts

Uber Geek

Moderator
Trusted
Subscriber

 Topic # 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 / 12ggbyIdeally 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

3545 posts

Uber Geek

Trusted
Subscriber

 Reply # 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

6426 posts

Uber Geek

 Reply # 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

4094 posts

Uber Geek

Moderator
Trusted
Subscriber

 Reply # 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

Nate wants an iphone
3864 posts

Uber Geek

Mod Emeritus
Trusted
Subscriber

 Reply # 353207 19-Jul-2010 07:34 pivot-tables might help :)(yes they are godlike) webhosting |New Zealand connections | geekzone IRC chatLoose lips may sink ships - Be smart - Don't post internal/commercially sensitive or confidential information!

## NonprayingMantis

6426 posts

Uber Geek

 Reply # 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 blankthen 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 likehopefully  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 G10then 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

4094 posts

Uber Geek

Moderator
Trusted
Subscriber

 Reply # 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 ;)

News »

National AI group launching next month
Posted 25-May-2017 09:54

New Zealand Digital Future, according to tech companies
Posted 25-May-2017 09:51

New Microsoft Surface Pro delivers outstanding battery life, performance
Posted 25-May-2017 09:34

Garmin VIRB 360 brings immersive 360-degree 5.7K camera experience
Posted 25-May-2017 09:30

Telecommunications monitoring report: Are you being served?
Posted 24-May-2017 11:54

NetValue partners with CRM Provider SugarCRM
Posted 23-May-2017 20:04

Posted 19-May-2017 14:51

2degrees tips into profit after seven lean years
Posted 19-May-2017 09:47

2degrees growth story continues
Posted 17-May-2017 15:25

Symantec Blocks 22 Million Attempted WannaCry Ransomware Attacks Globally
Posted 17-May-2017 12:41

HPE Unveils Computer Built for the Era of Big Data
Posted 17-May-2017 12:39

Samsung Galaxy S8 Plus review: Beautiful, feature-packed
Posted 16-May-2017 20:14

After ten years of mail pain Spark is done with Yahoo
Posted 15-May-2017 13:12

Warnings from security firms: do not click that link or risk your computer being infected
Posted 15-May-2017 10:11

Pushpay named NZ Hi-Tech Company of the Year 2017
Posted 15-May-2017 09:59

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.