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
1Employee IDUser IDEmployee NameBadge Barcode IDDepartment IDEmployment Start DateEmployment TypeEmployee StatusManager NameTemp Agency CodeJob TitleManagement Area IDShift PatternProcessContact Number
15101248396kaushallKaushally,Klair11269694120005006-Jun-163PTYActiveCole,JoeTRSWhse Assoc13DC4-0800Picking*
20101249251issafahIssafah,Muhammad11085494120005007-Jun-163PTYActiveCole,JoeTRSWhse Assoc13DB3-0800Picking*
22100794918weldeabkKefle,Weldeab11117768120007025-Sep-153PTYActiveColtman,Macy ElizabethTRSWhse Assoc27NA5-1900IC/QA/CS*
24101249246annatureTurek,Anna11180696120006007-Jun-163PTYActiveBickley,Connor DavidTRSWhse Assoc17NB3-1900Pack - Batch*
25100827767grechiaGrechi,Alessandro11213470120007009-Oct-153PTYActiveColtman,Macy ElizabethTRSWhse Assoc27NA5-1900IC/QA/CS*
26101263294hilitanuHilitanu,Remus212912120005014-Jun-163PTYActiveFarmer,ChristopherTRSWhse Assoc13DB3-0800Picking*
29101249238hayatulzZazay,Hayatullah224183120005007-Jun-163PTYActiveCole,JoeTRSWhse Assoc13DB3-0800Picking*
32100038829lhingleyHingley,Lisa11084059120030-Sep-153PTYActiveWynn,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>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


MyTime Sim

*BCDEFGHIJKL
1Employee IDEmployee NameEmployee LogonSchedule GroupEE TypeManager NameAgency NameLocationCost CenterBadge NumberOn
Premises
2101202785Abbaas, HasanhabbaasDH3-0800HGupta, Puneet BansalTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center2813283*
3100721642Abbas, AhmadabahmadNB3-1900HSmith, LeeTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center3728919X
4101272373Abdalbaree, SomiaabdalbarDC4-0800HEllis, JamesTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center3729029*
5101229682Abdelshafa, HaythamabdelshaDB3-0800HGupta, Puneet BansalTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center4432041*
6100645644Abdullah, Mohamed100645644NN2-1900HYadwad, SiddharthTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center**
7101218109Abdullah, OmeromerabduDA5-0800HFarmer, ChristopherTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center4431673*
8101207019Abdulle, Yusuf AhmedabdulleyDC4-0800HEllis, JamesTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center3740480*
9101275013Abdulrahman, AmaanabdamaanDA5-0800HStys, ArturTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center4431386*
10100875253Abnam, MohammedabnammNB3-1900HDavies, John AnthonyTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center3552132X
11101013899Abraha, SimonabrsimonNC4-1900HSmith, MartinTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center3160423X
12101281367Abraha, TedrosatedrosNB3-1900HSmith, LeeTRS-TRANSLINEBHX1 - Birmingham, UKFulfillment Center3728916*

<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>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


ADAPT Sim

*ABCDEFGHIJK
1WarehouseEmployee IDEmployee LoginEmployee NameEmployment StatusEmployment TypeManager NameCreated DateTypeLevelStatus
2BHX11.01E+08mokhalilKhalil,MohammedActiveTGupta,Puneet Bansal2016-06-25T17:01:14.702Z,1466874074702BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
3BHX11.01E+08stafieStafie,ConstantinActiveTTarnaud,Florian2016-06-25T16:31:07.969Z,1466872267969BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
4BHX11.01E+08floridumDumitru,FlorinActiveTCole,Joe2016-06-25T16:26:55.450Z,1466872015450BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
5BHX11.01E+08janotothToth,JanosActiveTTiwana,Harby2016-06-25T16:07:19.695Z,1466870839695BEHAVIORALCONVERSATIONPENDING_REVIEW
6BHX11.01E+08navakhtaAkhtar,NaveedActiveTCole,Joe2016-06-25T15:08:02.070Z,1466867282070BEHAVIORALCOUNSELLINGPENDING_REVIEW
7BHX11.01E+08meharaliAli,MeharActiveTFarmer,Christopher2016-06-25T15:00:28.281Z,1466866828281BEHAVIORALCONVERSATIONPENDING_REVIEW
8BHX11.01E+08darremcwMcWilliams,DarrenActiveTTarnaud,Florian2016-06-25T13:40:26.453Z,1466862026453BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
9BHX11.01E+08hsmzHassan,MohamedActiveTTarnaud,Florian2016-06-25T13:33:58.520Z,1466861638520BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
10BHX11.01E+08muhmaAli,Muhammad ShaheedActiveTTarnaud,Florian2016-06-25T13:24:33.218Z,1466861073218BEHAVIORALSUPPORTIVE_CONVERSATIONPENDING_REVIEW
11BHX11.01E+08segunjJunaid,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>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Test

*ABCDE
1***In or not inTRS
2jasbinjasbin*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>
</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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,216,091
Messages
6,128,775
Members
449,468
Latest member
AGreen17

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