I need help writing a macro to replace cell contents. (new to VB)
 

Hello,

I am building an Oracle database and I am going to be using Excel to injest
the data. I am trying to give unique values to records and to do so I have
been hand copying rows between different worksheets and pasting the same GUID
(unique identifier) on matching column values between different worksheets.

I am desparately trying to find a time saving function or macro because this
task is taking way too long now that I am getting +50,000 records.

Is there a way to create a unique ID for a cell and then copy and paste
that ID into a cell in a specific row on another worksheet where a cell in
the target row is identical to a cell from the row where the ID is being
copied from?

I am desparate at this point, so if anyone has any ideas or needs more
information, I would be more thatn happy to provide it.

I need help writing a macro to replace cell contents. (new to
 

I suggest
1) collect all the unique ID's
2) build your new table

1)
We're going to use a collection, more specifically a dictionary object, to
use the .Exists method to test if keys exist.
In the IDE, set a refence to Microsoft Scripting Runtime
in a module, paste
Option Explicit
Private dGUIDs As Scripting.Dictionary
Sub GetIDs()
Dim wsheets() As Variant
Dim ws As Worksheet
Dim wsIndex As Long
Dim cell As Range
Dim key As String
wsheets = Array("sheet1", "sheet2")
Set dGUIDs = New Scripting.Dictionary

For wsIndex = 0 To UBound(wsheets, 1)
Set ws = Worksheets(wsheets(wsIndex))
With ws
For Each cell In .Range(.Range("A1"), .Range("A1").End(xlDown))

key = cell.Value
If key < "" Then
If Not dGUIDs.Exists(key) Then
dGUIDs.Add key, key
End If
End If

Next
Next
End Sub

2) ? you haven't specified how records on more than one sheet get merged. I
assume in (1) that column A had the GUIDs. Apart from A then, do the columns
dffer on other woksheets?
This step should output the ID's into A on a new sheet, then for each of
your worksheets copy rows to the relevent columns of the output sheet. so
sheet1 may be columns B- G, sheet2 columns H-M and so on?
does this sound right....I 'll spend the time writing the code when this is
clearer.



"Patrick Molloy" wrote:

if you use MS Access, this becomes trivial. open a new Access database, link
a table to each of the sheets, open a query, add all the sheets & seelct
everything, using the GUID as the key for all sheets. The resultant results
can be resaved as a table and then you can suck it straight into your main
database.



"Emoshag" wrote:

Hello,

I am building an Oracle database and I am going to be using Excel to injest
the data. I am trying to give unique values to records and to do so I have
been hand copying rows between different worksheets and pasting the same GUID
(unique identifier) on matching column values between different worksheets.

I am desparately trying to find a time saving function or macro because this
task is taking way too long now that I am getting +50,000 records.

Is there a way to create a unique ID for a cell and then copy and paste
that ID into a cell in a specific row on another worksheet where a cell in
the target row is identical to a cell from the row where the ID is being
copied from?

I am desparate at this point, so if anyone has any ideas or needs more
information, I would be more thatn happy to provide it.

I need help writing a macro to replace cell contents. (new to
 

See, thats the problem. I am using Access for the first time. I have never
done any sort of database work (audio engineer) and as one of my co-workers
has developed the schema for this Oracle database, I am just in charge of
populating it.

When you say link a table to each of the sheets, what do you mean? I have
figured out how to export the Oracle schema into both Access and Excel
worksheets, but I do not understand "linking them."

Here is a very rough idea of what I imagine the macro will resemble:

-----------------------------------------------------

Begin Loop
In worksheet A
Variable A = Current Row, Cell in Column A
Variable B = Current Row, Cell in Column B

For Worksheet B, C, D, E, F
Row in Current Worksheet = 1

Begin Loop

If Cell in Column A of Current Row in Current Worksheet = Variable A
Replace Cell in Column B of Current Row with Variable B
Next Row until End of File
End Loop

Next Row in Worksheet A until End of File

End Loop

I hope this clarifies my problem and not convolutes it.




"Patrick Molloy" wrote:

if you use MS Access, this becomes trivial. open a new Access database, link
a table to each of the sheets, open a query, add all the sheets & seelct
everything, using the GUID as the key for all sheets. The resultant results
can be resaved as a table and then you can suck it straight into your main
database.



"Emoshag" wrote:

Hello,

I am building an Oracle database and I am going to be using Excel to injest
the data. I am trying to give unique values to records and to do so I have
been hand copying rows between different worksheets and pasting the same GUID
(unique identifier) on matching column values between different worksheets.

I am desparately trying to find a time saving function or macro because this
task is taking way too long now that I am getting +50,000 records.

Is there a way to create a unique ID for a cell and then copy and paste
that ID into a cell in a specific row on another worksheet where a cell in
the target row is identical to a cell from the row where the ID is being
copied from?

I am desparate at this point, so if anyone has any ideas or needs more
information, I would be more thatn happy to provide it.