Re: Comparing Data Need to Include & Exclude

Well i tried it, i cant make it work. 
Did you try it to see if it worked??? and you got the Results i need
as shown in list3 ?? 


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


"someone@somedomain.com.invalid" 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
> 
> 
> 
> 



0
6/6/2006 1:42:54 PM
excel 39879 articles. 2 followers. Follow

1 Replies
744 Views

Similar Articles

[PageSpeed] 27

Yes...I tested my posted solution and got the exact results that you expected 
to see (per your post). The results of the Advanced Filter are in Col_C.

Here's my setup:
Col_A	Col_B	Col_C	Col_E
listA	ListB	ListB	Missing
2005	2005	2010	=COUNTIF($A$1:$A$27,B2)=0
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		

Try using the above setup in a test worksheet to see if you can figure out 
what's different from your previous results

Let us know what you discover.
***********
Regards,
Ron

XL2002, WinXP


"someone@somedomain.com.invalid" wrote:

> Well i tried it, i cant make it work. 
> Did you try it to see if it worked??? and you got the Results i need
> as shown in list3 ?? 
> 
> 
> 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
> 
> 
> "someone@somedomain.com.invalid" 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
> > 
> > 
> > 
> > 
> 
> 
> 
> 
0
6/6/2006 2:26:02 PM
Reply:

Similar Artilces:

Need text/detail of appointments in Word doc for non-Outlook users
Dear All, Further to and concurrent with my enquiries about missing information in appointments, I also need to share the entire holiday schedule with my Mum, sister and cousin. They do not have Outlook. They do have Word. My idea was to email them a Word document containing the holiday schedule. I searched your help database and could only find references to techniques which embedded an icon which then opened the Outlook calendar. I need to dump the entire subject, text/notes and dates contained in approximately 20 appointments so that they can be informed of our schedule. How do...

re: sum cells containing text
Hi. Can anyone help me please. I want to sum the values of certain cells, that contain numbers and text eg. cell B2 contains 32A cell B3 contains 12B cell B4 contains 6C the text will always be a, b or c, and always stay on the right Thanks in advance With sample data like this: cell B2 contains 32A cell B3 contains 12B cell B4 contains 6C Try this formula: =SUMPRODUCT(--LEFT("00"&B2:B10,LEN(B2:B10)+1)) With the above data, the formula returns: 50 32+12+6 Is that something you can work with? -- Ron Coderre -----------------...

need a formula 11-15-09
need to find the formula for b3 is 8 hrs c3 = -.50min / b3 is less than 8hrs c3 -.25 example b3 8. hrs c3 7.5 hrs / b3 6. hrs c3 5.75 Terry: take a big breath and retype this slowly using punctuation and line feeds. We do want to help but this makes no sense to me Unlike cell phone text messages, there is no word-count cost. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Terry" <Terry@discussions.microsoft.com> wrote in message news:8A0B8990-E7EE-4FF7-9FE1-5577D1DBF3EE@microsoft.com... > need to find the formula f...

Exclude Header from Range?
Hi, can I exclude a header from a range in an equation, i.e.: "C:D" to exclude 5 rows from the top? Thanks Hi Depends what you are trying to do. You could have =SUM(C:D)-SUM(C1:D5) for example. -- Regards Roger Govier "nastech" <nastech@discussions.microsoft.com> wrote in message news:9F8FBB8F-8978-42CE-8797-1809B8CA39C9@microsoft.com... > Hi, can I exclude a header from a range in an equation, i.e.: "C:D" to > exclude 5 rows from the top? Thanks =SUM(c6:d65000) or equilavant equation "nastech" wrote: > Hi, can I exclude ...

How to draw a regression line on 7000+ dated data with Excel 2007
How to draw a regression line on 7000+ dated data using Excel 2007 Are your "dates" actual Excel dates or just text? With actual Excel dates, pre-2007 versions have no problem doing simple linear regression. To interpret the results though, you need to remember how Excel dates are stored: An Excel date is the number of days since 1900, so the numeric value of today's date is 39736, and the intercept of the regression will be the value of the relationship extrapolated to 30Dec1899 (since Excel mistakenly considers 1900 to be a leap year) "Gordon Lee" wrote: >...

Get rid of "folder sizes" and "data file management" links
at the bottom of the folder list? I'd love to remove these to make more space available in the list. Thanks. ...

working with default data files
I am using IMAP and I would like to config outlook to bring all my emai to the default "personal folders" or delete that folder. Is tha possible -- g.garrett ----------------------------------------------------------------------- g.garrette's Profile: http://www.officehelp.in/member.php?userid=491 View this thread: http://www.officehelp.in/showthread.php?t=124989 Posted from - http://www.officehelp.i g.garrette <g.garrette.2gnhe4@NoSpamPleaze.com> wrote: > I am using IMAP and I would like to config outlook to bring all my > email to the default "personal fo...

Multiple Conditions Formula Help Needed
I currently have a formula that I have used to calculate commisio rates. We had a 2 rate plan. If sales were below a certail threshol then apply this rate and if above apply this rate. I used an IF/THE formula. We have recently switched to a 4 rate plan, and I am unsur how to create a formula to reflect this. Please advise.. thanks dav -- Message posted from http://www.ExcelForum.com One approach would be a table to use with VLOOKUP. In the formula, where you now have a rate, use VLOOKUP. On Wed, 21 Jul 2004 16:53:11 -0500, chiwavdg <<chiwavdg.19rkim@excelforum-nospam.com>...

Money Data
I am using Money 2002 (simply because later version don't offer anything new that I need) but I cannot find anyway or option that will allow me to store the data externally. I can store the backup in a variety of places, however, I would like to keep the primary files, and the automatic backup, off the hard drive and unaccessessible to hackers. Does anyone know how this can be done? Thanks You can copy your .mny file to any media you like using the normal Windows tools from My Computer or Windows Explorer. You can also usually backup to these media using browse from within the M...

"create mscrm.sql" error on 3rd (re-)install
I have installed CRM 1.2 on SBS2K3 twice as "advetureworks cycle" and "msdn subscriber" with only 90% success because of conflicts on the web site. I have removed the conflicts but now CRM installer errors trying to create the new database as (default) local service. My login has SQLServer db_creator permissions and I can create a DB, so I'm totally confused. Can I workaround this somehow ? What login needs to be given db_creator rights so Install can run te SQL scripts ? Hi Marcus, have you deleate the complete the old date from AWC? Have you delete the ...

sorting data #2
Sorry if this has been asked before, but ive searched to no avail, as I dont really know what to call the question. i have the following A B 4 2 1 5 2 3 1 6 4 3 Can I automate the sorting of this data so that for each value in column A the Values in Column B are totaled, ie it becomes A B 1 11 2 3 4 5 Thanks Andy --- Message posted from http://www.ExcelForum.com/ You could either sort your data by column A and then Data|subtotals or you could add headers and do Data|pivottable. powelly wrote: > > Sorry if this has been asked before, but ive searched to no avail, as I >...

Re: Saving CSV in Unicode?
I'm using Excel to write a CSV in UNICODE. If I simple save this file then all the unicode text is converted to ASCII '???' things. So I specify the file name like "test.csv" and specify "UNICODE Text" in FileType. Now when I open the file in Notepad, I find no comma but TABS as separator. If I open the file in Excel, it prompts me to select delimiter with which the file was saved. If I select anything except "Tab" then all the data is shown in single column that I've to break using "Text to Column" Feature. And you know my clie...

data entry problem
I am trying to make a detailed sheet of some inventory data whic changes each month. The first sheet just has general data, but ther are more columns on the second sheet with varying expanded details Column A - product number Column B - color Column C - available Column D - Misc notes (may or may not be filled in) [SHEET 1] Month 1 A B C D 2310 blue yes 2348 red M/R must sell soon 2362 blue no (some text data in-between) 2405 red M/R need to be redone 2450 grn yes sell cheap 2477 wh yes Month 2 (new ones may be added, and sold item t...

Need help plz! Unable to Send/Recv emails in OE.
I'm sorry if this is a dumb question but I just set up my Outlook Express and I'm unable to Send/Recv emails which is strange because I already have an msn.com email account which works perfectly but I wish to make OE my default email and that doesn't work. Whenever I try to send an email or press the Send/Recv button I keep getting these two error messages: 1: "The service is currently unavailable. Try again later. Account: 'MSN Mail Server', Server: 'http://services.msn.com/svcs/hotmail/httpmail.asp', Protocol: HTTPMail, Server Response: 'Service Unavail...

Re: How can I search for a ? in Publisher?
I just sent my suggestion to the shredder. <G> -- Don - Vancouver, USA "May your shadow be found in happy places." - Native North American "Don Schmidt" <Don Engineer@PNB.Retired_1987> wrote in message news:... >I see you problem; don't know if your search can be done in Publisher but >here's a way of finding the "?s" in your file. > > Save the file as a PDF and do the search in your PDF viewer program. This > works for me using PDF-XChange to create the file and PDF-XChange Viewer > to do the searching....

Data counting
hi there, I need a solution for excel. Sheet1. This worksheet excists of a column DATE where a user can fill in dates. These dates are of the format: dd/mm/yyyy . And a column AMOUNT. Where a user can fill in an amount. This amount is of the format: number, decimal 2 for example: 05/07/2005 | 15,30 12/07/2005 | 34,80 12/07/2005 | 8,50 12/07/2005 | 12,90 20/07/2005 | 5,00 20/07/2005 | 7,50 24/07/2005 | 100,60 What I want to accomplish is the following. Excel has to make a SOM from the amounts per date. and list these SOMs on sheet2. in the format: 05/07/2005 | 15,30 12/07/2005 | 56,20 ...

Excel 2002 annoyance
Greetings List I am using Excel XP amd Access XP I regularly run queries in Access and cut and paste the results into a new Excel spreadsheet. The Access data is usually formatted numeric, fixed, zero decimal places. Once pasted into Excel, it gains 2 decimal places and I get the little green triangle in each cell inviting me to convert to numeric of error check etc. Can anyone think of a way to retain the numeric formatting from the MS Access data? Regards George ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet mess...

How do I not plot items with zero in a data series?
...

how to include in codebehind page?
Hi All, In classic ASP I have a include file that reads all cookies and put in variables to be use, in dot net I also have a ASPX file that reads all the cookies, but I am having problem using the file in the codebehind .vb page. and then I thought I will just create a DLL and put in the bin folder, but I am having problem calling the System.Web.HttpCookie with in the Public Class. Can any point me to the right direction how I can do this? Thanks for help Ray --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -=...

How do I display data from multiple worksheets in a summary sheet.
I have over 100 worksheets which are layout exactly the same. I want to create a summary sheet to display only certain information from all of them...like name, sales total, numbers of calls. I don't want to consolidate or tally the information just display them. Does anyone know how to do this? Many ways to skin this cat =Sheet1!C4 =Sheet1!C4 ... If the sheets will be always be accessible in an open workbook you could use =INDIRECT("Sheet"&ROW()&"!C4") In a macro you could use For i = 1 To 100 Cells(i, 1).FormulaR1C1 = &qu...

Sort numeric data in more than 3 columns in excel
Hi All, I have to sort 12 columns of numeric data in descending order on a excel worksheet. I currently sort 3 at a time and then move forward. For example, I have sales data for months Jan to Dec. I want each of these columns to show zeros at the bottom when the macro is run. The rows would increase in future but the columns would be fixed. The worksheet would also remains unchanged each month. None of these columns are of least significance. Is there a way I could change a recorded macro to achieve this?? I know very little of VB. This would be of great help! Thanks! All of the columns ...

Need help #2
I have a column full of photo names such as 2801.JPG. There are aroun 500+ image names in this column. What I need to do is add "photos/" i front of every image name in this column; e.g. "photos/2801.JPG". I there any way to do this other than one at a time. It would take a ver long time doing it this way. Thanks for your help example: _current_ 2801.jpg 2802.jpg 2803.jpg _What_I_need_ photos/2801.jpg photos/2802.jpg photos/2803.jp -- trima ----------------------------------------------------------------------- trimax's Profile: http:...

Excel Data Integration
I'm trying to manipulate the adjust cost and general journal entry in Great Plains 9.0. I'm succesfully set data in the fields of headers but having difficulty on Scrolling window and also in activating radio buttons. Can anybody have a sample code for adding/deleting/getting data from/to scrolling window and email to me at poaps629@yahoo.com ? And I know there is a certain number key for adding a line /data in scrolling window which starts at 16384 of SQCNLINE and increment itself for every line added. It will be nice if I can also acquire how to use this. By the way, I am usi...

Re: IPM.Post instead of IPM.Note (Exchange Server 2003)
(Apologies for the repost - I types in "ms.p.e.application.conversion twice, and forgot this group) "Steven Wilmot" <steven-news@wilmot.me.uk> wrote in message news:... > Hello, > > could you tell me is there a similar patch for Exchange > Server 2003 as there is one for Exchange Server 2000 > (http://support.microsoft.com/?id=817809) addressing the > issue of "SMTP messages coming to public folders have a > default type of IPM.Post instead of IPM.Note"? > > Thanks a lot, > > Steven. > > ...

Re: this accidental unwanted mail 04-06-09
"agonis shala" <agonis@fahrwangen.ch> schrieb im Newsbeitrag news:... > > "::1" <cookie:default@routerlogin.net> schrieb im Newsbeitrag > news:OlhbUo3fJHA.5540@TK2MSFTNGP06.phx.gbl... >> >> "Joe Mc" <mcrdl76@netscape.net> wrote in message >> news:unTpd3dfJHA.1168@TK2MSFTNGP05.phx.gbl... >>> Ok >>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message >>> news:OMuWD2PSJHA.3584@TK2MSFTNGP06.phx.gbl... >>>> Sorry, but this isn't e-mail. >&g...