Im having troubles finding the formula to do the following: So i got column A filled with certain phone numbers, and in column B i got a larger list of phone numbers, i need the formula to make column C to have the bigger list without the numbers in column A. Can anyone help me?
This is rudimentary and won’t be as elegant as other solutions. It does need the columns re-arranged though so the numbers to remove are in the second column because it uses VLOOKUP which can’t look columns before it.
RemovePhoneNumbers82737.ods (15.8 KB)
1 Like
To add to @EarnestAl, you can put another column to the right with
=OFFSET($A$1,SMALL(IF($C$2:$C$32="",1000000,ROW()-1),ROW()-1),0,1,1)
Entered as an array formula. Here you should probably name $C$2:$C$32 (or whatever range). The $A$1 is based on the idea that the main list is in column A and there is a single header row, 1. The 1000000 just means a cell bound to be empty way down the A column, since it will act as an offset to A1 (so don’t designate the main list with A:A, to be safe).
It works by creating a list of numbers that are either 1000000 IF the phone number doesn’t occur in the subtrahend, or the row number of the phone number IF it does occur in the subtrahend list. These are sorted with the SMALL function. Then the phone numbers are picked out using the OFFSET function anchored at A1.
ColumnSetSubtractionbyEarnestAI.ods (31.4 KB)
No, because the index is 1 (the same column). Just restore (with Alt) column A to B.
1 Like
Similar though slightly different solutions to EarnestAl. I would suggest using countif instead of vlookup. Assuming Column B has the larger list and you want unique numbers from Column B.
=IF(COUNTIF(A:A,B1)>0,"",B1)
Copy and paste the formula down as needed.
1 Like
With the drawback that COUNTIF() always iterates over all cells with content in the given range to compare with, whereas VLOOKUP() stops as soon as it found a match. With large amounts of data that can make quite a difference especially when used for every entry.
2 Likes