Help Needed: Layout suggestions - Merging data from many spreadsheets into one

[Helen842000]

Hi All,

I'm working on a bit of a mammoth task and I need to figure out what's the most organised way to display all the data on a master spreadsheet.

I have about 8-10 spreadsheets that contain first names & surnames and the persons User ID for a specific location. Each spreadsheet has (roughly) the same people listed and is specific to one location.

I need to bring all the spreadsheets together so I have 1 list of people and then we can see what ID's they have across every location.

Now it would be quite straight forward if I could just list the master spreadsheet as follows

Surname, Firstname, ID(Loc1), ID(Loc2), ID(Loc3) etc etc

However people may have multiple ID's for the same location which would mess up the columns when importing them.

Would I be best creating a layout that looks more like this? Which would leave room for people to have as many ID's at they need.

Surname, First Name
Location 1 : ID
Location 1 : ID
Location 2 : ID

I want to make sure I get the layout right because I'm pretty sure new locations and people will continue to be added to this, so I want to ensure it has room for expansion!

Also if anyone has any tips in general on simplifying this process or avoiding errors while importing, it would be really appreciated! e.g matching names across spreadsheets.


Thank you!

 

[bibleguy125]

Does you only need to see one person's information at a time, or everyone's information on one huge sheet? An example workbook would help.

 

[Helen842000]

Does you only need to see one person's information at a time, or everyone's information on one huge sheet? An example workbook would help.

Thanks for your reply. It's just going to be one huge sheet, peoples names and whatever ID's they have.

 

[bibleguy125]

I think if you are making one big sheet, the most sensible would be to run the location names across the top, the employees down the first row and have the ID's for each location fill in. An easy way using formulas would be VLOOKUP. Then you can be sure that all of the employees are matched with the correct ID's. You will have to enter the formulas and it does make for a fairly slow sheet (opening, linking, saving, etc), but it is accurate. Another option is to use macros. What are you most comfortable with?