Hi guys,




I had an idea for a document: there is a worksheet with a table, tons of entries (rows) in this table. However in a second worksheet, certain characteristics are looked up from that big table, and only certain rows are returned.


The document has to do this "live" (real time) without needing to manually press buttons, and the document cannot use macros, plus it has to be compatible with 2007 excel version. So really it must use smart code inside some cells.


The idea is, that anybody could add items onto the end of the big messy table, however in another worksheet, relevant items would continue to be shown there, even when new ones are added.



What I don't have a problem with:
Returning certain rows based on info from a column
Removing duplicate entires (haven't run into this yet)...


What I do have a problem with:
Removing any and all spaces from the "smaller" list that is generated.



As someone with rudimentary coding knowledge, I set out to find a method where this can be done, but man, was it not easy - I learned about iterative/circular functions, where cells can re-calculate and have some self-awareness regarding their own contents, and this worked great for me, I made some test programs and they worked, however they weren't "manipulating cells" they were just maths...



So, I am treating this a bit like stack overflow, I have provided a minimum reproducible example. In the attached document there are two little tables of data, I removed anything that was not necessary, so it's very bare bones. I don't even think these functions support the addition of new data when they are "live" yet, but I am dealing with one problem at a time.


The left column, is the "original data", so to say, so it is not to be touched, and the right column is supposed to be the version with spaces removed.


If you write anything in cell I3 (I for indigo), the cells will begin work, but they will only iterate once - if you press F9 a bunch of times, they will get to the end of what they're doing (move everything to top, without affecting order). My question is, can I do anything to make it automatic, so that I don't need to press F9 a bunch of times? By the way, deleting whatever is inside cell I3 will "reset" everything and stop functions from calculating stuff, it's a little debugging thing.



I decided to write here because maybe there is a better way, obviously the end result of what I want to do will be quite long code, but I don't want people to do all my work for me, I am just stuck on one specific part of things.


Thanks in advance, love you all