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]

	the table below could be from, say, 3 different sheets
	surname	forename	staffnum	comp	authcode	authexp
	smith	tom	123	compA	234	01/10/2015
	jones	fred	234	compB	345	09/10/2015
	brown	andy	345	compA	456	17/10/2015
	smith	tom	123	compA	345	25/10/2015
	jones	fred	234	compB	234	02/11/2015
	brown	andy	345	compA	567	10/11/2015
	smith	tom	123	compA	678	18/11/2015
	jones	fred	234	compB	789	26/11/2015
	brown	andy	345	compA	888	04/12/2015
	Count of authcode	authcode
	surname	234	345	456	567	678	789	888	Grand Total
	brown			1	1			1	3
	jones	1	1				1		3
	smith	1	1			1			3
	Grand Total	2	2	1	1	1	1	1	9
	a pivot table marks which codes are assigned to each person
	it also tells you how many of each code are in your basic data

<colgroup><col><col><col span="7"><col><col></colgroup><tbody>
</tbody>

 

[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...