We have lots of spreadsheets that are exported from different systems where each system doesn’t know or can’t export some of the information we need to work with the data. Something that should never happen but is probably a pretty common scenario in the real world.
Without getting into the details of the problem too much, That would be really boring for most people, here’s what I want to do.
I want to write a function (or macro?) that will add some value like “1” which will mean “the products I have to track inventory for” to collum b whenever the value in collum a equals any one of a long list of values which I have stored in another sheet.
Assuming that the first entry is in A1, I’d write something like =if(iserror(vlookup(a1,*,1,false)),0,1) where * is the range containing the list of values you’re comparing it to. If you’d rather have a blank instead of a zero for items not on the list, you can change the “0” to “”.
You might be able to do it just using MATCH or Vlookup…Does it need to be “1” or could #N/A work too? I think MATCH needs to be alphabetical (or 1,2,3,4…).
If I understand what you are going for, I do similar things getting a “1” or “True” or ??? then I Copy > Paste Special > As Values to remove the function. Then I Data > Sort… the selection and group all of the “1” or “True” or ??? together.