Excel genius needed!
Discussion
Ok, I have a massive list of peoples names of which I want to group them into 24 groups of people. I have a list that looks like this;
Fred 1
Alan 1
Tom 1
Frank 2
Tony 2
Harry 2
Sam 3
Paul 3
John 3
etc, etc...
I want to be able to put one 1, one 2, one 3 etc into each group.
Big question is how do I do that...
Answers on a post card please...
Fred 1
Alan 1
Tom 1
Frank 2
Tony 2
Harry 2
Sam 3
Paul 3
John 3
etc, etc...
I want to be able to put one 1, one 2, one 3 etc into each group.
Big question is how do I do that...
Answers on a post card please...
Are the names in 1 column?
If so you need to get the names and numbers into different columns. To do this click on the column then click Data > Text to columns.
Select Delimited > Next > put a tick in the Space option then click OK.
This should put the Names and numbers in different columns.
When this is done to a Data > Sort by Name column ascending and Number column ascending.
This should give you what you want.
(Usual disclaimers apply!)
If so you need to get the names and numbers into different columns. To do this click on the column then click Data > Text to columns.
Select Delimited > Next > put a tick in the Space option then click OK.
This should put the Names and numbers in different columns.
When this is done to a Data > Sort by Name column ascending and Number column ascending.
This should give you what you want.
(Usual disclaimers apply!)
TheGroover said:
Are the names in 1 column?
If so you need to get the names and numbers into different columns. To do this click on the column then click Data > Text to columns.
Select Delimited > Next > put a tick in the Space option then click OK.
This should put the Names and numbers in different columns.
When this is done to a Data > Sort by Name column ascending and Number column ascending.
This should give you what you want.
(Usual disclaimers apply!)
and then transpose the rows into columns each with 24 rows.
email me through my profile if you wish and I'll have a go.
As a quick(ish) solution:
1. Sort by the existing number
2. Add a new column with 1 in the first row, and the following formula in the second row:
=IF(B2=B1,C1+1,1)
(assuming your existing number is in column B, and the new column is column C)
3. Copy the formula in 2 to the rest of column C.
4. Sort by column C, followed by column B.
1. Sort by the existing number
2. Add a new column with 1 in the first row, and the following formula in the second row:
=IF(B2=B1,C1+1,1)
(assuming your existing number is in column B, and the new column is column C)
3. Copy the formula in 2 to the rest of column C.
4. Sort by column C, followed by column B.
The general idea above has the following steps:
1)Seperate the number and name into two columns. ie you have colA with all the names and colB with all the numbers - Excel can then use the numbers to sort etc
2)The next stage is to seperate the two col list into three smaller lists only containing the same numbers ie all 1s all 2s etc.
There are two ways of doing it easily:
a) IF YOU ONLY WANT TO DO THE EXERCISE ONCE...select all the names and numbers and use DATA and SORT, sorting by the column your numbers are in.
This will give the same list but running allthe 1s then all the 2s etc.
Finaly manulay cut and paste each third of the two columns ie all the 2s etc into two new seperate columns.
You should end up with 6 columns containing all the 1s in the first two all the 2s in the secound two etc.
This will give you your groups in the rows running horizontaly. If it needs to be neater and vertical use copy and paste special and transpose function to spin them through 90deg.
b)Sorry run out of time but you can use a VLOOKUP formula to pick the imformation out the origional list and place it into three seperate columns as before...
If you still have problems send me an e-mail. with your number on it or a copy of the sheet.
1)Seperate the number and name into two columns. ie you have colA with all the names and colB with all the numbers - Excel can then use the numbers to sort etc
2)The next stage is to seperate the two col list into three smaller lists only containing the same numbers ie all 1s all 2s etc.
There are two ways of doing it easily:
a) IF YOU ONLY WANT TO DO THE EXERCISE ONCE...select all the names and numbers and use DATA and SORT, sorting by the column your numbers are in.
This will give the same list but running allthe 1s then all the 2s etc.
Finaly manulay cut and paste each third of the two columns ie all the 2s etc into two new seperate columns.
You should end up with 6 columns containing all the 1s in the first two all the 2s in the secound two etc.
This will give you your groups in the rows running horizontaly. If it needs to be neater and vertical use copy and paste special and transpose function to spin them through 90deg.
b)Sorry run out of time but you can use a VLOOKUP formula to pick the imformation out the origional list and place it into three seperate columns as before...
If you still have problems send me an e-mail. with your number on it or a copy of the sheet.
OK, here goes...
What I was getting at was adding a column with a formula that counts upwards through the 1s, then through the 2s, etc.
You should end up with something like this:
Fred 1 1
Alan 1 2
Tom 1 3
Frank 2 1
Tony 2 2
Harry 2 3
Sam 3 1
Paul 3 2
John 3 3
etc....
You can then sort this to get the first occurrence of each number to the top.
You might want to change the formulas to values before you do the sort, as sorting will change them all (but your rows will be in the right order).
Does that make sense?
What I was getting at was adding a column with a formula that counts upwards through the 1s, then through the 2s, etc.
You should end up with something like this:
Fred 1 1
Alan 1 2
Tom 1 3
Frank 2 1
Tony 2 2
Harry 2 3
Sam 3 1
Paul 3 2
John 3 3
etc....
You can then sort this to get the first occurrence of each number to the top.
You might want to change the formulas to values before you do the sort, as sorting will change them all (but your rows will be in the right order).
Does that make sense?
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff