How to copy rows selectively by key value

Overview

[highlight txt=’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. [highlight txt=’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
[message type=”warning”]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.[/message]

For example, referring to the picture below, the [highlight txt=’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 [highlight txt=’No instruction has been selected yet, click here to select an instruction’]
  • Click on [highlight txt=’Excel group’], when popup opens with list of instructions
  • Select [highlight txt=’Copy rows by key’] instruction

  • Once you select [highlight txt=’Copy rows by key’], step will display controls to configure an instruction with values.
  • Click on [highlight txt=’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 [highlight txt=’Link’] a file searcher will be opened that allows you to select an Excel file.
  • Select Excel file, and click on [highlight txt=’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 [highlight txt=’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 [highlight txt=’Advance range’] option to specify the source range
  • Enter [highlight txt=’From Cell’] address as ‘B2′ and in [highlight txt=’To Cell’], select [highlight txt=’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 [highlight txt=’From Cell’] option, and enter ‘I1′ as starting cell address from where instruction should start pasting selected rows and Select [highlight txt=’To fit source data’] option in [highlight txt=’To Cell’] option
  • [highlight txt=’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 [highlight txt=’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 [highlight txt=’Add’] button to add another condition, and repeat step 16 to 21 to configure second condition

  • Click on [highlight txt=’Save’] button to save step configuration.
  • Once step details are saved, click on [highlight txt=’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 [highlight txt=’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.

Recommended Posts

Leave a Comment