0

I have recently used this solution to create a dynamically generated list of uniques.

What it does is it takes cell values (here: list of names) and generates another list without repetition. Let's say next to those names I wanted to have comments about those names. However, if I add more names (especially if the formula is extended to apply sorting), the unique names and comments columns become misaligned.

Is it possible to make a cell with comment that will “change rows” along with it’s go-to value in any way without using VBA?

I am aware I am copying database behavior, but please assume this needs to be done in Excel (or Google Sheets).

EDIT: Additional info:

What I meant is to somehow bind/update column F to keep it fixed against it's counterpart cell in column E.

Sample data

Improve this question
9
  • 2
    By Comments do you mean Excel's inline comments in a box that attaches itself to a cell or you plan to use an adjacent cell in next column (say Column C) to put some comments about names in Column B?
    – patkim
    Aug 27, 2018 at 9:52
  • 1
    Can you post some sample data (a screenshot here may help) of what you have, and what you'd like to do? I think you might benefit from using named ranges?
    – BruceWayne
    Aug 27, 2018 at 15:34
  • 1
    If I'm understanding right, it sounds like you have a list of names in Col A with duplicates and random order. You use a process to extract the unique names and put them in Col E. Then you manually enter comments in Col F. Later you want to add names to Col A, maybe sort, and rerun the process that puts the unique names in Col E. After that, the Col F comments are no longer aligned. If that's essentially it, the problem is that there is no real connection between the unique names and the comments, they just happen to occupy adjacent columns. (cont'd)
    – fixer1234
    Aug 29, 2018 at 7:29
  • 1
    To relate them, you need something that permanently associates them (a table that doesn't change when you enter or manipulate the Col A data). That table needs to be separate from the Col E results that are subject to revision. The gist would be to create a master table somewhere else. You could use your existing routine to create an initial list of unique names. Once you do, you need a different process to add to it so that you don't disrupt the previous data. Put the comments in that table, and add comments as you add names. (cont'd)
    – fixer1234
    Aug 29, 2018 at 7:29
  • 1
    Don't worry about sorting that table, or even deleting old names. That becomes a lookup table. Now, you can independently do anything you want with the Col E list. Create it or recreate it from Col A as needed, sort it, etc. You fill in the comments in Col F by looking up the name in Col E against your master table and returning the associated value (commonly using VLOOKUP).
    – fixer1234
    Aug 29, 2018 at 7:29

1 Answer 1

Reset to default
1

The problem is that there is no real connection between the unique names and the comments, they just happen to occupy adjacent columns. To relate them, you need something that permanently associates them (a table that doesn't change when you enter or manipulate the Column A data). That table needs to be separate from the Column E results that are subject to revision.

The gist would be to create a master table somewhere else. You could use your existing routine to create an initial list of unique names. Once you do, you need a different process to add to it so that you don't disrupt the previous data. Put the comments in that table, and add comments as you add names. Don't worry about sorting that table, or even deleting old names. That becomes a lookup table.

Now, you can independently do anything you want with the Column E list. Create it or recreate it from Column A as needed, sort it, etc. You fill in the comments in Column F by looking up the name in Column E against your master table and returning the associated value (that's commonly done using VLOOKUP).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .