Hallo everyone, I get a table of data/text made of 6 columns (from A to F), generated by a system. I simply past this table (green) manually in a Excel sheet.
After pasting the table, I add manually a column G (yellow) at the right of the table, and add manually some comments for each row in this column.
Every month I get an updated table from the system and paste over the current, but the G column is not more aligned with each row, and some comments for one specific line could be related to another one!


Any suggestions how to make sure each table row will match correct comments on G column?
Hope to be clear!
See attached ....
Thanks

Attached Files

Test1.xlsx (10.2 KB, 4 views)

In case some column in range A:F has unique value for for table row, define the datarange of this column as dynamic Named Range (e.g. nTableRowID). Otherwise add a column with formula, which creates such unique identifier for every table row, and define the datarange of this calculated column as dynamic Named Range (e.g. nTableRowID).

Now in another sheet, create a 2-column table, where for 1st column you define Data Validation List, which allows you to select a row inentifier value from main table (using defined dynamic Named Range as source (e.g. '= nTableRowID'). Select the row identifier value in 1st column, and enter comment for this main table row into 2nd one.

In main table add a column to display comments, and fill it with VLOOKUP formula to get matching comments from 2nd table for every row.

Now when you have next update of your main table, your old comments are displayed in comments column of main table. In case there are new data added, the comment field will return error or be empty in case you used IFERROR(...,"") in formula. You can add missing comments into 2nd (comments) table. You also can update existing comments in comments table whenever you need this. All changes will be displayed in main table.

Thank you! But I need to update the comments column of main table, how can I update my comments if there is a formula vlookup inside?
thanks for help!

You can't, unless the main table remains always same, or you update your main table manually row-wise! In case the main table can change, and you must keep old comments, you must have those comments updated in another table.

Another possible solution will be having a separate table where you copy new data, and then run a VBA procedure, which compares both new and old data row-wise, and overewrites row-wise old data when there is some difference. What I adviced is much easier, I think!

Thanks, I will try by manually the main table!
Thank you for the help,
regards