Comparing columns and extracting data

I've got two columns with lots of data in each.  Some unique, some the
same.  Short example:
Col A
cat
cow
dolphin
dog
fish
horse
snake
zebra

Col B
bear
cow
dolphin
dog
fish
hamster
monkey
zebra

I would like to create additional columns with the following
stipulations:

Col C (what's common in both A & B)
Col D (what's in A but not B)
Col E (what's in B but not A)

Any help is appreciated.
0
11/15/2007 11:27:15 PM
excel 39879 articles. 2 followers. Follow

1 Replies
386 Views

Similar Articles

[PageSpeed] 8

These are all array formulas. **Array formulas need to be entered using the 
key combination of CTRL,SHIFT,ENTER (not just ENTER)

> Col C (what's common in both A & B)

Assuming your data starts on row 2. Enter this array formula** in C2 and 
copy down until you get #NUM! errors meaning all data has been exhausted:

=INDEX(A$2:A$9,SMALL(IF(ISNUMBER(MATCH(A$2:A$9,B$2:B$9,0)),ROW(A$2:A$9)-MIN(ROW(A$2:A$9))+1),ROWS(C$2:C2)))

> Col D (what's in A but not B)

Assuming your data starts on row 2. Enter this array formula** in D2 and 
copy down until you get #NUM! errors meaning all data has been exhausted:

=INDEX(A$2:A$9,SMALL(IF(ISNA(MATCH(A$2:A$9,B$2:B$9,0)),ROW(A$2:A$9)-MIN(ROW(A$2:A$9))+1),ROWS(D$2:D2)))

> Col E (what's in B but not A)

Assuming your data starts on row 2. Enter this array formula** in E2 and 
copy down until you get #NUM! errors meaning all data has been exhausted:

=INDEX(B$2:B$9,SMALL(IF(ISNA(MATCH(B$2:B$9,A$2:A$9,0)),ROW(A$2:A$9)-MIN(ROW(A$2:A$9))+1),ROWS(E$2:E2)))

-- 
Biff
Microsoft Excel MVP


"thermometer" <thermometer98@gmail.com> wrote in message 
news:52c58d8a-4e88-43b1-acc2-2875de228c14@o6g2000hsd.googlegroups.com...
> I've got two columns with lots of data in each.  Some unique, some the
> same.  Short example:
> Col A
> cat
> cow
> dolphin
> dog
> fish
> horse
> snake
> zebra
>
> Col B
> bear
> cow
> dolphin
> dog
> fish
> hamster
> monkey
> zebra
>
> I would like to create additional columns with the following
> stipulations:
>
> Col C (what's common in both A & B)
> Col D (what's in A but not B)
> Col E (what's in B but not A)
>
> Any help is appreciated. 


0
biffinpitt (3172)
11/16/2007 4:37:38 AM
Reply:

Similar Artilces:

How to manipulate Outlook data from MS Access
When the Calendar folder is used as a linked table in MS Access, only some fields are shown. Is there a way to have them all? The linked table method has major limitations, including the inability = to show even all the reasonably important fields. While articles have = been written on how to expand on this method's obvious features, I've = never been able to duplicate the results. See = http://www.outlookcode.com/d/database.htm#linkedtables .=20 That page will also give you other ideas on how to work with databases = and Outlook together. You'll need to write code or use a t...

You should be able to include Budget data in canned reports
When extracting data from Great Plains either on a report like a trial balance or through SmartList you should be able to include in the report specifications a budget. Everything we do with analysis of the numbers is geared towards measuring actual against budgets and forecasts, yet we cannot extract the data on one report or in one place. The exception is the Budget Versus Actual Screen, and if you are extracting for a department with 35 accounts this screen just doesn't cut it. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions ...

Data input OK but form not dispalying information back
Hi I have created various forms and tables successfully but suddenly I have one form which is not behaving as i would expect. When I enter data into this form, it appears to have been saved in the table, however, when I exit the form and then go back in to check the data, it is no longer displayed. I am new to access and have been teaching myself as I go along. Obviously I am missing someting here. Can you help me please? Many Thanks-- Tash It is possible you may have changed the data entry properties for the form. I think that would be unlikely for a beginner. Check the properties ...

Allowing data in specific forms
Hi Is it possible to only allow data of a certain format to be entered in to a cell. For instance a UK national insurance number that always has the same format of two letters followed by six numbers and then either A, B, C or D i.e YB123456A. Many thanks for your help Luke One play which might suffice .. Set-up a defined columnar range in say, X1:X4 --------------- Input in X1:X4 the letters: A, B, C, D Select X1:X4, and click inside the namebox (the droplist just to the left of the equal sign) and type: List (Note that the defined range: List can be set-up on another sheet) Supp...

Subform not showing all data
I have a subform that the agent can enter comments in regarding the owner's request. I have set it up so that there is a history of comments. The matching links are a home-grown key that will not change for a request, the specialist, and an option that is chosen by the operator. My problem is I want all comments to be shown but I auto-populate the option and specialist from the Main Form for any new comments. Ideas? On Thu, 27 Sep 2007 10:43:06 -0700, Bunky <Bunky@discussions.microsoft.com> wrote: >I have a subform that the agent can enter comments in regarding the owne...

Excel Data List Gets "Stuck" -- Help
This has now happened for the second time. I have a "data list" that is around 1800 rows in size. Somcetimes, after I've filtered the data to show just a small number of rows, it gets "stuck." I I am no longer able to expand the list to show all the rows. I've gone in to all the columns and chosen "All" for all the columns and it still does not work. I know I can highight the entire list and tell it to remove the list and recreate the list, but that's a pain, and I lose some formatting. Can anyone help me? -- slobizman -----------------------...

Compare two tabs and only show exceptions
Hello, Does anyone know how I can compare two seperate tabs of data and show only the exceptions on a seperate tab? I am comparing data on two tables and only need to know which ones don't match. Thanks, Cell by cell? A1 with A1, Q12 with Q12, AC232 with AC232? If yes, then you could use a program written by Myrna Larson and Bill Manville. You can find a copy on Chip Pearson's site: http://www.cpearson.com/excel/whatsnew.htm look for compare.xla Rich wrote: > > Hello, > Does anyone know how I can compare two seperate tabs of data and show > only the except...

Transfer data
I don't get an error but it also does not update my table!! This code should create a new record in table: tblQualityProvider then copy ICNNo into the new record. (The GetNewID comes from a public function - I pasted it below this code). Private Sub cmdProv_Click() Dim gappname As String Dim lCriteria As String Dim lICCNNO As String Dim lRacfid As String lICCNNO = Me!ICNNo If MsgBox("Any Message") = vbYes Then DoCmd.SetWarnings False Dim lID As Long lID = GetNewID("tblQualityProvider") lCriteria ...

can my chart update itself if i add data?
i need my chart to automatically update if i add columns. i start everyday with 1 column and get to around 350 a day. i need to be able to have my chart update with each addition so that i don't have to change my chart series 350 times a day. thanks in advance for your help. -- caldolegare ------------------------------------------------------------------------ caldolegare's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26773 View this thread: http://www.excelforum.com/showthread.php?threadid=400307 I don't quite know how you add 350 columns to a wor...

I need to move my CRM data files
I am running out of disk space on my CRM server. I want to move the database files (mdf and ldf) to a second drive. My normal procedure would be to shut down the application (CRM), detach the files from SQL Server, move them to the new location and then attach them and restart CRM. Will this suffice for CRM? Or is there something else I need to consider? You'll want to use the redeployment tool supplied by Microsoft for this. It should make everything go much more smoothly: http://www.microsoft.com/downloads/details.aspx?FamilyID=BFCED393-61DB-49AF-9A50-4A90B311FA7D&displaylang...

Month in report header based on dates in data table
I am creating a report based on a table containing transactions for various days of a month. I would like to have the appropriate month print on the report. For example, my data may have transactions for May 2, 6, 27, and 30 and I'd like the report to print "May 2007". Can anyone tell me how to do this? Many thanks in advance, TomD On Fri, 11 May 2007 09:39:02 -0700, TomD wrote: > I am creating a report based on a table containing transactions for various > days of a month. I would like to have the appropriate month print on the > report. For example, my data m...

Printing wide columns
Hi all I have a spreadsheet that I want to print. It's only 2 columns, but the second column is very wide. I seem only able to print the first column. However if I reduce the width of column B to 88 or less it prints both columns. is this usual? I'm running XP. Thanks Really would depend on how big the paper your printing on to is. Print Preview. Page Setup Then adjust to 1 by 1 or % of actual size to fit onto how ever many pages you want. "Richard" wrote: > Hi all > > I have a spreadsheet that I want to print. It's only 2 > columns, but the seco...

How do I link data between worksheets within a workbook?
I want to pull data from one worksheet to use in a formula in another worksheet. todo4u, just ref. the sheet name, like this, in a cell in sheet1, will add whats in sheet1 and sheet2 cell A1 =A1+Sheet2!A1 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "todo4u" <todo4u@discussions.microsoft.com> wrote in message news:7F287A45-B19F-4AC1-A2F4-285ED945B28C@microsoft.com... > I want to pull data from one worksheet to ...

Cursor over data point on Excel Chart
For an x-y graph in Excel where the x values are dates and the y values are stock data, I have formatted it so I see the dates along the horizontal x axis. All good so far, but when I place my cursor pointer over a data point, it flashes up the numeric value for the date, then the price point like this: (37934, 1254) I wonder if there is a formatting technique to force it to show the date in date format. It would be easier for me to identify a particular date since I space out the date labels along the axis and can't determine with precision the exact date of a point I've placed th...

Data Migration Pack
Does anyone know if there is any documentation out there re: the data migration pack for v3? I need to understand what the limitations are (if any) - can you import relationships between accounts and contacts via the customerrelationshipbase table? Can you import notes and opportunities? Thanks Jo Savidge Hi Jo, If you start the import wizard in DMF and choose advanced view you'll see exactly what you can import. Further have a look at the available tables in the cdf database, if there's not an entity in this database for the info you want to import it cannot be imported. Th...

Merging Workbook Table data Based upon Value comparisons
I have two workbook tables (Two different workbooks) with two matching column names. What I wish to do is to merge values from one table to another, but ONLY for those records inwhich these two columns have matching values. Would this be possible? Jay Are you saying you have two workbooks, or are the tables within one workbook? "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:AC73B2C7-83EF-4D27-A464-32AEEE7D4214@microsoft.com... >I have two workbook tables (Two different workbooks) with two matching >column > names. What I wish to do ...

Total columns Based on another cells value
Good Day, I trying to use VBA to calculate totals in multiple columns when it see uniques values in another Column..in the example below column "A" contains user names while columns "B, C, D" contain the values I want totaled based on the user name in column "A"...An additional thing would be have it place the values with the user name on a new worksheet. Col A Col B Col C Col D John Smith 1.0 1.0 1.0 John Smith 1.25 1.25 2.0 Mary Johnson .75 1.0 2.5 Jack Johnson ...

how do I extract the last three digits from each cell in column?
I am using excel 2007. Each cell in a column contain varied length of data but ends with "Program ###". I would like to extract ### into an adjacent column for all cells in the first column. Thanks! PeggyT =RIGHT(A1,3) and copy down Vaya con Dios, Chuck, CABGx3 "PeggyT" <PeggyT@discussions.microsoft.com> wrote in message news:0893007D-2776-42CD-942F-16A5B31CB54C@microsoft.com... >I am using excel 2007. Each cell in a column contain varied length of data > but ends with "Program ###". I would like to extract ### into an adjacent ...

Add column "accounts" to activities
Hello there, I'm using the Sales-for-Outlook and want to monitor if all colleagues are using the program correctly. One of the things I've got to look at is whether they link a CRM-activity to an account in the Regarding-field. As far as I can see, the only way to do this is by clicking on each activity and look whether the Regarding-field contains (the right) items. It would be much easier if I could add a column, which contains the accounts filled in in the Regarding-field, to the total activity-list. Does anybody know if this is possible and if so, how can I do this? OK, tha...

ReportViewer: A data source instance has not been supplied for the data source
HI All, I am trying to use a reportviewer control on my Web page to display some reports. I wanted to use the same control to display more than one reports. But when I have a preSelected report every thing works fine. but when I change the report path programmatically I am getting this error. "A data source instance has not been supplied for the data source 'myreport1'" where myreport1 is the Dataset name I have used in my report. Looks like the code is able to read my report but the datasource is not set for the report properly. Please help. than...

Summing across columns while skipping some columns
Does anyone know of an easy way to accomplish this? In the example below, I do not wish to add the values in columns G18 and J18. =SUMIF(F18:L18,"<>#N/A",F18:L18) Thanks in advance for any replies. Joe How about =SUMIF(F18:L18,"<>#N/A")-SUMIF(G18,"<>#N/A")-SUMIF(J18,"<>#N/A") Note the third argument is not needed since you are summing the same range as you are testing. best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters "JAG" <JA...

Extract Data from Lotus Notes ? ? ?
Hi All I have been manually exporting a view from Lotus Notes and then using a macro to manipulate it. What I would now like to do is extract this directly from Notes into a excel workbook [using ADO?] Can anyone advise on the best method, site, source code or examples etc. Many Thanks in Advance John ...

Rename Columns
Hi All, I was wondering how you rename the column headings from A, B, C to my own choosing. Thanks in advance Hi, You cannot I'm afraid. WHat you should do is type your headings into the top row. Regards, Jan Karel Pieterse Excel MVP >-----Original Message----- >Hi All, >I was wondering how you rename the column headings from A, >B, C to my own choosing. > >Thanks in advance >. > Hi You can't - you only can switch to R1C1 style (column numbers are displayed) and back to column labels. When you want this for better lookout only (without any functional...

formatting problem on imported data
I have a column that I set a custom format. On the data that is imported in, some cells are formatting according to the custom format, while other cells do not. On the cells that do not, I noticed that if I click in the formula bar, then click away, the formatting in the cell will change to the custom. So I have 2 questions - why is this happening, and is there an easy way to correct the whole column without having to click in the formula bar for each cell that is incorrect. Using Excel 2002. Lynn This is normally because the data coming in is seen as text by Excel and it tries t...

Sales via Handheld / Portable Data Terminal
I have a need for a handheld device with which to do sales. Ideally the salesperson could simply carry the device, scan barcodes, and then sync or upload the scans into RMS invoice. Is there any easy way to do this? I have seen the products from newestech.com but they are a bit expensive. Could we use a Metrologic Scanpal or something similar without custom software? Please advise. Thanks in advance! Kevin wrote: > I have a need for a handheld device with which to do sales. Ideally the > salesperson could simply carry the device, scan barcodes, and then sync or > upload ...