Macro to do randomization and sorting

[ajang]

Good Evening,

I am a student and working on a study, which requires me to generate a sample of randomize participants from an existing excel file, and then sort them in ascending order. I have to repeat this process for all twelve months (12 tabs in the attached file). Could someone please suggest, if there can be a macro that can assist creating a new variable that will allow participants to have unique ID (Random number) and then sort the new variable for all months (12 tabs) together?

Thank you very much,
Ajang

[event21]

Hi -

You can do something like;

Please Login or Register  to view this content.

event

[ajang]

Hello event 21,

I tried using the Macro but it did not work. Could you please see? Thank you

[event21]

Hi -

Can you post your file with the codes?

event

[duncandhu]

Hello event 21,

I tried using the Macro but it did not work. Could you please see? Thank you

Worked for me. What are the steps you are following?

[event21]

Hi -

Please change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

event

[ajang]

I tried again.

The way I do it is. I will start working with January Tab. After last column with data, I create a new variable called Random using formula "=RAND()" or "=RANDBETWEEN($A$2,$A$26). Then I copy and paste them as special to create a new variable called Random2 because the otherwise you cannot sort them in any order (please see attached file). So, I am thinking that this macro will also create a new variable. May be I am thinking wrong. Please advise.

[event21]

Hi -

You need to saveas the file as macro-enabled workbook attached.

open the attached file, enable macros and click Ctrl + m

event

[ajang]

Thank you. But I believe I did not explain clearly what I meant. One more time please

What I want to do is create a new variable called Random1 (using Rand Formula) (as in the January tab) which, will give each client a new specific unique number in each tab below (Feb, March, April and so on...) and also create Random2 for each month. Random2 is nothing but Random1 pasted as special because Random2 allows to sort participants in ascending order which Random1 does not. I am wondering if there is a Macro that will do it for all 12 tabs together rather than me doing it manually in each tab.

Thank you very much.

[duncandhu]

If you use RAND or RANDBETWEEN then the number will not be unique (possibly). The macro posted above generates a unique value, and you can use it to sort with. For each tab.

[ajang]

Thank you duncandhu. I was using RANDBETWEEN in the beginning but it was giving duplicate values. On the forum, I was suggested to use RAND instead because it will not give duplicate values and thus I started using RAND. I am not sure what is going wrong with my file, when I use the posted macro it shuffles the client ID but does not generate unique value for me. I have used Macro before many times therefore, I am not sure why is it not generating unique value for me.

[ajang]

Just to clarify, I mean the macro does not create any new variable for me which, will have unique values for each participant. I do not want to change anything in any of original variables. Thnxx

[duncandhu]

I think I understand now.

The macro generates values in column A.

You want values in column M?

Try this (with credit to event21)

Please Login or Register  to view this content.

[ajang]

I just posted the clarification right before you replied. " The macro is changing the values in the Client ID (the first variable) which, could be unique. But what I have been asking is for Macro to create a new variable which, will have unique values for each participant. I do not want to change anything in any of original variables." I believe that is the confusion. Thnxx

[duncandhu]

Sorry, I think I was editing the last post when you wrote this. Please see my last post!

[ajang]

It works perfectly . Thank you very much.