Excel data manipulation

[mistercb]

OK.. Sorry in advance, I'm very new to VB (I understand basic concepts) but I need a little help here. Long story short, I'm migrating some data from one excel file to another, but there are some data structure changes. I know it seems as if I'm using Excel as a database, but right now it's just an easy medium to manipulate data in until it gets imported into the real database. My only problem is that I need to automate specific parts. Right now I'm using VBA within Excel to basically build some simple macros to move data around, but I've run into a situation I'm not sure how to code.

Scenario is this: source table has an unspecified number of rows, one column has name of a person, another column has an item ID # that they are associated with.

Destination table needs to have the item ID # in one column, but in place of the name, the destination table needs to have a user ID.

I have this third reference table that has the names tied to user ID's.

So basically, I need to go through each record in the source table, copy it over to the destination, then look up the name and replace it with the appropriate user ID from the reference table.. I'm think it's a pretty simple thing to do, and I've seen examples for SQL, but I can't really get my head around how to do it in VBA right now.

Any help?

[ggeu]

dear,

The fastest way is to work with 2 array: don't jump to the sheets 1 - 2 - 3 - 1 - 2 -.....
We use=
one with the 2e sheet data = username - userID
and one with the results = ItemID - UserID

how it works:
Load the data of the 2e sheet in the array.

Loop the data in the 1e sheet and check with the array from the 2e sheet = write the results in the array for the 3e sheet.

Dump the 2e array in the 3e sheet.

This is the code=

###########################################
Option Explicit

Type Name_UserID
Name As String
UserID As String
End Type

Type ItemID_UserID
ItemID As String
UserID As String
End Type


Sub Macro1()
Dim ArrayNameUserID() As Name_UserID
Dim ArrayItemIDUserID() As ItemID_UserID
Dim i As Integer
'§ FILL ARRAY ArrayNameUserID
'§ ==========================
Sheet2.Activate
Range("a2").Activate
'§ fill the 1e record
ReDim ArrayNameUserID(0)
ArrayNameUserID(UBound(ArrayNameUserID)).Name = ActiveCell.Value
ArrayNameUserID(UBound(ArrayNameUserID)).UserID = ActiveCell.Offset(0, 1).Value
'§ go to the next record
ActiveCell.Offset(1, 0).Activate
'§ load all other records
Do While ActiveCell.Value <> ""
ReDim Preserve ArrayNameUserID(UBound(ArrayNameUserID) + 1)
ArrayNameUserID(UBound(ArrayNameUserID)).Name = ActiveCell.Value
ArrayNameUserID(UBound(ArrayNameUserID)).UserID = ActiveCell.Offset(0, 1).Value
'§ go to the next record
ActiveCell.Offset(1, 0).Activate
Loop
'§ check all records and FILL ARRAY ArrayItemIDUserID
'§ ==================================================
Sheet1.Activate
Range("a2").Activate
'§ fill the 1e record
ReDim ArrayItemIDUserID(0)
ArrayItemIDUserID(UBound(ArrayItemIDUserID)).ItemID = ActiveCell.Offset(0, 1).Value
'§ find the userID
For i = LBound(ArrayNameUserID) To UBound(ArrayNameUserID)
If ActiveCell.Value = ArrayNameUserID(i).Name Then _
ArrayItemIDUserID(UBound(ArrayItemIDUserID)).UserID = ArrayNameUserID(i).UserID
Next
'§ go to the next record
ActiveCell.Offset(1, 0).Activate
'§ load all other records
Do While ActiveCell.Value <> ""
ReDim Preserve ArrayItemIDUserID(UBound(ArrayItemIDUserID) + 1)
ArrayItemIDUserID(UBound(ArrayItemIDUserID)).ItemID = ActiveCell.Offset(0, 1).Value
'§ find the userID
For i = LBound(ArrayNameUserID) To UBound(ArrayNameUserID)
If ActiveCell.Value = ArrayNameUserID(i).Name Then _
ArrayItemIDUserID(UBound(ArrayItemIDUserID)).UserID = ArrayNameUserID(i).UserID
Next
'§ go to the next record
ActiveCell.Offset(1, 0).Activate
Loop
'§ dump the results from ARRAY ArrayItemIDUserID
'§ =============================================
Sheet3.Activate
Range("a2").Activate
For i = LBound(ArrayItemIDUserID) To UBound(ArrayItemIDUserID)
ActiveCell.Value = ArrayItemIDUserID(i).ItemID
ActiveCell.Offset(0, 1).Value = ArrayItemIDUserID(i).UserID
ActiveCell.Offset(1, 0).Activate
Next
End Sub

###########################################


See also the attachment.

br,

[dglienna]

Who can READ that? Please go back and add CODE TAGS to that mess!