Is PivotTable the best solution for my problem?

[fapb48]

Hi everyone,

I have a spreadsheet that registers all Active Hardware Assets in our company.

I have two sheets. One for computers and the other one for other hardware (monitors, docking stations, printers, etc)

I want to be able to view in a separate sheet what hardware each individual user has.

I thought in creating a pivot table to link to both tables.. Currently my pivot table is filtering in the following order: Departments, Assigned to and Computer Name.

I want to be able to now incorporate the other hardware sheet into this pivot table, but it duplicates all the contents on that sheet into the pivot table instead of having a relationship of assigning all hardware to a specific name?

I wonder if a pivot table is actually useful for this situation (and if there is, what am i missing) or should i think about other options to have the end result that i require?

Thank you

 

[alansidman]

Suggest you post using XL2BB, sample data (dummy) which is representative of your actual data. Mock up your solution for the data presented.

 

[fapb48]

PC Sheet

Make and Model	Computer Name	Asset Number	OS	Windows 11 Compatability			PC/LPT	RAM	Warranty Start Date	Warranty Expiry Date	Warranty Period	Warranty Status	Age	Assigned to	Department
Lenovo ThinkPad E15 Gen 4	LPT000770	770	Windows 11 Pro	N/A			LPT	16	25/01/2023	24/01/2024	0	Warranty	0	Dave	IT & Ops
Lenovo Essential B50-70	PC000676	945	Windows 10 Pro	No			LPT	8	07/08/2014	05/10/2015	1	Expired	8	Bianca	Procurement
Yoga 510-14ISK Laptop (ideapad) - Type 80S7	LPT000771	505	Windows 10 Pro	No			LPT	8	18/07/2016	15/09/2017	1	Expired	6	Richard	IT & Ops
Yoga 510-14ISK Laptop (ideapad) - Type 80S7	PC000677	594	Windows 10 Pro	No			LPT	8	02/01/2017	02/01/2018	1	Expired	6	Spare	IT & Ops
HP 250 G6 Notebook PC	LPT000772	667	Windows 10 Pro	No			LPT	8	11/12/2018	09/01/2020	1	Expired	4	Spare	IT & Ops
Yoga 510-14ISK Laptop (ideapad) - Type 80S7	PC000678	88	Windows 10 Pro	No			LPT	8	01/03/2017	28/02/2018	0	Expired	5	Spare	IT & Ops
HP ProDesk 400 G5 Microtower PC	LPT000773	588	Windows 10 Pro	Yes			PC	8	07/05/2019	06/05/2020	1	Expired	3	Darren	Sales
ThinkBook 14-IIL Laptop - Type 20SL	PC000679	712	Windows 11 Pro	N/A			LPT	8	28/01/2021	27/01/2022	0	Expired	2	George	Sales
Lenovo ThinkCentre M70q Gen 2	LPT000774	675	Windows 11 Pro	N/A			PC	16	11/08/2022	10/08/2025	3	Warranty	0	Hot Desk	IT & Ops
Lenovo E495 (Type 20NE) Laptop (ThinkPad)	PC000680	970	Windows 10 Pro	Yes			LPT	16	06/03/2020	05/03/2021	0	Expired	2	Spare	IT & Ops
HP ProDesk 400 G4 SFF	LPT000775	57	Windows 10 Pro	No			PC	8	24/09/2017	23/10/2018	1	Expired	5	Vmware	IT & Ops
Lenovo ThinkCentre M70q Gen 2	PC000681	774	Windows 11 Pro	N/A			PC	8	11/08/2022	10/08/2025	3	Warranty	0	Hot Desk	IT & Ops
Lenovo V530-15ICB 10TV0024UK	LPT000776	941	Windows 10 Pro	Yes			PC		14/05/2019	13/05/2020	1	Expired	3	Morgan	IT & Ops
HP ProDesk 400 G4 SFF	PC000682	101	Windows 10 Pro	No			PC	16	19/12/2017	17/01/2019	1	Expired	5	Training	Meeting Room
E14 (Type 20RA, 20RB) Laptop (ThinkPad)	LPT000777	786	Windows 11 Pro	N/A			LPT	8	01/10/2020	01/09/2021	0	Expired	2	Natalie	HR
HP ProDesk 400 G4 SFF	PC000683	782	Windows 10 Pro	No			PC	8	19/03/2018	17/04/2019	1	Expired	4	Aston	Meeting Room
HP 290 G1 SFF Business	LPT000778	881	Windows 10 Pro	Yes			PC	8	29/08/2018	28/08/2019	0	Expired	4	Lotus	Meeting Room

Other Hardware Sheet

Device Type	Asset Number	Make	Model			MAC	IMEI	Assigned to	Department
Monitor	269	AOC	e2250Swa					Morgan	IT & Ops
Monitor	270	AOC	e2250Swa					Bianca	Procurement
Docking Station	773	Lenovo	ThinkPad Hybrid USB-C with USB-A Dock - Type 40AF					Morgan	IT & Ops
Docking Station	774	Lenovo	ThinkPad Hybrid USB-C with USB-A Dock - Type 40AF					Bianca	Procurement

Pivot Table Output

Row Labels
(blank)
(blank)
(blank)
Docking Station
Monitor
HR
Natalie
LPT000777
Docking Station
Monitor
IT & Ops
Dave
LPT000770
Docking Station
Monitor
Hot Desk
LPT000774
Docking Station
Monitor
PC000681
Docking Station
Monitor
Morgan
LPT000776
Docking Station
Monitor
Richard
LPT000771
Docking Station
Monitor
Spare
LPT000772
Docking Station
Monitor
PC000677
Docking Station
Monitor
PC000678
Docking Station
Monitor
PC000680
Docking Station
Monitor

So as you can see, other hardware sheet has only 2 names assigned against hardware but the pivot table is assigned them all against all users

 

[fapb48]

Suggest you post using XL2BB, sample data (dummy) which is representative of your actual data. Mock up your solution for the data presented.

Do you happen to have a better idea for my issue above?

 

[alansidman]

To be sure, the common field is the asset number. Is this true?

 

[fapb48]

To be sure, the common field is the asset number. Is this true?

The common field needs to be the name of the user.. “assigned to” column

 

[alansidman]

Load each table into the Power Query Editor and then perform a left Outer Join on the Assigned to and Department as shown below

let
    Source = Table.NestedJoin(Table2, {"Assigned to", "Department"}, Table3, {"Assigned to", "Department"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Device Type", "Asset Number", "Make", "Model", "Column1", "Column2", "MAC", "IMEI"}, {"Device Type", "Asset Number.1", "Make", "Model", "Column1.1", "Column2.1", "MAC", "IMEI"})
in
    #"Expanded Table3"

 

[fapb48]

Load each table into the Power Query Editor and then perform a left Outer Join on the Assigned to and Department as shown below

let
    Source = Table.NestedJoin(Table2, {"Assigned to", "Department"}, Table3, {"Assigned to", "Department"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Device Type", "Asset Number", "Make", "Model", "Column1", "Column2", "MAC", "IMEI"}, {"Device Type", "Asset Number.1", "Make", "Model", "Column1.1", "Column2.1", "MAC", "IMEI"})
in
    #"Expanded Table3"

Apologies for the delayed response but it worked perfectly!
Thank you very much for your help

 

[alansidman]

thanks for the feedback