Macro to replace numbers with specific text value
Hi, All!
Everyday I get an excel sheet with 400k records that has repetative numbers from 1 to 300 in 1st column. I have list (300) of text values, and the number in 1st column needs to be replaced by its specific text value.
For ex: if number=1 then replace it with "Apple", If number=2 then replace it with "boy".............If number=300 then replace it with "ZebraZebraZebra...."
Would someone help me to write a macro to do this.
Appreciate your help.
Thank you!
Shayk
Hello Shayk,
Welcome to the Forum!
I could be possible without VBA Code, but it would be much easier if you could attach a sample workbook, for us to see where this list (300) of text values is.
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.
Regards.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Please Login or Register to view this content.
To do it for me and help me it is 2 different things!
Sorry for my english, blame Google translator
Spreadsheet attached.
Hello Shayk,
There are no values Assigned to the Names on the "Value Assigned" Sheet.
Could you please do that for us?
Thank you.
The 1st column of sheet1 has ID's that needs to be replaced. The "value assigned" sheet has number in column1 and the text value for that number in column 2.
If the value in column1, sheet1 = 1 then replace it with the value which is in column2, sheet2.
In the spreadsheet if you go to A9, sheet1 = 176 then it should be replaced by A177,Value Assigned which is text value "xavier"
Hope this helps. Please let me know.
Thank you!.
Shayk.
Hello Kalbasiatka,
Your macro worked but it is replacing all the other columns which has numbers in it. I want the values replaced only in 1 column or 5 column. Would you please correct it so that it does not replace values in other columns.
Thanks,
Shayk
Hello Shayk,
Thank you for the Clarification.
If you Replace the Number in Column A in Sheet1 with the name on Sheet "Valued Assigned" you will lose the ability to verify if the Data presented is correct. Would it be O.K. with you if we return the desired results in, say Column C of Sheet1 instead of Column A?
Last edited by Winon; 11-02-2016 at 04:38 PM. Reason: Spelling
Yes, that's perfectly fine.
Thank you!.
Shayk
Hello Shayk,
Thank you for the feedback.
In C2 of Sheet1 enter the formula below, and drag it down as far as required.
Formula:
Please Login or Register to view this content.
Enjoy!
Kind Regards.
Hi Winon,
As I said I have 400k records and sometimes more than that. Dragging will be very difficult. Is there a possibility to create a macro out of it.
Appreciate your help.
Thanks,
Shayk
Here you go Shayk
Place the Code below in a Standard Module. You may link it to a Button of your choice!
Regards,Please Login or Register to view this content.
W.
Awesome!!!!!!!!!! You made my day
Kind Regards,
Shayk
Try
Please Login or Register to view this content.
Hello Shayk,
Thank you for the Rep, Much appreciate!
The Code Below is even better:
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.Please Login or Register to view this content.
Thanks.
Hi Winon,
I appreciate your help. I need one more help to execute the code. When I'm executing the macro for 50k records in excel it runs fine without issues but when i run the same for 1048587 records it gives 400 error.
Do you know what is causing this?
Appreciate your help in this matter.
Thank
Hello Shayk,
That happens because a Column has only 1048576 Rows. If your actual records start on, say Row 5 of a Column, the maximum number of rows you could work with records would become 1048576 - 5 Rows.when i run the same for 1048587 records it gives 400 error.
Leaving you with only 1048571 Rows of Records which is still more than the maximum any column could handle.
With the above in mind it tells me that your actual number of Records Flow over into other adjacent columns, whilst our solution looks at only one Column.
To best assist us in finding a solution for you with this scenario, it would be best if you could attach a sample workbook, showing us which Columns are at play here. You don't need to fill the Columns right down to the bottom of any Column, 50 to 100 Rows for each Column will suffice. It would even be better if you could leave a "Blank" Column between each two Columns which might be containing you Records.
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.
Regards.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks