Collection - Add to collection based on primary key in column A of collection 1 to end of collection 2

mloring1972

New Member
I have two collections - IR - it has multiple Columns with Position Number in A1, this acts as a primary key (only one position number for each person) I want to add column D of this IR worksheet to work sheet MAAT (sheet 2) add it at the end of the Column (both sheets have the same position number as the PK). Can I use PK of sheet 2, and get the IR status of (Yes or No) from column D to the end of sheet 2 (MAAT) against the PK.

PK1 = PK 2 and appends to Sheet 2 (MAAT) at the end (if PK1 = PK2 append IR Status to MAAT worksheet as ne column).

IR status worksheet
A1 = Position number, A2 = IR Status (Yes/No)
1000986 Yes

MAAT worksheet
A1 = Position Number, A2 = WHS, A3 = Alcohol training, A4 = Asbestos Training

New collection
A1 = Position Number (same as in IR Status), A2 = WHS, A3 = Alcohol training, A4 = Asbestos Training + IR Status (A2 from IR Status)
1000986 2/4/2020 2/4/2020 2/4/2020 YES


My concern is if a position number is not in worksheet 1 (IR Status) but in worksheet 2 (MAAT) what will happen, what if a new position is added or deleted in one but not the other (rows don't match) can I use positions number in work sheet 1 to find position number in worksheet 2 and append the IR Status to the end of the right collection row/column in worksheet 2 (MAAT)?.

I do not have control of how the position numbers are added in the two worksheets - I was thinking of adding two collections, sorting them by position number and then appending the two collections into a new collection (hopefully the IR status will append to the correct position number) but is this risky? Or should I sort the data by position number in excel first and then add to a collection.

I need this to be simple no VBA code etc.... Just need to understand if I can append two collection rows based on the position number so that if IR Status for the position changes, it will update the correct collection field....

Sorry I am so new to RPA and BP.
 
Top