Please help!! I need formula to search across multiple worksheets for duplicate info (staff number), then to extract relevant information (from a part

[scleworthenwl]

Hello,

Please could you help me?

I am a complete novice at Excel and am trying to save myself lots of time! I need to search for 1502 staff details in Excel 2007.

I need to search 9 different tabs to input consolidated info into tab number 10. Each of the 9 tabs contains the following columns:

Surname, Forename, Staff Number, Company, Auth Code, Auth Expiry Date.

The tabs are organised by which specific Auth Codes are held by staff (an example of an auth code would be 113 or 231 etc.) The same staff appear on multiple sheets - the difference between each sheet is the Auth Code and Auth Expiry Date.

I need to consolidate information from the worksheets on to the tenth tab - to show all of the auth codes/expiry dates, that each staff member holds. So that there is just one row per staff member showing all of the Auth Codes that they hold.

I would need the formula to search all of the spreadsheets for a particular Staff Number, then extract the information from the Auth Code column relevant to that person (it would be on the same row as the rest of the info about them) and put that a new cell on a new tab.

 

[oldbrewer]

why cannot the 9 sheets be combined in one sheet and then search just that sheet

 

[oldbrewer]

[TABLE="width: 854"]
<colgroup><col><col><col span="7"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]the table below could be from, say, 3 different sheets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]surname[/TD]
[TD]forename[/TD]
[TD]staffnum[/TD]
[TD]comp[/TD]
[TD]authcode[/TD]
[TD]authexp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]smith[/TD]
[TD]tom[/TD]
[TD="align: right"]123[/TD]
[TD]compA[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]01/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jones[/TD]
[TD]fred[/TD]
[TD="align: right"]234[/TD]
[TD]compB[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]09/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]brown[/TD]
[TD]andy[/TD]
[TD="align: right"]345[/TD]
[TD]compA[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]17/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]smith[/TD]
[TD]tom[/TD]
[TD="align: right"]123[/TD]
[TD]compA[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]25/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jones[/TD]
[TD]fred[/TD]
[TD="align: right"]234[/TD]
[TD]compB[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]02/11/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]brown[/TD]
[TD]andy[/TD]
[TD="align: right"]345[/TD]
[TD]compA[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]10/11/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]smith[/TD]
[TD]tom[/TD]
[TD="align: right"]123[/TD]
[TD]compA[/TD]
[TD="align: right"]678[/TD]
[TD="align: right"]18/11/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jones[/TD]
[TD]fred[/TD]
[TD="align: right"]234[/TD]
[TD]compB[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]26/11/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]brown[/TD]
[TD]andy[/TD]
[TD="align: right"]345[/TD]
[TD]compA[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]04/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count of authcode[/TD]
[TD]authcode[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]surname[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]678[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]888[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]brown[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jones[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]smith[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]a pivot table marks which codes are assigned to each person[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]it also tells you how many of each code are in your basic data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

if all data were combined you could analyse by company, by authority code, by date of authority code.........

 

[scleworthenwl]

I am copying and pasting all of the information from the nine spreadsheets into the tenth now.

I'll need to order them by staff number, is there a way to sort them whilst keeping the rows intact?

 

[AlanY]

you can put a filter in row1, be select the row, goto data > filter.
then you can use the drop down menu on row1 for the column and sort them accordingly

 

[oldbrewer]

when you sort by one column all the rows adjust accordingly

make a little spreadsheet and have a play...