+ Reply to Thread
Results 1 to 13 of 13

Randomise data from a column into another

  1. 02-02-2023, 07:31 PM #1
    k3v1n_23
    k3v1n_23 is offline
    Registered User
    Join Date
    02-02-2023
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    5

    Randomise data from a column into another

    Hi!

    I've got approx 3000 rows of data in Column A. Half of the data in this column ends with "(B&W)". I'd like to use a formula which will copy the data from this column into any other column, but it should meet the following criteria:

    - Randomise the data copied from column A
    - Every other cell should be one which ends in "(B&W)"
    - The same value should not be copied more than once i.e. there should be no duplicate values in the new column

    I've attached a sample workbook hopefully showing what I mean.

    Thank you in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-02-2023, 09:18 PM #2
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Randomise data from a column into another

    Hi,
    I don't understand "- Every other cell should be one which ends in (B&W)"
    But with I have a solution to randomize the A column
    Attached Files Attached Files
    Diana Tanase

    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
    Reply With QuoteRegister To Reply
  3. 02-02-2023, 10:44 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Randomise data from a column into another

    If you only need to do this once:

    =LET(A,A2:A21,B,ROWS(A),C,MOD(SEQUENCE(B)/PI(),1),D,HSTACK(WRAPROWS(A,2),WRAPROWS(C,2)),TOCOL(HSTACK(TAKE(SORT(CHOOSECOLS(D,1,3),2,1),,1),TAKE(SORT(CHOOSECOLS(D,2,4),2,1),,1))))

    if you need to do it multiple times:

    =LET(A,A2:A21,B,ROWS(A),C,MOD((F1+SEQUENCE(B))/PI(),1),D,HSTACK(WRAPROWS(A,2),WRAPROWS(C,2)),TOCOL(HSTACK(TAKE(SORT(CHOOSECOLS(D,1,3),2,1),,1),TAKE(SORT(CHOOSECOLS(D,2,4),2,1),,1))))

    and change the value in F1 to any number. If the number is 4568.... every time you choose 4568 the same pattern will be returned. However, if you choose your seed number from a wide range...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 02-04-2023, 08:24 AM #4
    k3v1n_23
    k3v1n_23 is offline
    Registered User
    Join Date
    02-02-2023
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Randomise data from a column into another

    Glenn, you legend! This worked a treat, thank you!
    Reply With QuoteRegister To Reply
  5. 02-04-2023, 08:38 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Randomise data from a column into another

    You're welcome and thanks for the feedback.
    Reply With QuoteRegister To Reply
  6. 02-04-2023, 12:45 PM #6
    Rick Rothstein
    Rick Rothstein is online now
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: Randomise data from a column into another

    I might be missing something, but where in Glenn's code does it meet your first requirement (randomise the data copied from column A)?
    Reply With QuoteRegister To Reply
  7. 02-04-2023, 09:23 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Randomise data from a column into another

    MOD(SEQUENCE(B)/PI(),1)

    produces a non-volatile pseudo-random sequence. I used it to sort the data (after splitting it into two columns... B&W in one non-B&W in the other.
    Reply With QuoteRegister To Reply
  8. 02-04-2023, 11:53 PM #8
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,057

    Re: Randomise data from a column into another

    Alternative solution with helper column:
    With random numbers in C2=RANDARRAY(COUNTA(A2:A21),1)
    D2=INDEX($A$2:$A$21,IFERROR(MATCH(C2#,SORT(FILTER(C2#,ISNUMBER(FIND("B&W",$A$2:$A$21))=TRUE)),0)+0.5,MATCH(C2#,SORT(FILTER(C2#,ISNUMBER(FIND("B&W",$A$2:$A$21))=FALSE)),0))*2-1)
    Reply With QuoteRegister To Reply
  9. 02-06-2023, 03:40 PM #9
    Rick Rothstein
    Rick Rothstein is online now
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: Randomise data from a column into another

    Here is another (rather short) alternative solution that uses Excel's built-in randomizer for you to consider. Put this formula in cell C2...
    =LET(w,WRAPROWS(A2:A21,2),f,LAMBDA(x,SORTBY(INDEX(w,,x),RANDARRAY(ROWS(w)))),TOCOL(HSTACK(f(1),f(2))))
    Last edited by Rick Rothstein; 02-06-2023 at 11:05 PM.
    Reply With QuoteRegister To Reply
  10. 02-06-2023, 09:07 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Randomise data from a column into another

    Sure, RANARRAY can ive a shorter formula... but it's volatile and recalculates endlessly! Thwe approach I used remains as is until you decide to change it!
    Reply With QuoteRegister To Reply
  11. 02-06-2023, 09:30 PM #11
    Rick Rothstein
    Rick Rothstein is online now
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: Randomise data from a column into another

    Quote Originally Posted by Glenn Kennedy View Post
    Sure, RANARRAY can ive a shorter formula... but it's volatile and recalculates endlessly! Thwe approach I used remains as is until you decide to change it!
    True, that's a good point, although I guess the OP could copy and then paste values to freeze a list at any point in time. Anyway, using your "randomizer" snippet (with the F1 modifier) in my formula, produces this still relatively short formula...
    =LET(w,WRAPROWS(A2:A21,2),f,LAMBDA(x,SORTBY(INDEX(w,,x),MOD((F1+SEQUENCE(ROWS(w)))/PI(),1))),TOCOL(HSTACK(f(1),f(2))))
    Last edited by Rick Rothstein; 02-06-2023 at 11:13 PM.
    Reply With QuoteRegister To Reply
  12. 02-07-2023, 05:01 AM #12
    kvsrinivasamurthy
    kvsrinivasamurthy is online now
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Randomise data from a column into another

    VBA solution is here. Every time List will be randomized Differently. output in column B.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 02-07-2023 at 09:23 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  13. 02-07-2023, 12:24 PM #13
    Rick Rothstein
    Rick Rothstein is online now
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: Randomise data from a column into another

    *** ignore ***
    Last edited by Rick Rothstein; 02-07-2023 at 12:27 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Randomise who can go on what tile based on what colour they can be
    By scorpain in forum Excel Formulas & Functions
    Replies: 36
    Last Post: 09-15-2022, 09:19 AM
  2. Is there a way to Randomise RANDBETWEEN?
    By jyadayada in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2022, 08:44 AM
  3. Randomise Evenly
    By robonuk in forum Excel General
    Replies: 8
    Last Post: 04-16-2021, 05:09 AM
  4. [SOLVED] Numbers won't randomise for me
    By mhoey8 in forum Excel General
    Replies: 1
    Last Post: 08-09-2019, 12:41 PM
  5. [SOLVED] How to randomise staff for roster
    By kophanz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-12-2016, 04:10 AM
  6. Need to shuffle/randomise order of display
    By simoninparis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-23-2012, 03:46 PM
  7. randomise numbers in cells
    By msylai in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-29-2008, 06:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1