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

## rscole86

4154 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 / 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

3691 posts

Uber Geek

Trusted
Subscriber

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

6430 posts

Uber Geek

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

4154 posts

Uber Geek

Moderator
Trusted
Subscriber

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
3879 posts

Uber Geek

Mod Emeritus
Trusted
Subscriber

pivot-tables might help :)

(yes they are godlike)

webhosting |New Zealand connectionsgeekzone IRC chat
Loose lips may sink ships - Be smart - Don't post internal/commercially sensitive or confidential information!

## NonprayingMantis

6430 posts

Uber Geek

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

4154 posts

Uber Geek

Moderator
Trusted
Subscriber

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 »

Nokia 8 review: Classy midrange pure Android phone
Posted 16-Oct-2017 07:27

Why carriers might want to embrace Commerce Commission study, MVNOs
Posted 13-Oct-2017 09:42

Fitbit launches Ionic, its health and fitness smartwatch
Posted 12-Oct-2017 15:52

Xero launches machine learning automation to improve coding accuracy for small businesses
Posted 12-Oct-2017 15:45

Bank of New Zealand uses Intel AI to detect financial crime
Posted 12-Oct-2017 15:39

Sony launches Xperia XZ1, a smartphone with real-time 3D capture
Posted 11-Oct-2017 10:26

Notes on Nokia’s phone comeback
Posted 10-Oct-2017 10:06

Air New Zealand begins Inflight Wi-Fi rollout
Posted 9-Oct-2017 20:16

The latest mobile phones in perspective
Posted 9-Oct-2017 18:34

Review: Acronis True Image 2018 — serious backup
Posted 8-Oct-2017 11:22

Lenovo launches ThinkPad Anniversary Edition 25
Posted 7-Oct-2017 23:16

Less fone, more tech as Vodafone gets brand make-over
Posted 6-Oct-2017 08:16

API Talent Achieves AWS MSP Partner Status
Posted 5-Oct-2017 21:20

Stellar Consulting Group now a Domo Partner
Posted 5-Oct-2017 21:03

Xero passes quarter of a million subscriber milestone in UK
Posted 5-Oct-2017 21:02

Geekzone Live »

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