I ran across a formula on this site used to compare lists. It involved ISNA
functionality and I was hoping someone could explain it to me.

As an example, I most often compare two lists to see where there are
discrepancies. Currently I use A1=B1 and move the rows of false responses so
I can see what has been added and what has been removed.

Visually:

New List Compare Old List
Tom TRUE Tom
Dick FALSE Harry
Harry FALSE Sally

becomes

New List Compare Old List
Tom TRUE Tom
Dick FALSE
Harry TRUE Harry
FALSE Sally


This allows me to see that Dick has been added and Sally has been removed.

HOWEVER, the ISNA function seems to be a faster, easier way. I would like
to learn how to use it, but just handing me a formula won't help (as much).

So, what does ISNA stand for? How do you frame the formula (what are the
components of it)? Will it work in a two way comparison (added and deleted)
or does it need to be done twice to achieve full understanding of the changes
between the new and old list?

I thank you for wading through this question and any assistance you can
provide.


--
Please don''t just give it to me, explain how/why so I can stop bugging you
fine people! :)

ISNA is merely a boolean decision (Boolean means return True or False). It
looks at the formula or value within the () to determine if the result is #N/A

typically, it would look somethign like =IF(ISNA(formula),do something,do
something else)

if you recall your math, you always do what's in the parentheses first... so
the formula inside your ISNA is run. If the formula returns a value of #N/A
(not available), then ISNA() will return True. In this case, the If formula
would run the do something. Otherwise, the If formula would run the do
somethign else instead.

"Dyvotion" wrote:

I ran across a formula on this site used to compare lists. It involved ISNA
functionality and I was hoping someone could explain it to me.

As an example, I most often compare two lists to see where there are
discrepancies. Currently I use A1=B1 and move the rows of false responses so
I can see what has been added and what has been removed.

Visually:

New List Compare Old List
Tom TRUE Tom
Dick FALSE Harry
Harry FALSE Sally

becomes

New List Compare Old List
Tom TRUE Tom
Dick FALSE
Harry TRUE Harry
FALSE Sally


This allows me to see that Dick has been added and Sally has been removed.

HOWEVER, the ISNA function seems to be a faster, easier way. I would like
to learn how to use it, but just handing me a formula won't help (as much).

So, what does ISNA stand for? How do you frame the formula (what are the
components of it)? Will it work in a two way comparison (added and deleted)
or does it need to be done twice to achieve full understanding of the changes
between the new and old list?

I thank you for wading through this question and any assistance you can
provide.


--
Please don''t just give it to me, explain how/why so I can stop bugging you
fine people! :)

A agree with Sean.

Try this:

Convert your "Compare" column into this formula:

=IF(New List=Old List,0,1)

Explanation:
The formula will tell you records with different contents by displaying "1"
(you may also know the no. of records that does not match). If it would be
okay to sort your data then convert the formula into value and sort using
that column as your criteria. All "1"s will be isolated and will be faster to
edit.

Hope this helps.

Dyvotion wrote:
I ran across a formula on this site used to compare lists. It involved ISNA
functionality and I was hoping someone could explain it to me.

As an example, I most often compare two lists to see where there are
discrepancies. Currently I use A1=B1 and move the rows of false responses so
I can see what has been added and what has been removed.

Visually:

New List Compare Old List
Tom TRUE Tom
Dick FALSE Harry
Harry FALSE Sally

becomes

New List Compare Old List
Tom TRUE Tom
Dick FALSE
Harry TRUE Harry
FALSE Sally

This allows me to see that Dick has been added and Sally has been removed.

HOWEVER, the ISNA function seems to be a faster, easier way. I would like
to learn how to use it, but just handing me a formula won't help (as much).

So, what does ISNA stand for? How do you frame the formula (what are the
components of it)? Will it work in a two way comparison (added and deleted)
or does it need to be done twice to achieve full understanding of the changes
between the new and old list?

I thank you for wading through this question and any assistance you can
provide.