[nbjeter3]

Ok,

I am at a loss here, I've tried all kinds of functions and nothing is working like I want it to. Here is what I have and What I am trying to do.

I need to compare a List of Names in one Column (Original), to a list of names in another Column (New) and the output should read in a third column one of three responses: "No Changes", "Name Added", or "Name Removed".

Things to know about this: The names in the second column will not always be in the same sort order as those in the first column. So each cell will need to check against the entire first column.

I am trying to provide as much info as I can, but my head is currently spinning with confusion, as I've been working on this for several hours now with no luck.

 

[nbjeter3]

Oh, I forgot to mention, I am using Excel 2010.

 

[etaf]

not sure how to use the three outputs
As you will only have two conditions
Maybe looking at it differently

If you could also provide a sample with examples of the three conditions

From what you have said - you are comparing the name in column B with column A

so in column C2

=countif(A:A,B2)=0 - true = the name does not appear in column A
=countif(A:A,B2)>0 - true = the name does appear in the list

and you can add that into an IF()

so we can tell if the name exists and if it does not exist

"No Changes",
would be =countif(A:A,B2)>0
the name exists

"Name Removed"
would be =countif(A:A,B2)=0
the name does not exists

"Name Added"
Not sure how to do that ?
other than a different count
compare A2 with column B


can you put into words how you would be able to tell the following

"No Changes",


"Name Removed"


"Name Added"

 

[nbjeter3]

With a lot of hunting I finally figured out a way to do it. First, I broke it up into two different columns with opposing functions. In the first column, I compared Column B (the current list) with Column F (Last Months list). If a name appeared in Column B that wasn't in Column F, then it populates "Added". If the name appears in both columns then it populates with "No Changes" (I also added a nested IF Function that leaves it blank if the cell isn't populated) this is the function:

=IF(B18<>"",IF(ISERROR(MATCH(B18,$F$18:$F$116,0)),"Added","No Changes"),"")

I then created a second, opposing function that checked Column F against Column B, If a name appears in F and not in B then it populates with "Removed", and if it appears in both columns it populates with "No Changes" (Also with the nested IF statement that checks for blank cells):

=IF(F18<>"",IF(ISERROR(MATCH(F18,$B$18:$B$117,0)),"Removed","No Changes"),"")

My newest tweak is that I am working on Conditional formatting, that when "Removed" or "Added" is populated, it changes the cell fill color and text color of the cell populated with either "Added" or "Removed" as well as the cell it's next to (the name that has been added or removed.)

So far, I've only been able to get it to work on the populated cell itself, but not on the cell with the name in it that was added or removed.

 

[etaf]

you need to use a $ and select the columns B and F and the status - assuming G

conditional formula
=OR( $G2="added", $G2="removed")