Forum Discussion

nidospartan's avatar
nidospartan
Copper Contributor
May 08, 2021

Excel Data Import with custom formating rules

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.

ExcelExcitingany ideas ? please

developer
excel
Formulas and Functions
Macros and VBA
Office Scripts

8 Replies

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    May 15, 2021
    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
    • nidospartan's avatar
      nidospartan
      Copper Contributor
      May 17, 2021

      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 .

      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        May 17, 2021

        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

Resources