Mapping Data? There must be a way of doing this quickly... But I have no idea how!

[ggggggina]

I have a set of data for an old system we used to use and I need to put this data into my new system with the new wording. Imagine it is something like this

Old Code New Code
Apple Red Apples R
Apple Green Apples G
Orange standard Oranges N/A
Grape Red Grapes R
Grape Green Grapes G

I need a way of quickly changing all the old codes to the new codes in my master document... Is there a simple way of doing this for multiple columns?

My second question is how I can potentially manage this if I have more or less data in one of the sets... see below... (Although I might be able to put some leg work in to get the numbers somewhat match.)
Old Code New Code
Apple Red Apples
Apple Green Apples
Orange standard Oranges
Grape Red Grapes
Grape Green Pineapples

Any help here greatly appreciated!!!!

[JohnTopley]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)


And when you have changed the title please post a sample file

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ggggggina]

If I knew how to more accurately describe this issue then I would have googled it already... Can you help with suggested wording based on my issue?

Thanks

[AliGW]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification.

All participants:
Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

[ggggggina]

You actually deleted their response also? How are you supposed to run a helpful forum where you refuse to help? I clearly requested help from you and rather than suggest a response you delete the only person who tried to actually help? You spiteful *****

[shg]

Careful; people who post things that appear as ***** risk getting thrown out on their asterisks.

[AliGW]

The asterisks in the other thread title were added by me to replace foul language, which will not be tolerated.

You need to abide by the forum rules, just like everyone else.

Your thread title needs to explain the problem you wish to solve. Once you have changed it, you may receive help.

[ggggggina]

You would need to separate the Old Code and New Code values into separate columns in a 'Key' sheet.

Adjust the sheet names, and range, as needed


Sub Test()

Dim StartRow As LongPtr _
, LastRowKey As LongPtr _
, LastRowNewData As LongPtr _
, OldCode As String _
, NewCode As String

LastRowKey = Sheets("Key").Cells(Rows.Count, "A").End(Excel.xlUp).Row
LastRowNewData = Sheets("NewSystem").Cells(Rows.Count, "A").End(Excel.xlUp).Row

If LastRowKey > 1 And LastRowNewData > 1 Then

For StartRow = 2 To LastRowKey

OldCode = Sheets("Key").Range("A" & StartRow & "")
NewCode = Sheets("Key").Range("B" & StartRow & "")

For Each Cell In Sheets("NewSystem").Range("A2:B" & LastRowNewData & "")

If InStr(Cell, OldCode) > 0 Then

Cell.Value = Replace(Cell, OldCode, NewCode)

End If

Next

OldCode = ""
NewCode = ""

Next

End If

End Sub

[jeffreybrown]

Sure, I'll jump in...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[ggggggina]

I don't even know what code is... I was copying and pasting the previously deleted post in case this helped anyone else... I still have no idea what will help me... maybe one day a moderator without a rod up their **** will help?

[ggggggina]

The asterisks in the other thread title were added by me to replace foul language, which will not be tolerated.

You need to abide by the forum rules, just like everyone else.

Your thread title needs to explain the problem you wish to solve. Once you have changed it, you may receive help.

How is "Mapping data" not the problem? I want to map data? If you want it called something else, maybe you should suggest something

[AliGW]

I would if I had any idea what you meant.

You are lucky that you have not been banned, at least temporarily. I suggest you calm down, take a moment to read the forum rules and then calmly explain what you are trying to achieve.

[ggggggina]

I would if I had any idea what you meant.

You are lucky that you have not been banned, at least temporarily. I suggest you calm down, take a moment to read the forum rules and then calmly explain what you are trying to achieve.

If you read my original post I had hoped it explained that already? I have a set of data I need to map to new wording for the system to read it? Maybe if you read my post you would see it isn't as simple as putting that in a sentence in the title?

[ggggggina2]

......I don't actually care if you ban me because you're the least helpful group of people I have ever come across. I couldn't add my example in a table because it started bringing up all this code stuff... I'm obviously not tech savvy or I wouldn't need to come on a forum to ask for help, and you insult me even further by picking up on the language I use to explain my problem because I don't know exactly how to articulate it in a sentence. You're all just rude and unhelpful and I'm really disappointed. I thought these things would be full of helpful people with good ideas on how to help people struggling who aren't as good at using technology as others... Now I see it is just a power trip for you all sitting there just waiting eagerly to press your regulation buttons. It's sad really. Enjoy your forum.

[jeffreybrown]

Thread closed.