Excel Macro help

Status
Not open for further replies.
Dave

Dave

Staff member
One of the things I have to do for my job now is to check a group of people to make sure they are doing what they are supposed to. I need to take 3 examples from each person and then check them all out. It's taking me just as long to grab the names as it is to check them. As it doesn't matter which examples are picked I would like to have a macro that will at random pick 3 from each person.

Level of difficulty:

If possible, I would like to not have to isolate the user before I sort. So in the spreadsheet with 15000 entries the macro would pick 3 random selections from each user and put the rows selected on another tab in the same workbook.

I've looked at a few nerd sites I frequent but I've seen nothing. If I need to I can paste a portion of the sheet here with information scrubbed so you can see what I'm up against.
 
Shakey

Shakey

Dave said:
If I need to I can paste a portion of the sheet here with information scrubbed so you can see what I'm up against.
Click to expand...
That would help.

Are they all jumbled randomly on one sheet? Or are they sorted by name on one sheet? Or does each person have their own sheet?
 
Dave

Dave

Staff member
  1. They are all jumbled up into one freaking HUGE sheet.
  2. I can add a field.
Here's what I did and it's working although still manually intensive.


  1. Added a field called "Random".
  2. Put in the formula "=Rand()" and copied it in the whole column.
  3. Sorted from lowest to highest based on the random numbers.
  4. Added a filter to the main headings and am going through the counselors 1 at a time and taking the first 3 on their list.
 
MindDetective

MindDetective

OK, I took two seconds to work it out. I can randomly draw three different people from the data set but the way to do it depends upon the left most column in the data set. If it is an ID number, we can work with that. If you can add in a column, even better.

---------- Post added at 02:31 PM ---------- Previous post was at 02:22 PM ----------

Ninja'd! Ok, steps one and two are PERFECT. You can use this without sorting the data entirely, though. Here is what I did, although it needs to be tweaked if you are looking at a filtering the data set to a specific field (which can be done).

In another worksheet (or workbook), you will create a column that pulls out the largest three values from the previous data set:

Code: 
=LARGE(Sheet2!C:C, 1)
The last value determines if you want the 1st largest number, the second, or the third.

This should return three values then, assuming you copy and paste and modify the 1 to a 2 and a 3 to capture those values.

Next, in the adjacent cell:

Code: 
=VLOOKUP(B2,Sheet2!C$2:D$8,2,FALSE)
You identify the matching number (the highest random number from the other data set), the range of data you are searching (this is your whole data set, including the leftmost field containing the random numbers), the column you wish to return (in this example, it is the column immediately next to the highest random number) and whether or not an approximation is okay (false, get the exact number).

If you are filtering, it gets a little more complicated, though. What I would do is actually modify the random number by adding one to it if it meets the filter requirement. I'm just going to assume you might be able to do that.
 
Dave

Dave

Staff member
Not the sorting. But doing it with the first 2 steps and just taking the top 3 when I filtered works slower but fine.
 
MindDetective

MindDetective

Sent. I hope it works for you. I'm feeling very clever for working that little example out. If it isn't what you're after, I'm sure we can figure it out. :)
 
Dave

Dave

Staff member
Hmmm. How big was it? I still don't have anything.

You could also try David.Nihsen(at)Bellevue(dot)edu
 
Dave

Dave

Staff member
JUST NOW got it on my gmail account! That's my main email and I don't know whether to trust it right now or not.
 
Dave

Dave

Staff member
Okay, new issue thanks to my boss who doesn't understand what it is he's asking.

I have a table which we update daily. What he needs is a different table to update with the last 14 days of the first table.

The main issue is that the first table is set up with the dates vertical from top to bottom. The second needs to be set up horizontally.

Example:

Table 1:

Date..............Item 1...............Item 2..............Item 3
07/15..............30......................25....................15
07/16..............32......................30....................16
07/17..............34......................35....................17
07/18..............36......................40....................18
07/19..............38......................45....................19
07/20..............40......................50....................20
07/21..............42......................55....................21

Needs to show in table 2:

Date:......07/15.....07/16.....07/17.....17/18....
Item 1:.....30..........32..........34..........36.......
Item 2:.....25..........30..........35..........40.......
Item 3:.....15..........16..........17..........18.......


Now, I already know that to do dynamic CHARTS I need to do offsets, but I can't for the life of me get this one. I've been scouring Google and a bunch of sites all day to no avail.

First person who gets me the solution I'll send a mug to.

---------- Post added at 04:15 PM ---------- Previous post was at 04:15 PM ----------

Oh, and remember it only shows the LAST 14 ENTRIES on the second table.

God this boss is killing me!
 
Dave

Dave

Staff member
WOOT!

I got it! And all on my own, too!

---------- Post added at 04:41 PM ---------- Previous post was at 04:40 PM ----------

I simply put in "=OFFSET(DashboardData!$J$1,COUNT(DashboardData!$J:$J)-13,0,1,1)" and then did -12, -11, -10...

It works and my boss has no idea what he's put me through to do this.
 
Necronic

Necronic

Staff member
yeah I was going to suggest transpose. Its a little wonky because you have to reference the range as an array.

From what you have described though...you really should consider using a database as opposed to excel. IMHO anytime you get past 1000 cells in Excel it starts behaving poorly. Sorting and drawing out specific sets of data becomes ridiculously easy.

Also....just a nitpick....but I don't see a Macro in here anywhere.

I have a macro question though. Does anyone work with DAO or ADO? I wrote something up using DAO, which is supposedly obsolete, but I don't know if its worth the effort to rewrite it to ADO.
 
Status
Not open for further replies.
Top