I need to have manual data track a unique dynamic list.

Hello,

I've been fighting this for weeks, I have a dataset that is entered manually. I'm using the =unique function to extract the unique values in the list, but I need "attach" manual data to what's extracted. In my attached example I have a list of colors that spans several rows and columns. Next to this I extract the unique colors and manually assign a color code. If the dataset changes or grows I need a way to automatically shift the color code to the correct row.

In my real spreadsheet there would be hundreds of rows and columns, and everything to the right of the dynamic list would need to track it's corresponding "color".

While you did just blow my mind, the real spreadsheet doesn't actually have anything to do with colors. In my head I had written that in my original post, but after re reading it I obviously did not. My apologies.

sssooooo, in the next post say what you really need

Using your example above pretend the hex data wasn't being calculated, but manually entered. the data itself is irrelevant. The important part is it must be entered manually, and always track with the dynamic data set.

So if the value/ hex columns originally looked like

value | hex

brown| potato
green | ostrich
red | bandanna

but due to the dynamic list changing it would end up becoming

value | hex

yellow | potato
brown | ostrich
red | bandanna
green |

What I really need is

value | hex

yellow |
brown | potato
red | bandanna
green | ostrich

Thank you for taking the time to help me. I'm afraid this is one of those times I don't even know how to ask the proper question.

post example excel file with source data (BEFORE) and expected result (AFTER) with a bit more than 2-3 lines, maybe 10-20

Book2.xlsx

Here is another example.

You're correct, and that's pretty much what I did in the working version of the spreadsheet before I had access to office 365 and the unique function. I created a sheet with almost all possible "color codes" and just used index/match to perform lookups. Unfortunately for me I'm using several array formulas, and a lookup on a different workbook. The calculation times had climbed ~ 1 minute. I was hoping by generating the list as I went it would cut down the calc times. Inserting a row into my "color codes" table also takes ~ 1 minute.

Do you know of a function that excel would only trigger once and forget? I was thinking I could do what I need with xlookup if I had a way to initially give it a value to look up. Say COLOR CODE had a lookup formula nested in an if statement, and when UNIQUE LIST <> blank it would copy that text string into the XLOOKUP value somehow, and then never try to overwrite the lookup value.

Edit Reply: No the dataset is updated manually.

set it and forget it ?

with formula(s) maybe someone else

I can try with Power Query but I really need assignment table
btw. in my first post assignment table (color and HEX) has 251 rows

Originally Posted by EtradeBaby

Edit Reply: No the dataset is updated manually.

good news

You definitely sent me down a power query rabbit hole. I see a lot of reading and YouTube in my future. Thank you for that. I think. Maybe ask me in a week.


Seriously though. Thank you for trying to help me it's greatly appreciated.

Power Query is not so complicated as you think

anyway you can try with your unique list and assignment table then create new table assignment and manual entries in proper order
it can be done with formula(s)

assignment table is required because nobody knows the 2 is red but not brown or 256 is pink but not blue

but sorry I can't help with formula

Unfortunately not, I'd still need some way to fill in the missing data.