Vba macro filter data set

[kanchan_mita]

I have 25000[approx,varies each day] records of data entry done by agents. And then 1st tier qc occurs by phone calling. They give status of data entry as accepted or rejected.but i need to do 2nd tier qc of 1% or more[varies if data entry size is small/large] from qc done by 1st tier to check their provided status.

Col1:agent name of 1st tier qc
Col 2: online requester name
Col3: add
Col4: ph
Col5: amnt
Col6: date of order
Col7: item
Col8: qc status accepted/rejected
Col9: serial no.

I want to qc 2nd tier every day of certain percent for each 1st tier qc agents and qc status.need help in developing this by vba/macro or any formula.

Ex: after i select 2% from drop down/radio button. 2% of 1000 item is selected with 10 item qc by mr x, 10 by mr y and so on,consisting 5 accepted and 5 rejected item ordered. And then extract or select that list to another sheet.

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kanchan_mita]

Attachment 628928Attachment 628929 need urgent help. my excel screenshot is given. thanks

[Pepe Le Mokko]

1. As you can read in post #2, we ask NOT to attach pictures but a worksheet
2. As this forum is working with volunteers helping you on their spare time, you cannot expect them to be waiting for your question to answer urgently. If you need an urgent response, commercial services will be more then happy to help

[kanchan_mita]

Dear Pepe Le Mokko sorry for that. my exceL is attached.

[JeteMc]

Perhaps this formula based proposal will help.
This proposal employs two helper columns per agent on the 'orders' sheet which are populated using: =IF(AND($P3=Q$1,$O3=Q$2),RAND(),"")
It also employs three helper columns on the '2nd tier qc' sheet.
The helper labeled 'Column' is populated using: =IF(P11=0,"",AGGREGATE(15,6,(COLUMN(Q$1:V$1)-COLUMN(P$1))/(orders!Q$1:V$1=P11)/(orders!Q$2:V$2=O11),1))
'Random()#' is populated using: =IF(P11=0,"",AGGREGATE(15,6,INDEX(orders!Q$3:V$302,,Q11),COUNTIFS(P$11:P11,P11,O$11:O11,O11)))
'Row' is populated using: =IF(P11=0,"",MATCH(R11,INDEX(orders!Q$3:V$302,,Q11),0))
In the output table 'Serial' is populated using: =IF(P11=0,"",INDEX(orders!A$3:A$302,S11))
'MISSDN':'Order date' are populated using: =IF($P11=0,"",INDEX(orders!B$3:B$302,MATCH($A11,orders!$A$3:$A$302,0)))
'Status' is populated using: =IF(P11=0,"",IF(COUNTIFS(P$11:P11,P11)<=B$7,A$7,A$8))
'1st hand qc by' is populated using: =INDEX(N$1:N$4,MATCH(ROW(A1)-1,O$1:O$4))
Note that much of the table is formatted to hide zeros by using white font.
Let us know if you have any questions.

[kanchan_mita]

thanks for the feedback, but i need to generate the 2nd tier qc list in a automated way, so that if the emp no. increase in 1 st tier and new status comes up then it wont be a problem.

[JeteMc]

Gave this a few days to see if anyone else would chime in. Here's an updated version that will add names, although it does not address the issue of adding a new status.
Some of the formula updates include:
1. On 2nd tier qc sheet V2:V8 =IFERROR(IF(MATCH(0,INDEX(COUNTIF(V$1:V1,orders!P$3:P$3000),,),)>COUNTA(orders!P$3:P$3000),"",INDEX(orders!P$3:P$3000,MATCH(0,INDEX(COUNTIF(V$1:V1,orders!P$3:P$3000),,),))),"")
2. P11:P26 =IF(ROW(A1)>PRODUCT(B$6:B$8),"",(INDEX(V$2:V$8,MATCH(ROW(A1)-1,W$2:W$8))))
3. On the Orders sheet Q1:Z1 =IF(COLUMN(A1)>PRODUCT('2nd tier qc'!$B6:$B7),"",INDEX('2nd tier qc'!$V2:$V8,MATCH(COLUMN(A1)-1,'2nd tier qc'!$X2:$X8)))
Test by putting a new name in some of the rows in column P on the Orders sheet.
Let us know if you have any questions.