How I can generate 5000 records in 2 columns of random numbers between 1 and 100 that being unique.
For example:
A B
----------------
1 98
1 23
37 98
6 56
93 18
. .
. .
. .
thanks
Excel formulas do not perform loops until a condition has been met. Any 'loop' or array processing must have a defined number of cycles. Further,
RAND
andRANDBETWEEN
are volatile formulas that will recalculate anytime the workbook goes through a calculation cycle.In VBA this would look like the following.
First run this tiny macro:
Then sort cols A,B,C by column C.
Then pick the first 5000 rows.
EDIT#1:
To remove cases in which the value in column A is the same as the value in column B use this macro instead:
Here is a simple-minded approach using formulae. Whether it would be appropriate would depend on context.
First in the Formulas tab set calculation options to 'Manual'.
Put the following formula in a2:-
B is going to be a helper column. Put the following in B2:-
Column C is the second result that you want. Put the following in C2:-
Pull the formulae down as required.
Each time you press 'Calculate Now', you will get a fresh set of random numbers.
However if you really need unique rows (every row to be different) you'd need a different approach - could generate a set of 4-digit numbers, split them into first and last pairs of digits and filter out ones where first and second were equal.
Generate the 4-digit number in A2:-
Take the first two-digit number plus one in B2:-
Take the second 2-digit number plus one in C2:-
Check for invalid numbers in D2:-
Set up a running total of valid numbers in E2:-
Here's how the second approach would look with checking for duplicate rows as well as duplicate numbers within a row. Note that you'd have to generate about 3,000 rows to get 2,500 distinct rows:-