Big time help needed

[KingSwish828]

Ok here is my problem and I am hoping someone can help me out with this. I will have 10,000 rows of data and I will sort that by category and I have 18 categories:

ELIG
OOP
MISS
RESP
DUPC
INCL
MEDN
CODE
PROV
TIFI
PEND
NOAC
OTHER
IRTI
ADAC
PAID
ASTER
INFO

The number of data for each category will vary. I currently will sort it by category and manually select 3% for each category to be reviewed for QA. This is a painfuly long task to do manually. Is there some way to create a formula,macro or vba code to select the 3% percent for me randomly? I would need it to pull the entire row and place it in another work sheet or highlight the row to be review?


Below is what my data wil look like:

<TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=534 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 34.5pt; mso-height-source: userset" height=46><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 34.5pt; BACKGROUND-COLOR: transparent" width=64 height=46>swi</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>GW65</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>W9</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>MISS</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=75>11/12/2010</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=75>9/12/2010</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>123456</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>josmith

</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=534 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>her</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>PPHN</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>PR16</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>PEND</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=75>11/22/2010</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=75>9/8/2010</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>1234566</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>josmith</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=534 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 60pt" height=80><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 60pt; BACKGROUND-COLOR: transparent" width=64 height=80>wih</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>AETL1</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>COB7</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>PROV</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=75>11/22/2010</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=75>9/2/2010</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>987654</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>josmith</TD></TR></TBODY></TABLE>

 

[KingSwish828]

I have Excel 2007

 

[freesurf]

I don't think there's an easy fix for this. However, I think the problem can be broken into stages.

1) establish how many times each category appears - e.g. say ELIG appears 648 times

2) multiply the answer to Q1 (for each category) by 0.03 and then round up to the nearest integer. this tells you how many samples are needed in respect of each category - e.g. for ELIG we get 20 values needed ie. roundup(0.03*648,0)

3) now code each entry in your 10000 sample so that you know the nth instance for each category. e.g. suppose that the first 6 entries in the big population are:

# categ
1 ELIG
2 OOP
3 RESP
4 DUPC
5 CODE
6 ELIG

you need to develop a way of assigning the nth instance, i.e. third and therefore fourth columns below

# categ instance lookup code
1 ELIG 1 ELIG00001
2 OOP 1 OOP00001
3 RESP 1 RESP00001
4 DUPC 1 DUPC00001
5 CODE 1 CODE00001
6 ELIG 2 ELIG00002

4) now sample without replacement. For example for ELIG we need to sample 20 numbers from the range {1 to 648}. to so this develop some VBA, here's a link that explains how:

http://www.anthony-vba.kefra.com/vba/excelvba-simulation.htm#Double_Sorting_-_The_secret_of

5) let's suppose that the first three of the 20 ELIG random draws (from step 4) are 2,5 and 583. You simply need to look up ELIG00002, ELIG00005, ELIG000583.

6) Repeat the process for all eighteen categories.

I will try and attach a workbook showing steps 1 to 3. But you will need to invest time into step 4 and 6. Step 5, is straightforward.

HTH

 

[freesurf]

Sorry, unable to upload workbook, but to get excel to iteratively count up the nth instance use the following formula in cell D23:

=COUNTIF($C$23:C23,C23)

which assumes that your raw data starts in cell C23, with column C containing the categories and where you drag this formula down the page by 10000 rows.

then to create the format of the unique code (in cell E23) use:
=C23&TEXT(D23,"00000")

 

[KingSwish828]

thanks...I will work with this and see how it goes.