Author |
Topic: Excel help please. (Read 819 times) |
|
aicoped
Junior Member
Gender: 
Posts: 57
|
 |
Excel help please.
« on: Dec 30th, 2008, 9:15pm » |
 Quote  Modify
|
OK, first you guys have been very helpful. I did not want to start a new thread, but I have an excel question related to a different problem.
Let me explain what I want to do first. I want to generate 5000 random numbers between 1-20. I know how to do that no problem. I want to know an easy way to tell for example where the first 1 shows up and the second 1 and third 1etc up to the 10th time it appears in the list, in a table format, and I want this to do that for all 20 numbers.
So for example if the first 1 showed up on the thirteenth line, I would need some way to get that into the table under column 1 and row 1st appearance, somehow.
Any help you guys have in this would be greatly appreciated.
|
|
 IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Excel help please.
« Reply #1 on: Dec 31st, 2008, 12:42am » |
Quote Modify
|
You could give something like http://www.ozgrid.com/Excel/find-nth.htm a try, maybe. I'm not sure, I'm not good with spreadsheets.
Or maybe add a column with the positions 1-5000, then sort both columns together, first on the first then on the second.
|
| « Last Edit: Dec 31st, 2008, 12:45am by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
Grimbal
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 7527
|
|
Re: Excel help please.
« Reply #2 on: Dec 31st, 2008, 8:43am » |
Quote Modify
|
I had to look it up.
There is the function MATCH(value,array,type)
value is the value you are searching for.
array is the array to search.
type must be 0 for an unordered array.
returned is the 1-based index of the first occurrence of the value in the array.
|
|
IP Logged |
|
|
|
aicoped
Junior Member
Gender:
Posts: 57
|
|
Re: Excel help please.
« Reply #3 on: Dec 31st, 2008, 12:19pm » |
Quote Modify
|
OK so lets say my numbers are all in column c1:c5000
And i want to find the first occurence of 20 I would type =MATCH(20,C1:C5000,0)
Now what would i type to find the second 20 in that list?
|
|
IP Logged |
|
|
|
Grimbal
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 7527
|
|
Re: Excel help please.
« Reply #4 on: Jan 1st, 2009, 5:16pm » |
Quote Modify
|
Ah, I didn't see you also want the second and third. Then it seems the link given by towr would help. Another way is to write a small visualbasic program that searches the loop.
|
|
IP Logged |
|
|
|
Ub3r_h4Ck3r
Newbie
I wish to die programming :-)
Gender:
Posts: 4
|
|
Re: Excel help please.
« Reply #5 on: Jan 7th, 2009, 12:18pm » |
Quote Modify
|
This problem motivated me to design this question.
Given a list of 5000 numbers with numbers from say 1 to 20. how to design a data structure that will tell me the positions of each number.
The obvious solution will be to maintain a two-dimensional linked list. Is there any better way to do this. I would like the datastructure to perform these operations in an efficient way.
1. Number of elements of each value.
2. Which element has max occurences.
3. Average distance for each value.
4. Maximum average distance for each value.
Let me know what you think.
|
|
IP Logged |
|
|
|
|
RIDDLES SITE
WRITE MATH!
Home 
Help 
Search 
Members 
Login 
Register
Reply
Notify of replies
Send Topic
Print