Use VBA to create a new sheet and copy only if a condition is met

K

kasse1982

New Member
Joined
Jun 26, 2016
Messages
7
Hello

I am working for a recruitment company and I need to sort daily feedbacks for our employees. I have a sheet with all our employees (TRS Workforce), I get a list from a program and paste it into a new sheet (called MyTime Sim) and I get another list from a different program with all feedbacks (ADAPT Sim). Number of rows differ everytime.

I want to retrieve from MyTime Sim sheet only employees from "On Premises" (L column) tab and have an X in cell (if they have an X in cell, it means they have clocked in and are inside), then if they are inside I need to sort only people from my agency (lookup into TRS Workforce) and then, if they match and are in, to lookup into ADAPT Sim and retrieve only employees with a feedback then copy into another sheet their User ID, Employee Name, Process and Level (from ADAPT Sim).

I have used an array forumula, but it takes too much and at work probably will crash my computer. At the bottom you will see what formula I have used.

Also, if there is any other way without using VBA can replay it, please?

Can someone please help me?

TRS Workforce

*ABCDEFGHIJKLMNO
kaushallKaushally,Klair3PTYActiveCole,JoeTRSWhse AssocDC4-0800Picking*
issafahIssafah,Muhammad3PTYActiveCole,JoeTRSWhse AssocDB3-0800Picking*
weldeabkKefle,Weldeab3PTYActiveColtman,Macy ElizabethTRSWhse AssocNA5-1900IC/QA/CS*
annatureTurek,Anna3PTYActiveBickley,Connor DavidTRSWhse AssocNB3-1900Pack - Batch*
grechiaGrechi,Alessandro3PTYActiveColtman,Macy ElizabethTRSWhse AssocNA5-1900IC/QA/CS*
hilitanuHilitanu,Remus3PTYActiveFarmer,ChristopherTRSWhse AssocDB3-0800Picking*
hayatulzZazay,Hayatullah3PTYActiveCole,JoeTRSWhse AssocDB3-0800Picking*
lhingleyHingley,Lisa3PTYActiveWynn,TrevorTRSFC Manager**-*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:100px;"><col style="width:82px;"><col style="width:124px;"><col style="width:129px;"><col style="width:113px;"><col style="width:165px;"><col style="width:134px;"><col style="width:125px;"><col style="width:159px;"><col style="width:140px;"><col style="width:80px;"><col style="width:153px;"><col style="width:100px;"><col style="width:106px;"><col style="width:123px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Employee ID[/TD]
[TD="align: center"]User ID[/TD]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Badge Barcode ID[/TD]
[TD="align: center"]Department ID[/TD]
[TD="align: center"]Employment Start Date[/TD]
[TD="align: center"]Employment Type[/TD]
[TD="align: center"]Employee Status[/TD]
[TD="align: center"]Manager Name[/TD]
[TD="align: center"]Temp Agency Code[/TD]
[TD="align: center"]Job Title[/TD]
[TD="align: center"]Management Area ID[/TD]
[TD="align: center"]Shift Pattern[/TD]
[TD="align: center"]Process[/TD]
[TD="align: center"]Contact Number[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]101248396[/TD]

[TD="align: right"]11269694[/TD]
[TD="align: right"]1200050[/TD]
[TD="align: right"]06-Jun-16[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]101249251[/TD]

[TD="align: right"]11085494[/TD]
[TD="align: right"]1200050[/TD]
[TD="align: right"]07-Jun-16[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]100794918[/TD]

[TD="align: right"]11117768[/TD]
[TD="align: right"]1200070[/TD]
[TD="align: right"]25-Sep-15[/TD]

[TD="align: right"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]101249246[/TD]

[TD="align: right"]11180696[/TD]
[TD="align: right"]1200060[/TD]
[TD="align: right"]07-Jun-16[/TD]

[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]100827767[/TD]

[TD="align: right"]11213470[/TD]
[TD="align: right"]1200070[/TD]
[TD="align: right"]09-Oct-15[/TD]

[TD="align: right"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]101263294[/TD]

[TD="align: right"]212912[/TD]
[TD="align: right"]1200050[/TD]
[TD="align: right"]14-Jun-16[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: right"]101249238[/TD]

[TD="align: right"]224183[/TD]
[TD="align: right"]1200050[/TD]
[TD="align: right"]07-Jun-16[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: right"]100038829[/TD]

[TD="align: right"]11084059[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]30-Sep-15[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


MyTime Sim

*BCDEFGHIJKL
Abbaas, HasanhabbaasDH3-0800HGupta, Puneet BansalTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*
Abbas, AhmadabahmadNB3-1900HSmith, LeeTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment CenterX
Abdalbaree, SomiaabdalbarDC4-0800HEllis, JamesTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*
Abdelshafa, HaythamabdelshaDB3-0800HGupta, Puneet BansalTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*
Abdullah, MohamedNN2-1900HYadwad, SiddharthTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center**
Abdullah, OmeromerabduDA5-0800HFarmer, ChristopherTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*
Abdulle, Yusuf AhmedabdulleyDC4-0800HEllis, JamesTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*
Abdulrahman, AmaanabdamaanDA5-0800HStys, ArturTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*
Abnam, MohammedabnammNB3-1900HDavies, John AnthonyTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment CenterX
Abraha, SimonabrsimonNC4-1900HSmith, MartinTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment CenterX
Abraha, TedrosatedrosNB3-1900HSmith, LeeTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:110px;"><col style="width:110px;"><col style="width:106px;"><col style="width:182px;"><col style="width:159px;"><col style="width:93px;"><col style="width:59px;"><col style="width:79px;"><col style="width:100px;"><col style="width:85px;"><col style="width:131px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Employee ID[/TD]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Employee Logon[/TD]
[TD="align: center"]Schedule Group[/TD]
[TD="align: center"]EE Type[/TD]
[TD="align: center"]Manager Name[/TD]
[TD="align: center"]Agency Name[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Cost Center[/TD]
[TD="align: center"]Badge Number[/TD]
[TD="align: center"]On
Premises[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]101202785[/TD]

[TD="align: right"]2813283[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]100721642[/TD]

[TD="align: right"]3728919[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]101272373[/TD]

[TD="align: right"]3729029[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]101229682[/TD]

[TD="align: right"]4432041[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]100645644[/TD]

[TD="align: right"]100645644[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]101218109[/TD]

[TD="align: right"]4431673[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]101207019[/TD]

[TD="align: right"]3740480[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]101275013[/TD]

[TD="align: right"]4431386[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]100875253[/TD]

[TD="align: right"]3552132[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]101013899[/TD]

[TD="align: right"]3160423[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]101281367[/TD]

[TD="align: right"]3728916[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


ADAPT Sim

*ABCDEFGHIJK
WarehouseEmployee IDEmployee LoginEmployee NameEmployment StatusEmployment TypeManager NameCreated DateTypeLevelStatus
BHX1mokhalilKhalil,MohammedActiveTGupta,Puneet Bansal2016-06-25T17:01:14.702Z,1466874074702BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
BHX1stafieStafie,ConstantinActiveTTarnaud,Florian2016-06-25T16:31:07.969Z,1466872267969BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
BHX1floridumDumitru,FlorinActiveTCole,Joe2016-06-25T16:26:55.450Z,1466872015450BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
BHX1janotothToth,JanosActiveTTiwana,Harby2016-06-25T16:07:19.695Z,1466870839695BEHAVIORALCONVERSATIONPENDING_REVIEW
BHX1navakhtaAkhtar,NaveedActiveTCole,Joe2016-06-25T15:08:02.070Z,1466867282070BEHAVIORALCOUNSELLINGPENDING_REVIEW
BHX1meharaliAli,MeharActiveTFarmer,Christopher2016-06-25T15:00:28.281Z,1466866828281BEHAVIORALCONVERSATIONPENDING_REVIEW
BHX1darremcwMcWilliams,DarrenActiveTTarnaud,Florian2016-06-25T13:40:26.453Z,1466862026453BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
BHX1hsmzHassan,MohamedActiveTTarnaud,Florian2016-06-25T13:33:58.520Z,1466861638520BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
BHX1muhmaAli,Muhammad ShaheedActiveTTarnaud,Florian2016-06-25T13:24:33.218Z,1466861073218BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
BHX1segunjJunaid,SegunActiveTRitson,Steven Lea2016-06-25T13:03:33.980Z,1466859813980BEHAVIORALFIRST_WRITTEN_WARNINGPENDING_REVIEW

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1.01E+08[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1.01E+08[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Test

*ABCDE
***In or not inTRS
jasbinjasbin*jasbinCOUNSELLING

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:90px;"><col style="width:194px;"><col style="width:80px;"><col style="width:194px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=IFNA(INDEX(TRS_Workforce[User ID],MATCH(A2,TRS_Workforce[User ID],0)),"Not Transline")
D2{=IFNA(INDEX('MyTime Sim'!D:D,MATCH(1,('MyTime Sim'!L:L="X")*('MyTime Sim'!D:D=A2),0)),"")}
E2=IF(D2=B2,INDEX('ADAPT Sim'!J:J,MATCH(A2,'ADAPT Sim'!C:C,0)),"")

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA two dynamic ranges, different sheets--insert problem using arrays
Replies
2
Views
1K
ChrisCom
C

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top