Need to do a SQL-like functions within Excel. Need HELP!
 

I have an Excel 2003 worksheet in which Column A contains Employee IDs
(ie 00123), Column D contains UserIDs, and Column G contains the
employee's Manager's Employee ID.

An employees Manager ID in Column G will appear later on in the
spreadsheet as its own employee record (Employee ID in Column A).

EXAMPLE:

ROW 1: Column A contains EmployeeID: 00012. Column D contains UserID:
rgrubin. Column G contains Manager ID: 00078.


ROW 670: Column A contains EmployeeID: 00078. Column D contains
UserID: jsmith. Column G contains Manager ID:00212

In this case rgrubin's manager is jsmith and jsmith's employee record
appears in Row 670.

What I need to do is in another column insert a employee's manager's
userid. So, in Row 1, I need to have a column that contains jsmith
which is rgrubin's manager's id. So, I am putting the UserID from Row
670 into a column in Row 1 and I need to do this in some sort of
formula or query since the spreadsheet has about 3000 rows and there
is no way I am going to sit there and manually match up employee IDs
with user IDs.

I don't want to have to bring the data into Access or SQL Server,
because this is a regular data dump from a PeopleSoft system in which
we have to convert the Excel to CSV which then gets converted to XML
for importing into another system.

Is there ANY way to do this directly within Excel?

Need to do a SQL-like functions within Excel. Need HELP!
 

I have an Excel 2003 worksheet in which Column A contains Employee IDs
(ie 00123), Column D contains UserIDs, and Column G contains the
employee's Manager's Employee ID.

An employees Manager ID in Column G will appear later on in the
spreadsheet as its own employee record (Employee ID in Column A).

EXAMPLE:

ROW 1: Column A contains EmployeeID: 00012. Column D contains UserID:
rgrubin. Column G contains Manager ID: 00078.


ROW 670: Column A contains EmployeeID: 00078. Column D contains
UserID: jsmith. Column G contains Manager ID:00212

In this case rgrubin's manager is jsmith and jsmith's employee record
appears in Row 670.

What I need to do is in another column insert a employee's manager's
userid. So, in Row 1, I need to have a column that contains jsmith
which is rgrubin's manager's id. So, I am putting the UserID from Row
670 into a column in Row 1 and I need to do this in some sort of
formula or query since the spreadsheet has about 3000 rows and there
is no way I am going to sit there and manually match up employee IDs
with user IDs.

I don't want to have to bring the data into Access or SQL Server,
because this is a regular data dump from a PeopleSoft system in which
we have to convert the Excel to CSV which then gets converted to XML
for importing into another system.

Is there ANY way to do this directly within Excel?

This may not be pretty, but it works (at least for a small number of rows!!)

Sub copyManagerUserID()
Dim strManID
Dim strFoundUserID
For c = 2 To 25
strManID = Cells(c, 7)
Range("A:A").Select
Selection.Find(What:=strManID, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
strFoundUserID = ActiveCell.Offset(0, 3)
Cells(c, 10) = strFoundUserID
Next c
Range("A1").Select
End Sub