Returning employee status based on client

GREGRACH

New Member
Joined
Mar 8, 2017
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
Hi Everyone,
Firstly, thanks for reading my question.
I have a spreadsheet that lists employee names, their status and the clients they are assigned to. I am trying to create a separate sheet to create a report to send to clients that lists the employees that are assigned to them and their status. I'm not sure if I should be using vlookup or a pivot table or something else? I'm sure there is a simple answer but I'm having no luck. I've attached some image examples.
Any help would be hugely appreciated!
Thank you

* I should add that in the report, the 'select client name' is a data validation dropdown list.
 

Attachments

  • Screen Shot 2021-09-23 at 12.53.23 pm.png
    Screen Shot 2021-09-23 at 12.53.23 pm.png
    66.2 KB · Views: 6
  • Screen Shot 2021-09-23 at 12.53.36 pm.png
    Screen Shot 2021-09-23 at 12.53.36 pm.png
    64.7 KB · Views: 6

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,657
Office Version
  1. 365
Platform
  1. Windows
1. What version of excel are you using? Please update your profile so that we can tailor a solution.
2. We cannot manipulate data in a picture. Please update your question with sample data using the XL2BB function provided in this forum.
 

GREGRACH

New Member
Joined
Mar 8, 2017
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
1. What version of excel are you using? Please update your profile so that we can tailor a solution.
2. We cannot manipulate data in a picture. Please update your question with sample data using the XL2BB function provided in this forum.
Apologies. Attached.

excel test file.xlsx
ABCDEFG
1Employee NameStatusAssigned to Client
2AAA ElectricalAdrians FlyersRidleysColesMyer
3Joein progressyes
4Billcompleteyes
5Geraldnewyesyesyes
6Susancompleteyesyes
7Fredin progressyesyes
8Debbiecompleteyesyesyesyes
9Franknewyes
10Juliacompleteyes
11Milescompleteyesyesyes
12Bobin progressyesyes
13
Employee Status
Cells with Data Validation
CellAllowCriteria
B3:B12List='Data Validation lists'!$A$1:$A$3
C3:G12List='Data Validation lists'!$B$1:$B$3


excel test file.xlsx
ABC
1Report
2
3
4Select Client NameAdrians Flyers
5
6
7EmployeeStatus
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Client Form
Cells with Data Validation
CellAllowCriteria
B4List='Employee Status'!$C$2:$G$2
 

GREGRACH

New Member
Joined
Mar 8, 2017
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
1. What version of excel are you using? Please update your profile so that we can tailor a solution.
2. We cannot manipulate data in a picture. Please update your question with sample data using the XL2BB function provided in this forum.
Thank you. I have updated everything. Thanks for replying.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,657
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My apologies, but I am unable to assist you as my solution requires a Windows Operating System. I would use Power Query, but unfortunately this is not an option for you as it not available for MAC operating systems. Good Luck.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,657
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Alex, it was my understanding that PQ had not been added to 365 for MAC.

Note to OP: Heading out for a Hike in the Rockies. Will check back later and give you Mcode if no one has offered up a solution by the time I return.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,657
Office Version
  1. 365
Platform
  1. Windows
Here is the Mcode for a Power Query Solution

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Status", type text}, {"AAA Electrical", type text}, {"Adrians Flyers", type text}, {"Ridleys", type text}, {"Coles", type text}, {"Myer", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee Name", "Status"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Status", "Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute"}, {{"Data", each _, type table [Employee Name=nullable text, Attribute=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Employee Name", "Index"}, {"Employee Name", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Employee Name")
in
    #"Pivoted Column"

Book13
ABCDEFG
2Employee NameStatusAAA ElectricalAdrians FlyersRidleysColesMyer
3Joein progressyes
4Billcompleteyes
5Geraldnewyesyesyes
6Susancompleteyesyes
7Fredin progressyesyes
8Debbiecompleteyesyesyesyes
9Franknewyes
10Juliacompleteyes
11Milescompleteyesyesyes
12Bobin progressyesyes
13
14Attribute123456
15AAA ElectricalDebbieMilesBob
16Adrians FlyersGeraldSusanDebbieFrankJuliaBob
17ColesJoeBill
18MyerGeraldSusanFredDebbieMiles
19RidleysGeraldFredDebbieMiles
20
Sheet1
 
Solution

GREGRACH

New Member
Joined
Mar 8, 2017
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
Here is the Mcode for a Power Query Solution

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Status", type text}, {"AAA Electrical", type text}, {"Adrians Flyers", type text}, {"Ridleys", type text}, {"Coles", type text}, {"Myer", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee Name", "Status"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Status", "Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute"}, {{"Data", each _, type table [Employee Name=nullable text, Attribute=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Employee Name", "Index"}, {"Employee Name", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Employee Name")
in
    #"Pivoted Column"

Book13
ABCDEFG
2Employee NameStatusAAA ElectricalAdrians FlyersRidleysColesMyer
3Joein progressyes
4Billcompleteyes
5Geraldnewyesyesyes
6Susancompleteyesyes
7Fredin progressyesyes
8Debbiecompleteyesyesyesyes
9Franknewyes
10Juliacompleteyes
11Milescompleteyesyesyes
12Bobin progressyesyes
13
14Attribute123456
15AAA ElectricalDebbieMilesBob
16Adrians FlyersGeraldSusanDebbieFrankJuliaBob
17ColesJoeBill
18MyerGeraldSusanFredDebbieMiles
19RidleysGeraldFredDebbieMiles
20
Sheet1
Thanks so much for taking the time to provide a solution. Apologies I didn't reply sooner. I didn't get a notification you had replied so have only just checked. Thanks again!
 

Forum statistics

Threads
1,147,823
Messages
5,743,405
Members
423,792
Latest member
travisds

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
Top