Log In
Register
Home
August 18, 2022
Offline
Hi All,
I have a few questions:
1. I don't know how to filter the column "Expat/Inpat.
a. My requirement is I want the data in end result only "Inpat" and don't want it to appear in my end result
2. I add a new column and name. My new column name is User-type and custom_field: Employee ID.
a. My requirement for column "User-type" is I want employee id 00012 as an admin, employee id 00017 as a lecturer and another employee id is a student
b. My requirement for column "custom_field: Employee ID", I want to pull from column "Login" without "MY"
3. For filtering for a date after today and status, I noticed if the column "Leave Date" in my original data has a hyphen(-), my end result is not extracting the data. I want it to be as "Active". And, the hyphen(-) will be blank in my end result. Last but not least, the date format will be dd/mm/yyyy.
Thank you for your help.
Trusted Members
Moderators
Power BI
January 31, 2022
Online
It would be helpful if you could attach a file so that we can see what you are talking about. If you did try, perhaps you forgot to press "Start upload" befor submitting your post.
August 18, 2022
Offline
Herewith attached are the files
Trusted Members
Moderators
Power BI
January 31, 2022
Online
Thanks for the file. I'm not convinced that VBA is the correct tool. It seems to be ideal for Power Query. Are you familiar with that?
Looking at the file, though, I'm confused. You describe that you only want "Inpat" not "Expat" but the output column AI includes "Expat".
Then you want to include a user type. How should Excel know the type to attach to each user, as it is not part of the original data set?
And regarding the date filtering, you want to use >= todays date (column J) and active status (column I), and I think you want to include active employees without a leave date. Correct?
August 18, 2022
Offline
Hi Riny,
Thanks for the reply.
For questions 1 and 3 I manage to do.
But I still stuck to do for question no 2(a).
I want to be set for column "User-type" as per employee id 00012 as an admin, employee id 00017 as a lecturer, and another employee id as a student.
Trusted Members
Moderators
Power BI
January 31, 2022
Online
I understand the question regarding 2(a), but how is Excel supposed to know who is admin, a lecturer or a student? You need to have that information listed somewhere if you want to include in the final output.
August 18, 2022
Offline
My column E is dependent on column A
Okay so if the id is 0012 then the column user type needs to display as super admin
Else if the id 0017 needs to be an instructor
Else if the learner it will display on the other id like 0013,0014,0019
I attach the latest file for you refer.
August 18, 2022
Offline
Already found the solution. Thanks
Trusted Members
Moderators
Power BI
January 31, 2022
Online
Perhaps the attached file does what you ask for. It contains a table (the green one) created with Power Query, based on the data in columns A:O.
1 Guest(s)
