Importing data from Excel and want to organize by a specific criteria and randomize

[olathe]

Hello all,

I have a data table in Excel with 20+ headers. It deals with insurance, and the only two columns that I want to use are "Claim Number" and "Indemnity $" Using only these two columns, I want to organize the Claim Numbers by the $$ range they fall within (we call them Buckets, and there are 6 of them: NA, $0-500,501-2500,2501-10000,10001-50000,50001-250000). In excel, this is done using a Vlookup.

I also have a list of "Already Reviewed" claims in Excel that I want to use to eliminate claims from the "master" Claim Numbers list because you don't want to review a claim twice. Again, this would be a Vlookup in Excel.

Once I have this list of Claim Numbers alongside the Bucket they fall under (with the already reviewed claims eliminated), I need to separate each bucket so I can randomize the order within the bucket.

Last, once I have each Bucket separated and randomized, I need to run the equivalent of the Excel Solver-Analysis tool. That is, say I am told, "We need you to pick out 25 Claims from this list of 5,000 claims but in way thats proportionate to each bucket." This means for the 25 claim sample, I will choose more from a bucket with 4,000 claims in it than a bucket with 200 claims.

I just realized this is a lot, and I am not sure that the last two steps couldn't be reversed so that I can pick out my claim sample before I randomize. Regardless, any amount of help or feedback would be appreciated!

-Olathe.

[June7]

Item 1 is not so difficult. Calculate a group identifier field in query. One way:

Switch([Indemnity $]<=500,1, [Indemnity $]<=2500,2, [Indemnity $]<=10000,3, [Indemnity $]<=50000,4, [Indemnity $]<=250000,5)

When does NA apply? What about >250000?

Item 2 to eliminate the already reviewed could possibly be a query joining tables and criteria of Is Null. Give details of relevant table and field names.

Item 3 to 'separate' each bucket and randomly select records would probably mean 6 queries.

Randomization methods beyond my experience but numerous threads discuss the topic. Search forum and web.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.