Extracting rows from a file based on another

Hi,

I have 2 excel files where on is a subset of the other. All rows are
uniquely identified by a number. I would like to automatically extract the
rows in the subset from the superset, resulting in a 3rd file with the
difference between the two, like a batch operation. Do I need a utility for
this or is there an option in Excel that allows me to do this?

Cheers, Thorbjorn


0
please8308 (15)
5/21/2004 8:52:58 AM
excel 39879 articles. 2 followers. Follow

4 Replies
409 Views

Similar Articles

[PageSpeed] 22

Hi
I posted you a links as starting point in the German 
newsgroup. Please post back if you have fruther question 
(best would be with some example rows in plain text - no 
attachment please)

>-----Original Message-----
>Hi,
>
>I have 2 excel files where on is a subset of the other. 
All rows are
>uniquely identified by a number. I would like to 
automatically extract the
>rows in the subset from the superset, resulting in a 3rd 
file with the
>difference between the two, like a batch operation. Do I 
need a utility for
>this or is there an option in Excel that allows me to do 
this?
>
>Cheers, Thorbjorn
>
>
>.
>
0
frank.kabel (11126)
5/21/2004 10:48:42 AM
Thanks for the link. I use Excel 2003 English. I am getting an error message
using the formula =IF(COUNTIF($A$1:$A$10,B1)=0,B1,""), it is reported as
Invalid. I have rearranged my data to correspond to the example to eliminate
errors, and also tried to enter the formula with ctrl+shift+enter.

Example A1-A10:

      Peter
      Paul
      Mary
      Melissa
      James
      John
      Oscar
      Wilfred
      Henner
      Ivar


Example B1-B10:
      James
      Paul
      Mary
      Melissa
      Avery
      John
      Oscar
      Henrik
      Henner
      Ole


In C1, I enter the formula: =IF(COUNTIF($A$1:$A$10,B1)=0,B1,"") getting
error message that formula is invalid. In my real example, I also need to
expand the ranges into more columns since I need to include address data as
well. Would appreciate any help.

Cheers, Thorbjorn

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:101d201c43f21$2e8e87e0$a601280a@phx.gbl...
> Hi
> I posted you a links as starting point in the German
> newsgroup. Please post back if you have fruther question
> (best would be with some example rows in plain text - no
> attachment please)
>
> >-----Original Message-----
> >Hi,
> >
> >I have 2 excel files where on is a subset of the other.
> All rows are
> >uniquely identified by a number. I would like to
> automatically extract the
> >rows in the subset from the superset, resulting in a 3rd
> file with the
> >difference between the two, like a batch operation. Do I
> need a utility for
> >this or is there an option in Excel that allows me to do
> this?
> >
> >Cheers, Thorbjorn
> >
> >
> >.
> >


0
please8308 (15)
5/21/2004 12:48:54 PM
Hi
do you use eventually the semicolon as delimiter. If yes try
=IF(COUNTIF($A$1:$A$10;B1)=0;B1;"")

--
Regards
Frank Kabel
Frankfurt, Germany

"Thorbjorn Sundboe" <please@replytonewsgroup.com> schrieb im
Newsbeitrag news:c8ktrm$n0b$1@paperboy.Austria.EU.net...
> Thanks for the link. I use Excel 2003 English. I am getting an error
message
> using the formula =IF(COUNTIF($A$1:$A$10,B1)=0,B1,""), it is reported
as
> Invalid. I have rearranged my data to correspond to the example to
eliminate
> errors, and also tried to enter the formula with ctrl+shift+enter.
>
> Example A1-A10:
>
>       Peter
>       Paul
>       Mary
>       Melissa
>       James
>       John
>       Oscar
>       Wilfred
>       Henner
>       Ivar
>
>
> Example B1-B10:
>       James
>       Paul
>       Mary
>       Melissa
>       Avery
>       John
>       Oscar
>       Henrik
>       Henner
>       Ole
>
>
> In C1, I enter the formula: =IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")
getting
> error message that formula is invalid. In my real example, I also
need to
> expand the ranges into more columns since I need to include address
data as
> well. Would appreciate any help.
>
> Cheers, Thorbjorn
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:101d201c43f21$2e8e87e0$a601280a@phx.gbl...
> > Hi
> > I posted you a links as starting point in the German
> > newsgroup. Please post back if you have fruther question
> > (best would be with some example rows in plain text - no
> > attachment please)
> >
> > >-----Original Message-----
> > >Hi,
> > >
> > >I have 2 excel files where on is a subset of the other.
> > All rows are
> > >uniquely identified by a number. I would like to
> > automatically extract the
> > >rows in the subset from the superset, resulting in a 3rd
> > file with the
> > >difference between the two, like a batch operation. Do I
> > need a utility for
> > >this or is there an option in Excel that allows me to do
> > this?
> > >
> > >Cheers, Thorbjorn
> > >
> > >
> > >.
> > >
>
>

0
frank.kabel (11126)
5/21/2004 1:01:51 PM
Thanks a lot, that did the trick for me! Sch�nes Wochenende aus Wien.

Cheers, Thorbj�rn

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OglNxOzPEHA.2132@TK2MSFTNGP11.phx.gbl...
> Hi
> do you use eventually the semicolon as delimiter. If yes try
> =IF(COUNTIF($A$1:$A$10;B1)=0;B1;"")
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Thorbjorn Sundboe" <please@replytonewsgroup.com> schrieb im
> Newsbeitrag news:c8ktrm$n0b$1@paperboy.Austria.EU.net...
> > Thanks for the link. I use Excel 2003 English. I am getting an error
> message
> > using the formula =IF(COUNTIF($A$1:$A$10,B1)=0,B1,""), it is reported
> as
> > Invalid. I have rearranged my data to correspond to the example to
> eliminate
> > errors, and also tried to enter the formula with ctrl+shift+enter.
> >
> > Example A1-A10:
> >
> >       Peter
> >       Paul
> >       Mary
> >       Melissa
> >       James
> >       John
> >       Oscar
> >       Wilfred
> >       Henner
> >       Ivar
> >
> >
> > Example B1-B10:
> >       James
> >       Paul
> >       Mary
> >       Melissa
> >       Avery
> >       John
> >       Oscar
> >       Henrik
> >       Henner
> >       Ole
> >
> >
> > In C1, I enter the formula: =IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")
> getting
> > error message that formula is invalid. In my real example, I also
> need to
> > expand the ranges into more columns since I need to include address
> data as
> > well. Would appreciate any help.
> >
> > Cheers, Thorbjorn
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:101d201c43f21$2e8e87e0$a601280a@phx.gbl...
> > > Hi
> > > I posted you a links as starting point in the German
> > > newsgroup. Please post back if you have fruther question
> > > (best would be with some example rows in plain text - no
> > > attachment please)
> > >
> > > >-----Original Message-----
> > > >Hi,
> > > >
> > > >I have 2 excel files where on is a subset of the other.
> > > All rows are
> > > >uniquely identified by a number. I would like to
> > > automatically extract the
> > > >rows in the subset from the superset, resulting in a 3rd
> > > file with the
> > > >difference between the two, like a batch operation. Do I
> > > need a utility for
> > > >this or is there an option in Excel that allows me to do
> > > this?
> > > >
> > > >Cheers, Thorbjorn
> > > >
> > > >
> > > >.
> > > >
> >
> >
>


0
please8308 (15)
5/21/2004 1:18:13 PM
Reply:

Similar Artilces:

unable to display #####.ost
Have problems using Outlook 2003. Periodically I get "unable to display the folder. The file ####.ost could not be accessed." Just recently I've got unable to display the folder. "The file ###.ost could not be accessed because another workstation has accessed it. close and restat any web-enabled applications." What could be causing this? the ost is being kept on a NT 4 file server running server protect. my local PC is running w2k and trend antivirus. Sukh You shouldn't be storing your OST on a network share. MS recommends against it since other processes could...

Shading Header / Footers and separating all rows with lines; weird whitespace
Hey All, I'm trying to create a simple report with no grouping and only a grand total line at the end. I placed a line control at the bottom of the detail section and the report footer section. This causes the whole report to appear as if the top/bottom borders were set in excel. Next I want to shade the grand total line. When I do this there is a 1 pixel (I think) line of whitespace between the line that ends the last detail record and the shading of the grand total (report footer). How can I remove this extra whitespace? I've tried setting CanShrink to yes. I've ...

CInternetFile::GetLength() returns incorrect file length.
Using GetLength() on a CInternetFile returned by CFtpConnection::OpenFile returns an incorrect value. I got arround this using CFtpFileFind::FindFile CFtpFileFind::FindNextFile CFtpFileFind::GetLength Is this a known bug (design feature)? Further info: Adding a delay between OpenFile and GetLength returns a slightly larger file size but only upto a max size of 8192 bytes. ...

Sum of common rows
I am trying to find an automated way of summing common row data. For example: ORIGINAL DATA Row1 123 John Smith Row2 456 John Smith Row3 678 John Smith Row4 324 Dave Jones Row5 678 Dave Jones DESIRED DATA Row1 1257 John Smith Row2 1002 Dave Jones I have several thousand rows and don't want to write something that is specific to the data that appears in the second column. Any ideas? Try this Row 1(col A and B) must have a header like Number and Name It will make a Unique list in Col C and the sum in Col D Sub test() Dim LastRow As Long With Sheets...

Outlook pst files
I once created a pst file on a floppy (A:)that I need temporarily. On load, Outlook returns an error message "There is no disk in the drive. Please inseert a disk into drive A:" How do I stop this error message? I do not use anything on A: with Outlook. Tks, Bob ...

Easiest way to insert multiple copied rows?
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Currently, I copy a row, right click and choose insert copied cells. <br> This works great but only works for copying one row below. <br><br>What if I want to copy 2 or 3 rows below? <br><br>I can find blank rows, copy them, and insert them and then do this, but that is real tedious. <br><br>I wish there was a was to simply copy a row and hit some keystroke which would copy that row right below as many time as you click the keystroke. Does this exist? thanks! Try clic...

Updating 1 worksheet with result from another worksheet
I have a worksheet that I use for a weekly summary. The other worksheets contain a breakdown of what was done. All worksheets are located in the same workbook. I would like to add a formula to the breakdown worksheets that will update the summary worksheet based on a result. For example, if the result is greater than 95% I want to update the cell in the summary worksheet that goes with 95%. Everytime a result meets this criteria I want to update the summary worksheet cell by 1. Summary worksheet 0-5% 5-10% 10-25% 25-50% 50%+ Breakdown sheets Accuracy 95 <- update 0-5% by 1 98 <- ...

cannot see rows
I don't know what happened. I did something and now I'm not able to see rows between 350 to 630, i.e. I cannot see or I lost data on the rows between 350 & 630. Any ideas. I appreciate your help. Thanks William william wrote: > I don't know what happened. I did something and now I'm > not able to see rows between 350 to 630, i.e. I cannot see > or I lost data on the rows between 350 & 630. Any ideas. I > appreciate your help. Thanks William Select rows 349 till 631, right click somewhere in your selection, and select unhide in the popup menu. See i...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

MDI App will no longer open file by double clicking associated filetype
OK, I had an app which was originally based upon an MDI app. It has been 'modded' to add various functionalities with code from www.codeguru.com and www.codeproject.com. Unfortunately, I just recently noticed that the ability to double click on the associated filetype (from a folder, desktop, etc) now no longer works. Instead, I get a messagebox: Title: (pathname of file) Message: Windows cannot find '(pathname of file).' Make sure you typed the name correctly, and then try again. To search for a file, click the Start button, and then click Search. This is on Windows XP. ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

Backup & Retreive Outlook 98 .pst files???
How do I backup and retreive Outlook 98 .pst files? Please, Please give me step by step instructions for both. Thank you, thank you, thank you!!! glen@ccim.net Better yet, read it for yourself and post back if you have any questions: http://www.slipstick.com/config/backup.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, glenf asked: | How do I backup and retreive Outlook 98 .pst files?...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

how do i form 2 lines in 1 row?
I'm guessing you're looking to force text in a cell to wrap into the next line of the same cell. If that's true...... Edit the text and move the insertion point to where you want to wrap the text. Hold down the [Alt] key and press [Enter] Does that help? ---------------------------------- Regards, Ron Microsoft MVP (Excel) "MSEXCELROOKIE" <MSEXCELROOKIE@discussions.microsoft.com> wrote in message news:951AA059-1613-4A62-AAA6-7890AF6F84A6@microsoft.com... > In one cell type this is line one then Alt + ENTER then type this is line two. The Alt ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Page Break Separator Row
I have a page/section/detail layout with tables that may or may not repeating and continue across pages. I've followed Chris Hay's hack to get the "continued..." at the top of the next page but the report design requires a blank row immediately after the last detail row before it continues to the following page. The blank row will then close the table with a border. Is this possible? I have made countleess attempts. Thanks in advance ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Export/Import Acces Table to another Acces table/file
Hi, Is anybody can help me how to export/import table to a new table/file only using 1 click?. I am sure, we'll use Visual Basic, but I don't know how. Thanks in advance, I am appreciated it. KT -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 I suspect you will need to look at the TransferDatabase method of the DoCmd object. See the Access help. Steve "KimTong via AccessMonster.com" wrote: > Hi, > > Is anybody can help me how to export/import table to a new table/file only > using 1 ...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

I can not email excel files
I am using xp- I can"t email a excel file? newbe wrote: || I am using xp- I can"t email a excel file? Umm what happens when you try? -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk And what are you doing to try?? -- Don Guillett SalesAid Software donaldb@281.com "newbe" <newbe@discussions.microsoft.com> wrote in message news:4EBA3AC3-F379-4973-AB64-FBA9FD7F43A5@microsoft.com... > I am using xp- I can"t email a excel file? i was trying to e mail right out of excel but it would not work. I found out...

how do I import pages from one publisher file to another
I am trying to merge two documents made with publisher into one document. I am finding this entire process frustrating What version Publisher? There is this for 2007 Publisher Tools PubCat v1.0.0.0 http://ed.mvps.org/Static.aspx?=Publisher/tools -- Mary Sauer http://msauer.mvps.org/ "AKB" <AKB@discussions.microsoft.com> wrote in message news:B41D7855-F0CF-47A3-B215-5D3B0B4D5542@microsoft.com... >I am trying to merge two documents made with publisher into one document. I > am finding this entire process frustrating A p.s. I have a convoluted way to combine 2003 d...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...