+ Reply to Thread
Results 1 to 6 of 6

Using Vlookup and Randomly selected values

  1. #1
    Registered User
    Join Date
    02-27-2020
    Location
    NOVA
    MS-Off Ver
    Excel for Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 32-bit
    Posts
    32

    Using Vlookup and Randomly selected values

    I'm working on an audit that will randomly select pieces of equipment and have engineers verify the electronic reading vs the local reading.
    I've put together a sample table in the tab "Source List" that will randomly choose which pieces of equipment have to be examined, as well as the points to verify. Like cooling units have to have Supply and Return air temperatures, as well as underfloor pressure. While power panels are phase voltage/current, and power.

    My struggle is in the "Report Page." Experience has shown that I have to let the randomization go on in the background, with the engineers just getting a list of what to fill out or verify. I'd like the Report Page to find every piece of equipment which fits the randomizer criteria (currently it's just a 10% chance), but not have empty gaps in it. At that point, they can print out the sheet which just has a list of what to examine, and start their inspections. There's a potential list of like 5000 pieces of equipment, so it's not feasible to have this print out with 4900 blank lines on it.

    I should honestly probably be doing this with a macro, but they're currently beyond my skill level.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Using Vlookup and Randomly selected values

    Do you have access to a version of Excel that supports the FILTER() function (or could you use Google Sheets or other spreadsheet that supports the FILTER() function)? Basically, your spreadsheet looks like you are trying to create a filtered list on "Report Page" based on "Source List" where column B contain "Check". My old version doesn't support the FILTER() function, so I cannot help with that.

    Assuming you cannot use the FILTER() function, I set up a lookup strategy where I created a unique lookup value from the random "CHECK" function, then used simple lookup functions to return get those records. Steps I took:

    1) I need a unique lookup value in Source List column B. I opt for Check0,Check1,Check2, etc. I edit the formula in B3 to be =IF(RANDBETWEEN(1,100)<10,"CHECK0",""). Then I edit the formula in B4 to be =IF(RANDBETWEEN(1,100)<10,"CHECK"&COUNTIFS(B$3:B3,"CHECK*"),""). Copy B4 down to the bottom of the list.
    2) On Report Page, I enter check0 into Z3, check1 into Z4, check2 into Z5, then select Z3:Z5, grab the little + on the lower right, and drag down to fill Z4:Z42 with check0,check1,etc.
    3) Now a simple lookup function can return the equipment id based on the checkn value. =IFERROR(INDEX('Source List'!$A$3:$A$6000,MATCH(Z3,'Source List'!$B$3:$B$6000,0)) into Report Page A3 and copy/paste/fill down.

    If you can get access to a spreadsheet that supports the FILTER() function, that seems like the easiest approach. In the absence of that, this should work if you can tolerate the changes to the "check" formula and the helper column.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-27-2020
    Location
    NOVA
    MS-Off Ver
    Excel for Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 32-bit
    Posts
    32

    Re: Using Vlookup and Randomly selected values

    I did not know about the FILTER() function, just the Filter button on the DATA ribbon. But it looks like I do have access to it.

    I'm going to try and implement your suggestion, and read up on the function as well. But it's a matter of 1) using the task with prepared and personalized instructions, or 2) using the best tool for the task.

    Thank you,
    I'll update once I've taken a look and tried it.

  4. #4
    Registered User
    Join Date
    02-27-2020
    Location
    NOVA
    MS-Off Ver
    Excel for Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 32-bit
    Posts
    32

    Re: Using Vlookup and Randomly selected values

    Alright, I got it, mostly.

    In the 'Report Page" I put =FILTER('Source List'!$A3:$A42, 'Source List'!$B3:$B42="CHECK", "k") in cell A3. So it returns any equipment that has "CHECK" show up in the random generation of 'Source List'.

    But sometimes it returns a #SPILL! error, and I really have no idea why.
    I did find that if the array has 5 entries pop up, and I don't have 5 empty rows, then it gives #SPILL!, but it'll do it sometimes without that restriction too.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Using Vlookup and Randomly selected values

    This (https://support.microsoft.com/en-us/...rs=en-us&ad=us ) is pretty much everything I know about these formulas and Spill errors. A Spill error suggests to me that Excel is seeing something in the rows beneath the formula that it doesn't want to overwrite with the output of the FILTER() function, but I cannot say any more. Perhaps someone else more familiar with these newer functions and their spill errors can comment?

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Using Vlookup and Randomly selected values

    Using this formula in A3:
    Please Login or Register  to view this content.
    Drag down

    B3:
    Please Login or Register  to view this content.
    Drag down and accross to Next point columns
    Attached Files Attached Files
    Quang PT

+ 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] match randomly selected rows of data
    By mikeh1978 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-18-2021, 04:02 PM
  2. [SOLVED] switch values randomly based on selected range
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2021, 05:25 AM
  3. Pressing enter and selected cell changes randomly
    By Big_Kev in forum Excel General
    Replies: 3
    Last Post: 09-12-2017, 10:09 AM
  4. No randomly selected values/VBA?
    By ruben0525 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2016, 07:39 AM
  5. [SOLVED] Highlight (grey) a randomly selected cell
    By drwhophd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2014, 10:17 AM
  6. [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
  7. Randomly selected numbers
    By Keith Robinson in forum Excel General
    Replies: 5
    Last Post: 04-14-2005, 12:06 PM

Tags for this Thread

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