Merging data from multiple sheets without duplication

Hi all

I am using Office 365 ProPlus and am looking to achieve a master sheet based on a number of sub sheets.

The sub sheets all contain specific data regarding technical data; one is for hardware which includes make, model, serial number, assigned user etc and another sub sheet contains service information such as mobile carrier, mobile phone number, SIM card number, assigned user etc.

Now the common element is the user (userID) so I want to bring the multiple sheets together into one master reference sheet for distribution however wish to retain the individual subsheets to make changes to and separate the service and hardware elements for reporting.

Can anyone suggest a way that I can start with a userID and then perform lookups on the sub sheets to bring all the service and hardware details together? Any changes on the sub sheets are then reflected in the master sheet?

- I used to be quite good on Excel about 20 years ago but as they say 'use it or lose it' and I have certainly lost it!!!

Thanks all
Ian

Index / Match array maybe? Can you attach a sample file please?

Originally Posted by xjohnson

Index / Match array maybe? Can you attach a sample file please?

Here is a sample file which contains users who have mobile SIM cards and users that have tablets / mobile devices. In some sceanrios users could have multiple devices (laptops, mobile handsets, tablets etc) some with a SIM card and some without. I need to try and bring all the data together on one sheet with columns titled with the sheet name for that device.

Okay first look I can see different column headings.

Perhaps if you want to combine all data you should have a master sheet that has all possible column headings and then it can be done.

I can redo the master sheet however not all devices have the same attributes (if there was a sheet for laptops on this example then they wouldnt have IMEI which is an attribute only on the mobile devices).

I will see if I can standardise a master sheet across all hardware / device types.

Maybe like this?