Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
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=558894 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
Have I missed something but can you not simply use the Edit=Find/Replace
function to replace these values? You can replace all within a workbook, rather than doing individual sheets. "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=558894 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
Yes, you missed the part that there are a tad over 80,000 cells than need to be replaced and I am working with about 3 days to do this. :) I have done that before when the data was smaller and less convoluted.... but this is just a giant mess of values and tables. I remember reading in a book I have lying around somewhere that there was a way to do this via a script.... but I can't find the book now. I think I lent it to a co-worker.... -- Emoshag ------------------------------------------------------------------------ Emoshag's Profile: http://www.excelforum.com/member.php...o&userid=22190 View this thread: http://www.excelforum.com/showthread...hreadid=558894 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
I am assuming that I will need some VB in this solution.... so I will also try posting this in that forum.... -- Emoshag ------------------------------------------------------------------------ Emoshag's Profile: http://www.excelforum.com/member.php...o&userid=22190 View this thread: http://www.excelforum.com/showthread...hreadid=558894 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
If you only have 3 keys to replace, you can group your sheets (click on the
first worksheet tab and ctrl-click on subsequent), then select the columns to change. Then edit|replace (3 times!) and ungroup the sheets and you're done. In code, you could do something like: Option Explicit Sub testme() Dim myAddrToChange As String Dim wks As Worksheet Dim MstrWks As Worksheet Dim KeyRng As Range Dim myCell As Range If ActiveWindow.SelectedSheets.Count = 1 Then MsgBox "Please select multiple worksheets!" Exit Sub End If myAddrToChange = "A:A,C:C,E:G,Z:Z" Set MstrWks = Worksheets("sheet999") With MstrWks Set KeyRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each wks In ActiveWindow.SelectedSheets For Each myCell In KeyRng.Cells With wks.Range(myAddrToChange) .Cells.Replace what:=myCell.Value, _ replacement:=myCell.Offset(0, 1).Value, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ MatchCase:=False End With Next myCell Next wks ActiveWindow.SelectedSheets(1).Select True End Sub 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=558894 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
With Find/Replace, the amount of work for you to replace 3 sets of values
should be the same whether it's 8 cells or 80,000 cells. If you're talking about the number of different combinations of values you need to replace, then yes it takes a lot of work to do Find/Replace for each of many different values. I'm guessing it's the latter case where you have a long list of unique primary key values that you need to replace with 1, 2, 3,... all the way to the number of unique values you have. What you can do is make a list of unique values on a separate sheet, and put the corresponding 1, 2, 3 values in the next column. Then on each of your 14 sheets, insert a column and use VLOOKUP to get the corresponding 1, 2, 3 values and copy formula down. You'll have to do this 14 times, but better than doing 100+ times of Find/Replace (assuming you have about 100 unique primary key values or more). -Simon "Emoshag" wrote: Yes, you missed the part that there are a tad over 80,000 cells than need to be replaced and I am working with about 3 days to do this. :) I have done that before when the data was smaller and less convoluted.... but this is just a giant mess of values and tables. I remember reading in a book I have lying around somewhere that there was a way to do this via a script.... but I can't find the book now. I think I lent it to a co-worker.... -- Emoshag ------------------------------------------------------------------------ Emoshag's Profile: http://www.excelforum.com/member.php...o&userid=22190 View this thread: http://www.excelforum.com/showthread...hreadid=558894 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help needed replacing multiple cells from a list of values.
Holy crap that is what I was hoping to figure out. I will let you know if it works. In any case, thank you.... very, very much! :) -- Emoshag ------------------------------------------------------------------------ Emoshag's Profile: http://www.excelforum.com/member.php...o&userid=22190 View this thread: http://www.excelforum.com/showthread...hreadid=558894 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offsetting values of multiple cells! | Excel Worksheet Functions | |||
Replacing sheet reference in multiple cells... | Excel Worksheet Functions | |||
percenatge increase in values across multiple cells | New Users to Excel | |||
Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) | |||
If formula that looks at multiple cells and values, and then calul | Excel Discussion (Misc queries) |