Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.
Buying anything on Amazon? Please use the Geekzone Amazon aff link.




3969 posts

Uber Geek
+1 received by user: 27

Moderator
Trusted
Subscriber

Topic # 64472 16-Jul-2010 08:11 Send private message

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!

Create new topic
2821 posts

Uber Geek
+1 received by user: 168

Trusted
Subscriber

  Reply # 352219 16-Jul-2010 10:09 Send private message

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.

5307 posts

Uber Geek
+1 received by user: 804


  Reply # 352221 16-Jul-2010 10:13 Send private message

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.



3969 posts

Uber Geek
+1 received by user: 27

Moderator
Trusted
Subscriber

  Reply # 353204 19-Jul-2010 07:23 Send private message

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.

Nate wants an iphone
3851 posts

Uber Geek
+1 received by user: 6

Mod Emeritus
Trusted
Subscriber

  Reply # 353207 19-Jul-2010 07:34 Send private message

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!

5307 posts

Uber Geek
+1 received by user: 804


  Reply # 353402 19-Jul-2010 14:02 Send private message

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.)



3969 posts

Uber Geek
+1 received by user: 27

Moderator
Trusted
Subscriber

  Reply # 353408 19-Jul-2010 14:10 Send private message

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

Create new topic




Twitter »
Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





Trending now »

Hot discussions in our forums right now:

My un-consented UFB install
Created by thurthur, last reply by mdooher on 28-Nov-2014 18:57 (79 replies)
Pages... 4 5 6


This is the end ...
Created by joker97, last reply by davidcole on 28-Nov-2014 21:30 (53 replies)
Pages... 2 3 4


Gigatown winner town and plans
Created by freitasm, last reply by Demeter on 28-Nov-2014 08:59 (76 replies)
Pages... 4 5 6


Seen any good Black Friday / Cyber Monday deals?
Created by Jaxson, last reply by ckc on 28-Nov-2014 15:08 (24 replies)
Pages... 2


Gull Employment Dispute.
Created by networkn, last reply by richms on 28-Nov-2014 17:57 (153 replies)
Pages... 9 10 11


Stuff.co.nz hacked by Syrian electronic army?
Created by surfisup1000, last reply by dclegg on 28-Nov-2014 13:35 (17 replies)
Pages... 2


What the hell MyRepublic!?
Created by gished, last reply by pohutukawa on 28-Nov-2014 17:45 (16 replies)
Pages... 2


The Warehouse pulling R18 games and DVD's
Created by semigeek, last reply by Geektastic on 27-Nov-2014 18:32 (64 replies)
Pages... 3 4 5



Geekzone Live »

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

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.