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.



3959 posts

Uber Geek
+1 received by user: 26

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

Uber Geek
+1 received by user: 156

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.

5267 posts

Uber Geek
+1 received by user: 782


  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.



3959 posts

Uber Geek
+1 received by user: 26

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!

5267 posts

Uber Geek
+1 received by user: 782


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



3959 posts

Uber Geek
+1 received by user: 26

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:

22nd Only: PB Tech BROTHER HL1110 Mono laser Printer $15 shipped(after $30 cashback)
Created by loceff13, last reply by old3eyes on 22-Oct-2014 18:40 (18 replies)
Pages... 2


Who Audits IRD?
Created by gundar, last reply by charsleysa on 22-Oct-2014 15:52 (18 replies)
Pages... 2


Spark Socialiser
Created by freitasm, last reply by freitasm on 22-Oct-2014 18:39 (34 replies)
Pages... 2 3


HERE Maps beta available to all Android 4.4 devices and up
Created by freitasm, last reply by sonyxperiageek on 22-Oct-2014 20:54 (16 replies)
Pages... 2


American legal jurisdiction in New Zealand
Created by ajobbins, last reply by gzt on 21-Oct-2014 14:58 (30 replies)
Pages... 2


Another Trade Me competitor: SellShed
Created by freitasm, last reply by SellShed on 22-Oct-2014 11:54 (42 replies)
Pages... 2 3


Snap! 200/200 Up And Running
Created by PoHq, last reply by andrewcnz on 22-Oct-2014 20:47 (12 replies)

Abnormal upstream data usage - Vodafone Cable Wellington
Created by otherside, last reply by otherside on 22-Oct-2014 17:11 (12 replies)


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.