Hi all,
This should be a simple problem but I seem unable to sort it:
I have a list of numerical and word product codes in B44:B60, e.g HJK/5566712/19
A macro button on each row can copy the data from B44 over to H44 when sent to another department.
Hitting the button again undoes the previous action.
Starting on K44- I want to create a list of outstanding entries from list H44- i.e. which values from B44:B60 DO NOT appear in H44:H60.
I've tried a couple of methods- namely in K44, I have used the following function:
=Filter(B44:B60,NOT(COUNTIF(H44:H60,B44:B60)))
Which works initially- however, if I remove data from H44:H60, the filtering array automatically changes to become the last row in column H i.e. If I remove data from H50, the formula rewrites to
Filter(B44:B60,NOT(COUNTIF(H44:H50,B44:B60))).
If all data is removed, it returns a #Ref error.
I have tried using absolute references and named ranges in place of the cell ranges, but these problems still occur- where am I going wrong?
I've resorted to using VBA which I adapted from a similar thread but really want to understand where I went wrong with the filter function, thanks for any help you can give.
This should be a simple problem but I seem unable to sort it:
I have a list of numerical and word product codes in B44:B60, e.g HJK/5566712/19
A macro button on each row can copy the data from B44 over to H44 when sent to another department.
Hitting the button again undoes the previous action.
Starting on K44- I want to create a list of outstanding entries from list H44- i.e. which values from B44:B60 DO NOT appear in H44:H60.
I've tried a couple of methods- namely in K44, I have used the following function:
=Filter(B44:B60,NOT(COUNTIF(H44:H60,B44:B60)))
Which works initially- however, if I remove data from H44:H60, the filtering array automatically changes to become the last row in column H i.e. If I remove data from H50, the formula rewrites to
Filter(B44:B60,NOT(COUNTIF(H44:H50,B44:B60))).
If all data is removed, it returns a #Ref error.
I have tried using absolute references and named ranges in place of the cell ranges, but these problems still occur- where am I going wrong?
I've resorted to using VBA which I adapted from a similar thread but really want to understand where I went wrong with the filter function, thanks for any help you can give.