Excel Data Import with custom formating rules

[nidospartan]

Hello i want to copy-paste data from one workbook in to another work book based on specific custom cells range and then paste in to another sheet to push to database and for internal record. The problem is data is in different formats for example in workbook 1 Words such as "Decrease" to be pasted as just "D" in target workbook, and "Invalid Card" to be pasted as Digit "5" and Word "Other" as 6

For example the data in above sheet is coming via a web form filled by a shop owned and then want to save it via a macro import in the workbook below without manual selecting each cell

Please help any help is appreciated.

1. Is it possible to select only specific cells to copy ?
2. Is it possible to Change formats for Text in to abbreviated or short Text ?
3. Is it possible to set rules on empty cells for data validation to assign values to text strings?
4. Is it possible to set data validation rules for empty or blank cells with custom Text fill options such as based on text intent ?

Any help will be highly appreciated, thanks in advance.

@Faraz Shaikhany ideas ? please

[#2]

all the questions in your post is yes, share your sample workbook so we can show the solution in your sample workbook and you can implement it on your actual workbook

[#3]

@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 .

[#4]

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

[#5]

@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 ?

[#6]

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

[#7]

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.

[#8]

Hi @nidospartan 

Good luck in your endeavor and have a great day

[#9]

@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.