• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

creating a list from given values

4crawfs

New Member
What is the easiest way to create a list from data?

The data needs to create group list. The list should return forename and surname. Each group has a numerical value from 1 to 8.

I would like the data to appear on a seperate worksheet based on the numerical value.


Thanks

4Crawfs
 
I'm afraid the explanation is rather vague...could you show us an example of what you have and/or want to have?
 
Ok sorry,


Forename Surname Group

Jay Abdul 1

John Adams 2

Laurence Allen 3


About 180 people divided into 8 groups. 1 master data sheet and a separate worksheet for each group.

Later in the year I will need to do the same again so wanted to automate the process.

I would like to return all the group 1 students to a group 1 worksheet etc etc.


Thanks
 
Hi, 4crawfs!

Give a look at this: http://www.2shared.com/file/JPeoKN74/creating_a_list_from_given_val.html

It handles discrete group numbers (not sequential from 1). Hope it works for you.

Regards!
 
SirJB7

Thanks for your help.


Is it possible to expand the macro to 8 groups and upto 200 entries?


Thanks

4Crawfs
 
Hi, 4crawfs!

It works with any number of groups and any number of entries, the only constraint is your Excel version where the worksheet rows number may change from 65536 to 1048576.

And is set to handle non consecutive group numbers too.

Regards!
 
SirJB7


Thanks again.

Is it possible to make each group appear on a different worksheet?

Group 1 on worksheet 1

Group 2 on worksheet 2

etc


Thanks

4Crawfs
 
Hi, 4crawfs!

Here it is:

http://www.2shared.com/file/n8sDYlF8/creating_a_list_from_given_val.html

Macro CreateGroups1, one sheet (Groups).

Macro CreateGroups2, one sheet per group (Group1, ...).

Regards!
 
Hi SirJB7,


Thanks that works as i hoped it would.

Is it possible to change it slightly so that it could put the data into 8 existing group worksheets rather than create 8 new ones?


Thanks

4Crawfs
 
Hi, SirJB7


Thanks for all your help.

I was wondering if the macros could be changed to allow the data to go to existing worksheets instead of new ones.

For example could group 1 go into a worksheet that exists called group 1?


Thanks for all oyur help.

4Crawfs
 
Hi, 4crafws!


You say "... if the macros ...":

a) for first macro CreateGroups1 that's not possible because it outputs on only one sheet called "Groups", you can change the name to whatsoever you want, but all the groups are going there

b) for second macro CreateGroups2 it's not only possible but it's already doing that: if a sheet name "GroupX" exists, with X=1 to last group, it'll output on that sheet, if not it'll create it before and then output there


If what you want is to change the name structure from "Group1" to "Group 1" as you wrote, just simply modify this code line in Sub CreateGroup2 (4th. line):

from: Const ksGroup = "Group"

to: Const ksGroup = "Group "


If you meant anything else, please explain it in detail. Thanks.

Regards!
 
Back
Top