How to copy rows selectively by key value

Overview

Copy row by key is an excel file instruction in SolveXia instructions library. It offers to copy a single row that matches supplied key in the specified column and paste copied row to matching key in the specified column in the target excel file. Copy row by key instruction

  • Requires target key criteria to match
  • cannot copy multiple rows from the source. (i.e. instruction stops running when it finds duplicate keys in specified source column)
  • each condition in instruction can copy one row
For the purpose of this article we are going to use a simple and fictitious example of a source and target excel files. You will want to replace reference to the source & target excel files with files of your own.

For example, referring to the picture below, the Copy row by key can copy rows data within specified source range where,

  • column A contains key “Text-1” and paste in the target file where column A contains “Text-1”
  • column A contains key “Text-3” and paste in the target file where column A contains “Text-3”

This would allow the source data on the left to be transformed into the target data on the right

Steps

  • Load process and turn on Design mode
  • Load file manipulation step
  • Click on blue text No instruction has been selected yet, click here to select an instruction
  • Click on Excel group, when popup opens with list of instructions
  • Select Copy rows by key instruction

  • Once you select Copy rows by key, step will display controls to configure an instruction with values.
  • Click on Link button to select source Excel workbook from where you wish to copy row that match with the key in the column. Once you click on Link a file searcher will be opened that allows you to select an Excel file.
  • Select Excel file, and click on Choose the selected item button at the bottom right corner of the file searcher. This action will link file in the source section.
  • Now click on Link button of target section to link an excel workbook. Repeat step 9 and choose target workbook.

  • Click on drop down button, and select source worksheet name from the drop down list
  • Click on Address drop down list and select Advance range option to specify the source range
  • Enter From Cell address as ‘B2’ and in To Cell, select Last Cell in Col option, and enter column ‘B’ in first text box, and column ‘F’ in ‘as far as’ text box
  • Please note: In step-12, specified source range are covers only row range from where instruction should copy data. This does not include the column that contains unique key.

  • In target section, click on target worksheet drop down list, and select target worksheet
  • Select Advance range option in the target section
  • Select the Address in From Cell option, and enter ‘I1’ as starting cell address from where instruction should start pasting selected rows and Select To fit source data option in To Cell option
  • Please note: In step-16, specified target range indicates top cell address from where instruction should start pasting the copied row data. It does not mean that instruction will paste row from cell B2. Instruction will paste copied row in row number where target key is.

    Now in parameter section, you can configure set of conditions to be met for source rows to be copied that match the given key and paste in the target wherever target key matches. Instruction contains a default condition that you can use. This example shows how to configure 2 conditions.

  • Enter column name A in the Column text box. (This is key column should contain unique keys)
  • Click on drop down list to select an operator. Select Equal operator for this example. You can choose any operator from the list
  • Enter Key value that should be in column A
  • In target section, Enter column name A in the Column text box. (This is key column can have duplicate keys)
  • Click on drop down list to select an operator. Select Equal operator for this example. You can choose any operator from the list
  • Enter Key value that should be in column A
  • Click on Add button to add another condition, and repeat step 16 to 21 to configure second condition

  • Click on Save button to save step configuration.
  • Once step details are saved, click on Run button to start running the step.

  • After running an instruction, rows that match to the given source key will be copied to target worksheet wherever target key will be matched.

  • Click on Download button in the target linked file to download a file, and view target worksheet

  • You will see following outcome for the conditions configured in above instruction.

Leave a Reply

Latest Tweets

Free eBook: 10 Tips for sourcing good data yucalc.com/2017/02/17/fre…

reply · retweet · favorite

Newsletter

By signing up, you agree to our Terms of Service and Privacy Policy.

Address

Suite 1506, Westfield Tower 2, 101 Grafton St Bondi Junction NSW 2022 Australia

View Larger Map

Contact Us

AUS: +61-2-9386-0202
UK: +44-20-8123-1251
Send us an email

ContactUs.com
WordPress Image Lightbox