[Solved] Randomize Email Addresses

Discuss the spreadsheet application

[Solved] Randomize Email Addresses

Postby emilstyle » Fri Dec 13, 2019 5:31 pm

Hi there. I do have 220.000 email addresses, one for each line of my spreadsheet, on Column A
I want to randomize them in column B.
I tried the Rand() but IT DOESN'T WORK as I suppose is only for numbers,not for words/letters content.

How can i create a list of the emails that I have in column A with random order in Column B ?
Thank you

For example the results are gonna be
Column A
ciao@ciao1.com
ciao@ciao2.com
ciao@ciao3.com
ciao@ciao4.com
palla@ciao.com

Column B random results:
Ciao@ciao3.com
palla@ciao.com
ciao@ciao2.com
ciao@ciao1.com
ciao@ciao4.com

Again, I've typed =RAND() in the column B but it doesnt work, the cell says #NAME? and I don't know why. I've tried also =RAND(A1:A220.000) but again same problem
Thank you
Last edited by emilstyle on Fri Dec 13, 2019 9:03 pm, edited 1 time in total.
OpenOffice 4 on Windows 10
emilstyle
 
Posts: 3
Joined: Fri Dec 13, 2019 5:25 pm

Re: Randomize Email Addresses

Postby MrProgrammer » Fri Dec 13, 2019 5:44 pm

Hi, and welcome to the forum.

emilstyle wrote:I've typed =RAND() in the column B but it doesnt work, the cell says #NAME?
RAND is the English name of the function. If you are using a different language, you must use the localized name in your spreadsheet, for example, ALEA in French. See if the Insert → Function dialog helps you.

emilstyle wrote: I do have 220.000 email addresses, one for each line of my spreadsheet, on Column A. I want to randomize them in column B.
Copy/paste to column B, then study [Tutorial] Randomization in Calc, part E Arranging a set of values in random order. Cell C2 in sheet RandSort will show you the localized name of the RAND function.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3952
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Randomize Email Addresses

Postby emilstyle » Fri Dec 13, 2019 6:04 pm

Oh I had no idea was for the language. I still have a problem.

I found the formula which is CASUALE but the problem still exist as RAND (casuale) is only for numbers, not for letters.
Now it says Err.508 on every cell.

I've checked out the tutorial on letter E but It talks about 3 columns. I have only 2, and I honestly don't understand what to do.

I also found some website doing that but one has a limit of 10.000 and the other one also included numbers in the results like 1: xxx 2: xxx so when I copied and pasted I had also numbers in every cell.

I don't know what to do. It should be something so easy to do and I'm losing like 2 days just to sort randomly some emails :(
OpenOffice 4 on Windows 10
emilstyle
 
Posts: 3
Joined: Fri Dec 13, 2019 5:25 pm

Re: Randomize Email Addresses

Postby Zizi64 » Fri Dec 13, 2019 6:39 pm

You can try the 'Mixer' extension:
http://extensions.libreoffice.org/exten ... r-for-calc
http://extensions.openoffice.org/en/pro ... r-calc-013

and finally:
It possible to write a StarBasic macro (similar to extension "mixer") for this task...
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8771
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Randomize Email Addresses

Postby John_Ha » Fri Dec 13, 2019 8:07 pm

1. Create a helper column
2. Insert 22,000 random numbers from 0 to 1 in the helper column
3. Sort both columns using the helper column as the "sort on me" column.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
LO 6.3.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7268
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Randomize Email Addresses

Postby emilstyle » Fri Dec 13, 2019 9:02 pm

Thank you John_Ha.
I've finally done it thanks to your comment.

What i Did was exactly what you said:
1) In column B i added the function =CASUALE() and then double clicked it. (in english is =RAND() )
2) I selected both columns and in SORT I choosed order per Column B (the one with random values from 0 to 1)
3) The output I selected C1:C1048567
4) I had in Columns C all the emails shuffled !

Thank you so much !
OpenOffice 4 on Windows 10
emilstyle
 
Posts: 3
Joined: Fri Dec 13, 2019 5:25 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests