ceneiqe
asked on
Macro to replace old codes with new codes
Old codes:
Code1
Code2
New codes:
Code1.1
Code2.1
If combination of Code1 & Code2, then replace by Code1.1 & Code2.1.
New codes should be at Column L and M respectively.
See attached.
Code-Replacements.xlsx
Code1
Code2
New codes:
Code1.1
Code2.1
If combination of Code1 & Code2, then replace by Code1.1 & Code2.1.
New codes should be at Column L and M respectively.
See attached.
Code-Replacements.xlsx
I don't understand the question.
Agree totally unclear
gowflow
gowflow
Yes, even with the attached spreadsheet, I too am confused.
Can you simply post only what you have currently.
Then post what you need the result to be
Then explain, clearly and precisely, how you got from what you have, to what you want.
Can you simply post only what you have currently.
Then post what you need the result to be
Then explain, clearly and precisely, how you got from what you have, to what you want.
ASKER
Sorry you did not explain anything it is still totally unclear !!
how do you go from Code 1 and Code 2 TO Code 1.1 and Code 2.1 there is no logic in the values can you explain by taking an example for us to understand.
gowflow
how do you go from Code 1 and Code 2 TO Code 1.1 and Code 2.1 there is no logic in the values can you explain by taking an example for us to understand.
gowflow
I can see no logical process for getting the ".1" values either...
In the first row Code 1 is 1 and code 2 is 5.
If you concatenate them (=P2&Q2) you get 15 (the value in Code 1.1)
...but I can't see any other rational explanations for the other ".1" values...?
Are you sure you have been given the correct datasets?
Can you contact the source of this data and see if they have an explanation?
In the first row Code 1 is 1 and code 2 is 5.
If you concatenate them (=P2&Q2) you get 15 (the value in Code 1.1)
...but I can't see any other rational explanations for the other ".1" values...?
Are you sure you have been given the correct datasets?
Can you contact the source of this data and see if they have an explanation?
@Jeffrey
I think it is not the first nor the last mystery for OP am hanged on an other question that is no different despite numerous question marks seems we are in a deadlock !!!
:)
gowflow
I think it is not the first nor the last mystery for OP am hanged on an other question that is no different despite numerous question marks seems we are in a deadlock !!!
:)
gowflow
OK,
Let us know how you would like to proceed.
Let us know how you would like to proceed.
ASKER
sorry there seems to be some misunderstanding.
There is absolutely no logic in the values.
As long as in code1 and code2 is 1 and 5, then it will change to 15 and 104.
As long as code1 and code2 is 1 and 6, then it will change to 15 and 82.
There is absolutely no logic in the values.
As long as in code1 and code2 is 1 and 5, then it will change to 15 and 104.
As long as code1 and code2 is 1 and 6, then it will change to 15 and 82.
ASKER
The code1.1 and Code2.1 are new codes to replace the old codes, so everything in old has to map to new.
but instead of replacing directly into column E and F, just place it in column L and M so that I can see the change.
hope this is clear.
but instead of replacing directly into column E and F, just place it in column L and M so that I can see the change.
hope this is clear.
Sorry, we are all still lost so lets keep this simple...
You are giver some values
Code1 Code2
1 5
1 5
1 5
1 5
3 5
3 5
3 5
3 4
3 4
3 17
3 18
3 9
3 16
185 14
186 24
186 25
183 105
Then you are looking for a formula that will convert those values to these values:
Code1.1 Code2.1
15 104
15 104
15 104
15 104
28 104
28 104
28 104
28 169
28 169
28 194
28 65
28 87
28 198
28 113
NA NA
NA NA
255 121
...is this correct?
You are giver some values
Code1 Code2
1 5
1 5
1 5
1 5
3 5
3 5
3 5
3 4
3 4
3 17
3 18
3 9
3 16
185 14
186 24
186 25
183 105
Then you are looking for a formula that will convert those values to these values:
Code1.1 Code2.1
15 104
15 104
15 104
15 104
28 104
28 104
28 104
28 169
28 169
28 194
28 65
28 87
28 198
28 113
NA NA
NA NA
255 121
...is this correct?
ok here you are finally got what you want here is the code for that
In the attached workbook I created a sheet called Mapping pls respect the was columns are and do not change them. Like original in A,B and Mapped in E,F
When oyu activate the macro it will look for data in sheet Original Data and do a mapping and give you the results in a new sheet that it will create called: Desired.
pls chk and let me know.
the workbook has everything.
gowflow
Code-Replacements--Details-V01.xlsm
Sub MapData()
Dim WSD As Worksheet
Dim WSM As Worksheet
Dim WSO As Worksheet
Dim MaxRowM As Long, MaxRowO As Long, MaxColO As Long, I As Long
Dim sSearch As String
Dim cCell As Range
'---> Disable Events
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
'---> Set Variables
Set WSM = Sheets("Mapping")
MaxRowM = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row
Set WSO = Sheets("Original data")
MaxRowO = WSO.Range("A" & WSO.Rows.Count).End(xlUp).Row
MaxColO = WSO.Columns(WSO.Columns.Count).End(xlToLeft).Column
On Error Resume Next
Set WSD = Sheets("Desired")
If Err <> 0 Then
Set WSD = Worksheets.Add(after:=Worksheets(Worksheets.Count))
WSD.Name = "Desired"
End If
On Error GoTo 0
WSD.Cells.Delete
'---> Create Col C as Concatenate A+B / G as E+F
WSM.Range("C2:C" & MaxRowM).Formula = "=CONCATENATE(A2,B2)"
WSM.Range("G2:G" & MaxRowM).Formula = "=CONCATENATE(E2,F2)"
'---> Loop thru Original
WSO.Range(WSO.Range("A1"), WSO.Cells(MaxRowO, MaxColO)).Copy WSD.Range("A1")
For I = 2 To MaxRowO
sSearch = WSD.Range("E" & I) & WSD.Range("F" & I)
Set cCell = WSM.Range("C1:C" & MaxRowM).Find(what:=sSearch, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
WSD.Range("L" & I) = WSM.Cells(cCell.Row, "E")
WSD.Range("M" & I) = WSM.Cells(cCell.Row, "F")
End If
Next I
WSM.Range("C:C").ClearContents
WSM.Range("G:G").ClearContents
'---> Enable Events
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With
MsgBox "Mapping Done !", vbExclamation
End Sub
In the attached workbook I created a sheet called Mapping pls respect the was columns are and do not change them. Like original in A,B and Mapped in E,F
When oyu activate the macro it will look for data in sheet Original Data and do a mapping and give you the results in a new sheet that it will create called: Desired.
pls chk and let me know.
the workbook has everything.
gowflow
Code-Replacements--Details-V01.xlsm
I'll look at this tonight unless another Expert chimes in.
ASKER
in response to message in ID: 40598725 >> Yes Jeffery, you are right.
in response to message in ID: 40599346 >> thks gowflow.
it works.
But could you tweak the macro such that:
1. row 1 also has the corresponding data in column L and M as my original data does not have headers so the data starts from row 1.
2. the macro works even without the worksheet "Mapping" and also without renaming the worksheet that contains the data to "Original Data"
because i retrieve the data from text file every month and i have to convert the text file into excel and then map the old codes to new codes.
If 1. and 2. can be done, then i can place the macro in personal.xlsb and run it every month.
Data--from-text-.xlsx
Data (from text).xlsx - shows the result when running your macro - worksheet "Desired"
Data--from-text--1.xlsx
Data (from text) 1.xlsx - shows my data that is originally derived from text and save as xlsx.
in response to message in ID: 40599346 >> thks gowflow.
it works.
But could you tweak the macro such that:
1. row 1 also has the corresponding data in column L and M as my original data does not have headers so the data starts from row 1.
2. the macro works even without the worksheet "Mapping" and also without renaming the worksheet that contains the data to "Original Data"
because i retrieve the data from text file every month and i have to convert the text file into excel and then map the old codes to new codes.
If 1. and 2. can be done, then i can place the macro in personal.xlsb and run it every month.
Data--from-text-.xlsx
Data (from text).xlsx - shows the result when running your macro - worksheet "Desired"
Data--from-text--1.xlsx
Data (from text) 1.xlsx - shows my data that is originally derived from text and save as xlsx.
So...
Problem solved?
Problem solved?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok i have raised separate question in :
https://www.experts-exchange.com/questions/28616285/Macro-to-replace-old-codes-with-new-codes-II.html
https://www.experts-exchange.com/questions/28616285/Macro-to-replace-old-codes-with-new-codes-II.html
ASKER
See extension of question in :
https://www.experts-exchange.com/questions/28616285/Macro-to-replace-old-codes-with-new-codes-II.html
https://www.experts-exchange.com/questions/28616285/Macro-to-replace-old-codes-with-new-codes-II.html