+ Reply to Thread
Results 1 to 8 of 8

match randomly selected rows of data

  1. #1
    Registered User
    Join Date
    11-08-2016
    Location
    Dorset, England
    MS-Off Ver
    2013
    Posts
    5

    match randomly selected rows of data

    Hello all,
    Hopefully I can explain what's in my head clearly enough

    Sheet 1 - dataset, including an ID number on each row (from 1 to 10000)
    Sheet 2 - already randomly selected 10% of those ID numbers, so we've got a column of 1000 numbers all between 1 and 10,000

    I'd like to be able to indicate on sheet 1 which ones have been randomly selected, so I can filter accordingly.
    (For example, by adding a column and some kind of 'if this ID number is listed on sheet 2, then add 'TRUE' type equation, but open to best suggestions)

    I imagine this is prob very simple if-you-know-how, but my brain isn't kicking into gear. Any help much appreciated!

    Cheers,
    Mike

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,883

    Re: match randomly selected rows of data

    Assume your data is in column A of both sheets

    Please Login or Register  to view this content.
    How to install your new code
    • Copy the Excel VBA code
    • Select the workbook in which you want to store the Excel VBA code
    • Press Alt+F11 to open the Visual Basic Editor
    • Choose Insert > Module
    • Edit > Paste the macro into the module that appeared
    • Close the VBEditor
    • Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    • Press Alt-F8 to open the macro list
    • Select a macro in the list
    • Click the Run button
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-08-2016
    Location
    Dorset, England
    MS-Off Ver
    2013
    Posts
    5

    Re: match randomly selected rows of data

    Thanks for the quick response. Should have clarified before, not a VBA user (as in, never have used it before).
    Is there a simple IF formula I can use for this?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: match randomly selected rows of data

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Are you still using Excel 2013?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    11-08-2016
    Location
    Dorset, England
    MS-Off Ver
    2013
    Posts
    5

    Re: match randomly selected rows of data

    See sample attached.

    Basically, in the 'data' tab, how can I indicate in column B that the ID number was selected for further review?

    The selected ID numbers are in the 'random' sheet.

    Many thanks
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,562

    Re: match randomly selected rows of data

    Try pasting the following into cell B2 on the Data sheet and then double clicking the fill handle to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    11-08-2016
    Location
    Dorset, England
    MS-Off Ver
    2013
    Posts
    5

    Re: match randomly selected rows of data

    That's perfect, many thanks!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,562

    Re: match randomly selected rows of data

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] randomly selected data to also bring over data from the same line
    By michael35 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2019, 09:52 AM
  2. No randomly selected values/VBA?
    By ruben0525 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2016, 07:39 AM
  3. [SOLVED] getting #value! using a randomly selected string
    By DvDj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2013, 02:10 PM
  4. Duplicate data rows randomly appearing
    By CarolBarnes in forum Excel General
    Replies: 2
    Last Post: 02-04-2013, 12:34 PM
  5. How to randomly sort rows of data?
    By chargeandgo in forum Excel General
    Replies: 2
    Last Post: 12-05-2012, 08:37 AM
  6. Replies: 3
    Last Post: 08-22-2012, 04:02 AM
  7. Sometimes data will jump rows randomly.
    By Jason in forum Excel General
    Replies: 1
    Last Post: 04-11-2006, 03:35 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1