"Copy Rows" - Automated Workflow

Kyle Morgan

A bit of a longshot, here... but I have seen this community pull together and come up with some creative solutions so I figured..."why not ask?" 💡

Is there a way to copy a row ONLY when the data in a cell is unique (not copy when the info already exists within the column)?

EXAMPLE:

I have a single sheet containing a single column. - This sheet has a form user(s) to input an item number. - When a new item number is entered into the sheet, a "Copy Rows" automation sends that item number to multiple sheets where a whole host of magical things happen.

Does anyone know of a way to prevent the same item number being sent twice (duplicated across all sheets)? I am okay with the same item number appearing multiple times in the source document as its only purpose is to trigger the workflow and populate other the other sheets with the item number. I just want to prevent the workflow from sending the same item number more than once.

Paul Newcome

Insert an auto-number column (no special formatting needed.

Insert a text/number column with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Insert a checkbox column with the following column formula:

=IF(COUNTIFS([Item Number]:[Item Number], @cell = [Item Number]@row, [Text/Number Column]:[Text/Number Column], @cell <= [Text/Number Column]@row) = 1, 1)

This will check the box on each row for the first occurrence of that particular item number. Set your workflow up to be triggered when that box changes to checked, and you should be good to go.

Paul Newcome

Insert an auto-number column (no special formatting needed.

Insert a text/number column with the following column formula:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Insert a checkbox column with the following column formula:

=IF(COUNTIFS([Item Number]:[Item Number], @cell = [Item Number]@row, [Text/Number Column]:[Text/Number Column], @cell <= [Text/Number Column]@row) = 1, 1)

This will check the box on each row for the first occurrence of that particular item number. Set your workflow up to be triggered when that box changes to checked, and you should be good to go.

Kyle Morgan

Thanks, Paul!

Now, I assume that these 2, new columns will be copied over into the destination sheets, correct? (I will need to add 2 additional columns to these sheets to accept the data)

Paul Newcome

All three columns will copy over, but you do not need to manually "make room". They will automatically add the needed columns as soon as the first copy row automation triggers and sends the data over.

Kyle Morgan

Thanks for the help, Paul - much appreciated. This was the perfect solution!

*note: had to make one minor alteration to your checkbox column formula (as written, the checkbox is checked with each new entry and is removed from previous entries due to <= ) - changing the last part to "greater than or equal to" - shown below -solves the issue

=IF(COUNTIFS([Item Number]:[Item Number], @cell = [Item Number]@row, [Text/Number Column]:[Text/Number Column], @cell >= [Text/Number Column]@row) = 1, 1)

Paul Newcome

Sorry about that. Glad you were able to figure it out.