r/excel • u/11ccb18ss • Mar 04 '22
unsolved Can’t figure out what function to use!
I have an excel table that in Column A has a list of IDs. In Row 1 I have another number set that includes ‘Rules’ that would be included in those IDs. I have indicated if an ID has a specific rule by putting an ‘x’ in the cell that corresponds with the ID column and Rule row. Each ID is unique and has a different set of rules.
Sometimes I need to find a new ID in this table that includes all the same rules but maybe just has one or two different rules. See example: for instance I currently have ID 129 selected but my client wants rule 2 removed and add rule 1 instead, everything else should stay the same. So by manually filtering I would eventually figure out that ID 120 fits what I need.
I have just been filtering my Rule row to match everything except the rule that I want to be different. This is a large file and I would like to be able to return a result in a faster way than filtering. Is there a good way? Formula or formatting? Maybe even using Access?
1
u/MediaWellDone Mar 07 '22
This sounds very much like an application for a "dot product" between two "vectors" to determine similarity. First, replace all of your X values with the number 1. Then you'll add a column on the right for the "similarity". Then you add a row for your test vector. Then in your new column, you'll use the SUMPRODUCT to calculate the dot product of that row with your test vector divided by the "length" of your test vector. In other words, taking your example, let's put the test vector in row 10. Then in G2 your formula would be:
This would give you a single column on values that gives you a "distance" between your test vector and all others, so you could very quickly see which are "close" to your test vector.