Forum Discussion
Excel Data Import with custom formating rules
Yea_SoHello Below is the attached workbook, so i want to import data FROM sheet2 in to Sheet1 using some recorded Macro to automate the manual copy-paste and typing abbreviations such as "D" for Decrease and "1" for True and "0" for False. Thanks in advance for any help or ideas or suggestions given in this regard .
Hi nidospartan
Here is the edits I did to your workbook:
Your sample data:
Converted data using excel formulas:
Data moved Destination Form using UNIQUE Funtion:
All you have to do is copy and paste to the Source Data Sheet, and it will automatically convert the data based on the Look up Sheet which you can edit/add they're dynamic tables so the range will autoadjust when you add items:
Let me know if you have any questions
Yea_So Thank you big time for the help, but issue is the data in source sheet comes from a web form and then it is copy pasted for few columns every time a form arrives. Then manually typed based on values in Source Sheet and converted to Match the validation rules in Destination sheet to further push in to sql db. So i want to set rules as Validation to create a sort of empty template out of destination sheet.
So that every time a form arrives with a click of macro import data gets pasted on empty cells based on set validation rules and then pushed to next system and saved also as a copy for the record in another excel sheet.
I guess the best is to use some kind of RPA flow from inside excel destination sheet to copy only selected column fields and then translate them based on validation rules so that no incorrect values can be inserted in to destination sheet then eventually in to database server.
Last thing, how can i replace "N/A" with 0 ? or empty ? to insert as "null" value in db ?
