+ Reply to Thread
Results 1 to 2 of 2

Automatically filling % of rows in Pivot Table

  1. #1
    Registered User
    Join Date
    12-16-2020
    Location
    Nowhere
    MS-Off Ver
    Ms Office 2016
    Posts
    3

    Automatically filling % of rows in Pivot Table

    Hi,
    I am looking for a way to solve the following problem:
    I have a pivot table with a row count of around 1000 (filtered). I need a function that would let me pick a random% of them (let's assume 50%). Then, from randomly selected 500 lines, I need to randomly assign them to 5 groups - (Blue, Yellow, Red, Green, White). However, each group must have the same number of members. After searching the internet, I found a lot of information on how to choose a random number, but the key in my case is to work on one document and not change anything (except possibly adding some columns and this forumula). I'm not willing to select 500 rows and then paste them somewhere to continue work, it cannot be done that way.
    Thanks in advance for any help in solving the problem!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Automatically filling % of rows in Pivot Table

    Does the attached offer a way forward

    Column A is original data. Your production workbook will no doubt have several columns. Add an extra column to your data called here Percentile and add the formula.

    When you enter a % from 1-100 in B2 then the Output Range starting in F6:G6 will automatically filter.

    Occasionally you'll end up with extra values in column F where there may be two or more random values the same, this happen more as you choose a bigger percentage, but column G will always contain the relevant number required.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Filling cell rows automatically
    By Dioxiced in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-23-2015, 01:18 PM
  2. Automatically insert rows and columns as pivot table changes
    By Jayrome in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2012, 03:41 AM
  3. Automatically filling a table
    By Gaz_m2k5 in forum Excel General
    Replies: 0
    Last Post: 09-02-2011, 04:08 AM
  4. Filling a table automatically
    By Gaz_m2k5 in forum Excel General
    Replies: 4
    Last Post: 08-31-2011, 07:09 AM
  5. Replies: 1
    Last Post: 11-25-2009, 03:44 AM
  6. [SOLVED] How do i automatically group rows in a pivot table?
    By SC in forum Excel General
    Replies: 1
    Last Post: 04-01-2006, 04:31 AM
  7. Need pivot table to automatically highlight subtotal rows
    By jim37055 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2005, 05:10 AM

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