Forum Discussion
Excel Data Import with custom formating rules
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 ?
- in the Conversion Sheet Wallet column, change the formula from:
=IFERROR(VLOOKUP($A2,SourceData!$A$1:$N$24,7,0),"N/A")
to
=IFERROR(VLOOKUP($A2,SourceData!$A$1:$N$24,7,0),"")
then copy the same formula all the way to the bottom- Thank you so much for your help but this became too complicated for me. I will try with power automate inside excel. The problem i am facing is when data comes from Web form in an excel sheet, i am unable to run a macro to simple copy and paste it, i wish excel had custom formatting options instead creating another sheet to convert as that simply adds more steps and more work.
nidospartan if your problem is:
"The problem i am facing is when data comes from Web form in an excel sheet"from what you said above, the Web form is an excel sheet. excel power query can point to the web address of the excel sheet Web form and display it into your local excel without copying and pasting, and once you have the power query set up to bring the data in your excel you can either use macro on your excel or other data manipulation to it.