Help needed replacing multiple cells from a list of values.

[Emoshag]

Hello,

I need to replace the contents of about 90k cells across 14 worksheets. The problem is that it is a very, very large set of data and it is in multiple columns.

Example:

8 of the tables will have a primary key column (this was originally an Oracle export). The other tables have a foreign key column. Now.... I need to replace all the occurrences of each primary key with a new value.

If this is a snippet of a table:

PK_COL NAME AGE
IK104T Dave 30
M10TY Paul 61
P7G34I Carl 42

Then what I want to do is replace all occurrences of IK104T, M10TY and P7G34I with 1, 2, and 3. Then find ALL occurences of IK104T, M10TY and P7G34I across the other worksheets and replace them with 1, 2, 3 (the smae values I did the original replace with).

I originally thought there would be a way if I copied every single KEY (every column these numbers might show up) and put it in a separate table in Column A. Then, in that new table in Column B, put incrementing numbers (1,2,3,4,5,etc.). Then use a function of some sort to execute a command like:

"Find value equal to Column A"
"Replace Column A with Column B"
"Find next value equal to Column A"
"Replace Column A with Column B"
"If no more Column A.... next"

Does this make sense? I have been doing this manually for a long time and this is just too much for the time alloted to my project.

Any ideas?

[Dave Peterson]

You have a response to your thread in .misc.

Emoshag wrote:
>
> Hello,
>
> I need to replace the contents of about 90k cells across 14 worksheets.
> The problem is that it is a very, very large set of data and it is in
> multiple columns.
>
> Example:
>
> 8 of the tables will have a primary key column (this was originally an
> Oracle export). The other tables have a foreign key column. Now.... I
> need to replace all the occurrences of each primary key with a new
> value.
>
> If this is a snippet of a table:
>
> PK_COL NAME AGE
> IK104T Dave 30
> M10TY Paul 61
> P7G34I Carl 42
>
> Then what I want to do is replace all occurrences of IK104T, M10TY and
> P7G34I with 1, 2, and 3. Then find ALL occurences of IK104T, M10TY and
> P7G34I across the other worksheets and replace them with 1, 2, 3 (the
> smae values I did the original replace with).
>
> I originally thought there would be a way if I copied every single KEY
> (every column these numbers might show up) and put it in a separate
> table in Column A. Then, in that new table in Column B, put
> incrementing numbers (1,2,3,4,5,etc.). Then use a function of some sort
> to execute a command like:
>
> "Find value equal to Column A"
> "Replace Column A with Column B"
> "Find next value equal to Column A"
> "Replace Column A with Column B"
> "If no more Column A.... next"
>
> Does this make sense? I have been doing this manually for a long time
> and this is just too much for the time alloted to my project.
>
> Any ideas?
>
> --
> Emoshag
> ------------------------------------------------------------------------
> Emoshag's Profile: http://www.excelforum.com/member.php...o&userid=22190
> View this thread: http://www.excelforum.com/showthread...hreadid=558959

--

Dave Peterson