I have two columns of numbers, column A and column B. Column A is where a numbered tag is entered when it leaves and Column B is where the numbered tag is entered when it returns. There are spaces in both of these columns. I am having trouble finding a proper formula or way to display in two additional columns tag numbers that were sent but not returned, and tags that were returned but not sent, essentially numbers that don't appear on both columns.

1

2 Answers

Look into MATCH() - it will display N/A for not found, IF(ISNA(...),...) will help for those if you wish to display something else. Example sheet below.

-- Untitled.csv --

Left,Sent+NR,Returned,"nonS,Returned"
a,"=MATCH(A2,$C$2:$C$8,0)",e,"=MATCH(C2,$A$2:$A$8,0)"
b,"=MATCH(A3,$C$2:$C$8,0)",g,"=MATCH(C3,$A$2:$A$8,0)"
c,"=MATCH(A4,$C$2:$C$8,0)",a,"=MATCH(C4,$A$2:$A$8,0)"
d,"=MATCH(A5,$C$2:$C$8,0)",b,"=MATCH(C5,$A$2:$A$8,0)"
e,"=MATCH(A6,$C$2:$C$8,0)",c,"=MATCH(C6,$A$2:$A$8,0)"
f,"=MATCH(A7,$C$2:$C$8,0)",x,"=MATCH(C7,$A$2:$A$8,0)"
g,"=MATCH(A8,$C$2:$C$8,0)",y,"=MATCH(C8,$A$2:$A$8,0)"

I would create a conditional format that will highlight any values in column A where there is not a corresponding value in column B. In this fashion, you can quickly visually see what tags have not been turned back in.

To enter a conditional format

1. Select cell A1
2. On the ribbon, select Home tab and then Conditional Formatting
3. On the following screen, select new rule and then select Use a formula to determine which cells to format
4. Enter the following formula =COUNTIF($A:$A, A1)
5. Change the background and text color per your liking

6. Change the Applies To box to column A

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy