Solved

Macro to replace old codes with new codes

Posted on 2015-02-04
18
53 Views
Last Modified: 2015-02-12
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
0
Comment
Question by:ceneiqe
  • 6
  • 6
  • 5
  • +1
18 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40589044
I don't understand the question.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40589136
Agree totally unclear

gowflow
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40591393
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.
0
 

Author Comment

by:ceneiqe
ID: 40594236
see worksheets
"Original data"
"desired results"

Thks

Code-Replacements--Details-.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40594325
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40594362
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?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40595524
@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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40596928
OK,

Let us know how you would like to proceed.
0
 

Author Comment

by:ceneiqe
ID: 40597703
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ceneiqe
ID: 40597706
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40598725
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?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40599346
ok here you are finally got what you want here is the code for that

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

Open in new window



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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40599383
I'll look at this tonight unless another Expert chimes in.
0
 

Author Comment

by:ceneiqe
ID: 40602312
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40602366
So...
Problem solved?
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40602548
re your questions:

You mean to say the mapping is fixed and always like the one you showed ? if yes, then yes we can do what you want pls check this version.

I kept the sheet Mapping as hidden and hv 2 sheets there Data and Original just select the sheet that you want and run the macro and it will update that sheet then try the other one etc..

Will worry separately how to incorporate in your .xlsb which should be a separate question.
Pls let me know.
gowlfow
Code-Replacements--Details-V02.xlsm
0
 

Author Comment

by:ceneiqe
ID: 40607267
0
 

Author Closing Comment

by:ceneiqe
ID: 40607269
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now