Comparing Data Need to Include & Exclude

What dioes E1 & E2 mean?
Please explain.

Try something like this:

With your data in columns A and B

E1: Missing (or any text other than the Col_B column title)
E2: =COUNTIF($A$1:$A$27,B2)=0

(Notice the dollar sign ($) plaement in the formula AND that it refers to
the FIRST DATA ITEM in Col_B)

C1: ListB

Select the Col_B data from B1 to the end of the list

From the Excel main menu:
<data><filter><advanced filter>
Check: Copy to another location
List range: (your already selected Col_B data)
Criteria Range: $E$1:$E$2
Copy to: $C$1
Click the [OK] button

That will create a listing, under C1 of the Col_B items that are not in the
Col_A list.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"[email protected]" wrote:

> Hello, i nee some help. Im trying to compare
> ListA & ListB to Achieve ListC
> ListC needs to be the resut of numbers missing from ListA & shoiwing in ListB.
> Hope you can help.
>
> ListC i hvae shown here is an example of what i need to see in ListC
>
> listA ListB ListC
> 2005 2005 2010
> 2006 2006 2011
> 2007 2007 2012
> 2008 2008 2017
> 2009 2009 2018
> 2013 2010 2020
> 2014 2011 2022
> 2015 2012 2024
> 2016 2013 2025
> 2019 2014 2026
> 2021 2015 2028
> 2023 2016
> 2027 2017
> 2027 2018
> 2029 2019
> 2030 2020
> 2021
> 2022
> 2023
> 2024
> 2025
> 2026
> 2027
> 2028
> 2029
> 2030
>
>
>
>


**********End Of Post*************

The E1:E2 range is the Criteria for the Advanced Filter. Usually that means
the first cell must be a column heading from the data list, followed by a
list of items to be matched.

Since we are performing a complex matching function, we can't use one of the
column headings from the data list (eg ListA) in cell E1. A common practice
is to leave the cell blank, but I prefer to give the criteria a somewhat
descriptive heading. Hence "Missing". Technically, E1 can be blank or any
value that is NOT one of the data column headings.

Cell E2 is the key to extracting the correct data.
The criteria formula is:
=COUNTIF($A$1:$A$27,B2)=0

That function begins by checking all of ListA for values that match the
value in cell B2, which is the first data cell under the ListB column
heading. If there are no matching items, the count is 0....so the E2 value
equates to TRUE and the B2 value is copied to the extraction area. Since the
first reference of the COUNTIF formula contains dollar signs, that same range
will be used for every iteration of the formula. The second reference (B2)
has NO dollar signs, making it a relative reference. When the Advanced
Filter runs that reference will change for each item in ListB....First, B2,
then B3...etc to the end of the list. All ListB items that are not on ListA
will be copied to the extractions area under C1.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"[email protected]" wrote:

> What dioes E1 & E2 mean?
> Please explain.
>
> Try something like this:
>
> With your data in columns A and B
>
> E1: Missing (or any text other than the Col_B column title)
> E2: =COUNTIF($A$1:$A$27,B2)=0
>
> (Notice the dollar sign ($) plaement in the formula AND that it refers to
> the FIRST DATA ITEM in Col_B)
>
> C1: ListB
>
> Select the Col_B data from B1 to the end of the list
>
> From the Excel main menu:
> <data><filter><advanced filter>
> Check: Copy to another location
> List range: (your already selected Col_B data)
> Criteria Range: $E$1:$E$2
> Copy to: $C$1
> Click the [OK] button
>
> That will create a listing, under C1 of the Col_B items that are not in the
> Col_A list.
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "[email protected]" wrote:
>
> > Hello, i nee some help. Im trying to compare
> > ListA & ListB to Achieve ListC
> > ListC needs to be the resut of numbers missing from ListA & shoiwing in ListB.
> > Hope you can help.
> >
> > ListC i hvae shown here is an example of what i need to see in ListC
> >
> > listA ListB ListC
> > 2005 2005 2010
> > 2006 2006 2011
> > 2007 2007 2012
> > 2008 2008 2017
> > 2009 2009 2018
> > 2013 2010 2020
> > 2014 2011 2022
> > 2015 2012 2024
> > 2016 2013 2025
> > 2019 2014 2026
> > 2021 2015 2028
> > 2023 2016
> > 2027 2017
> > 2027 2018
> > 2029 2019
> > 2030 2020
> > 2021
> > 2022
> > 2023
> > 2024
> > 2025
> > 2026
> > 2027
> > 2028
> > 2029
> > 2030
> >
> >
> >
> >
>
>
> **********End Of Post*************
>
>
>