match up column A with column B

Hi,

I have a column of numbers in A, and a column of numbers in B.  There
are 180 numbers in A (A1:A180) and 130 numbers in B (B1:B130).  I need
Excel to find which numbers match up between columns, e.g look down
column A and find all the matching numbers in column B, then sort the
columns so the matched numbers are next to each other (so I can see
which numbers don't match between columns). (I have sorted the numbers
from lowest to highest down the column, so they are in numerical
order).

I have no idea how to do this. 
:confused: 

Thanks for any assistance you can provide.  :)


-- 
Micronaut
------------------------------------------------------------------------
Micronaut's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15677
View this thread: http://www.excelforum.com/showthread.php?threadid=375272

0
5/31/2005 3:16:36 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1147 Views

Similar Articles

[PageSpeed] 10

Here's one way:

Insert a blank column B (current B becomes C)

In B1, enter this formula:

=IF(ISERROR(VLOOKUP(A1,$C$1:$C$130,1,0)),"",VLOOKUP(A1,$C$1:$C$130,1,0))

and copy this formula down to B180

Column B will be blank if the value in A is not found.  If the value in
A is found, the matching entry will be returned.

Will this work for you?


-- 
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101
View this thread: http://www.excelforum.com/showthread.php?threadid=375272

0
5/31/2005 3:30:04 PM
swatsp0p Wrote: 
> Here's one way:
> 
> Insert a blank column B (current B becomes C)
> 
> In B1, enter this formula:
> 
> =IF(ISERROR(VLOOKUP(A1,$C$1:$C$130,1,0)),"",VLOOKUP(A1,$C$1:$C$130,1,0))
> 
> and copy this formula down to B180
> 
> Column B will be blank if the value in A is not found.  If the value in
> A is found, the matching entry will be returned.
> 
> Will this work for you?

swatsp0p,

Thanks for the reply, that's worked well.  Is there any way to
highlight in the now column C, which numbers didn't match up?
:)


-- 
Micronaut
------------------------------------------------------------------------
Micronaut's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15677
View this thread: http://www.excelforum.com/showthread.php?threadid=375272

0
5/31/2005 3:43:06 PM
In C1, use Format>Conditional Formatting as such:

Choose Formula Is: =ISERROR(MATCH(C1,$B$1:$B$180,0))  and set
Format>Patterns to your desired fill color (I like Rose :) ) to
highlight cells that don't match in Col. B

Then copy this cell (C1) and Paste Special>Formats in range C2:C130

Does this work for you?


-- 
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101
View this thread: http://www.excelforum.com/showthread.php?threadid=375272

0
5/31/2005 4:01:17 PM
swatsp0p,

Yes, that also worked well.  I tried using the original formula as an
additional conditional format to highlight the matches displayed in
column B, but it's not highleted them all.  There weren't many "hits"
from column B so I sorted them manually from the list generated from
the original formula.

Thanks for your help with the problem, was much appreciated.  Sorted my
problem, literally!  :)


-- 
Micronaut
------------------------------------------------------------------------
Micronaut's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15677
View this thread: http://www.excelforum.com/showthread.php?threadid=375272

0
5/31/2005 4:39:06 PM
I'm glad this worked for you.  Thanks for the feedback, it is always
appreciated.

Cheers!


-- 
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101
View this thread: http://www.excelforum.com/showthread.php?threadid=375272

0
5/31/2005 5:50:07 PM
Reply:

Similar Artilces:

adding an horizontal scrollbar to columns
Hi, I've got a simple excel spreasheet with 10 columns. I'd like to be able to add a scrollbar from the third one to the eighth one so that when I move the scrollbar, columns that do not belong to this group will not move. It's pretty hard to explain, hope this was clear enough. Can someone helps ? Thx If you use Windows > Freeze Panes with your cursor in cell C3 then you can stop columns A and B from moving off the screen when the user scrolls right. I am not sure how you would freeze the ninth and tenth columns though. Hope this helps Rowan "Sam" wrote: > ...

Print Column Headings on multiple sheets
I have a budget that when printed is one page wide and three tall. How can I add the column headings to the second and third pages? click file/page setup and click the sheet tab click the icon next to rows to repeat at top select the rows click the icon again and then ok -- Gary Keramidas Excel 2003 "wx4usa" <wx4usa@gmail.com> wrote in message news:19421c6b-0ea5-4524-8574-8dea96d44bde@22g2000yqr.googlegroups.com... >I have a budget that when printed is one page wide and three tall. How > can I add the column headings to the second and third pages?...

Simultaneously sort 2 worksheets using linked column
I have 1 worksheet (Attendance) that keeps track of attendance. Rows 10 - 37 (27 students) with columns J - DQ for recording daily attendance. I'm designing a 2nd worksheet (Progress) in the same workbook to keep track of progress. Currently, the student names on Progress are linked to those on Attendance. This works fine. However, when I sort Attendance, only the student names on Progress move causing information on this second sheet to no longer correspond to the correct student. Is there a way to sort Attendance and have the data I enter on Progress stay with the names they're...

Removed content in right columns and limited size of spreadsheet
I was trying to get rid of the contents in the columns to the right of my spreadsheet and instead I accidently deleted everything to the right (area is now grey). Now I can't insert new columns because I have limited the size of the spreadsheet. How can I recover columns to the right? I can't just click undo because I have saved and gone out of the spreadsheet. The data in deleted columns is lost I do not understand how you think you have limited the size of the worksheet That is not possible tell us more best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/...

Merge cells in two columns
Hi I have a worksheet with 300 rows Is it possible to merge 2 adjacent columns in one or two steps without having to do each one individually I've just learnt how to merge 2 adjacent cells to include all data in the range in the merged cell column C has the word 'dist' in ever row column D has numbers from 1 to 14 in random order using Excel 2000 Martin ���� >>Is it possible to merge 2 adjacent columns in one or two steps without having to do each one individually Easy, but you have to use a menu choice not shown on the menus by default. Do a View, Toolbars, Custom...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Column Sum in Footer
Hi, I have created a Tabular report that lists names and weights for different people. I want to create a stand-alone field in the page footer that will display the SUM of all the weights on the report. Can somebody please help me? (I'm using Access 2000 btw) I have tried creating a text box with the Control Source property set as "=Sum(weight)". However, this just produces an error. Thanks in advance. Julie Smith wrote: >Hi, >I have created a Tabular report that lists names and weights for different >people. I want to create a stand-alone field in the page f...

Adding column data based on match in 2nd column
Hi, Simple problem, but got me stumped. The setup: I have 2 columns. The first (column A starting in row 10) has members' names. The second (column B starting in row 10) has the additions made by the members over time. Column B has a 'header' cell that adds up the values entered by a particular member. The header cell for column B is B9. Cell A1 contains the member's name that cell B9 has to add the values for. I need a formula for Cell B9. For example: [Cell A1] Member A [Row 9] Formula needed for cell B9 COLUMN A_______|| COLUMN B____ [Row 10] Member...

Sum cells using criteria from a row and a column
I am trying to sum cells in a sheet based on matching criteria in a row and matching criteria in a column. The data that I am working with is represented as: Resource: Resource1 JAN JAN JAN JAN FEB Project Project Description 12/19 12/26 1/2 1/9 1/16 Admin Administration 10 10 10 10 10 Holiday Holiday 8 8 Other Other time Off Training Training 20 Vacation Vacation 22 30 10 30 22 I ...

matching items #2
Peter I am aware of the using filter on what worksheet, however i am being bit basic with what i am trying to achieve. I need the information t be on a seperate worksheet in a completely different arrangemen basically like a report with the information what i need being shown. am strying to show the principle in what i am trying to do with m quaestion. Thanks anyway. Ton -- tony lindsle ----------------------------------------------------------------------- tony lindsley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1499 View this thread: http://www.excelf...

Partial Text Match then enter desired result into appropriate colu
I have a list of company names in Column A. On a separate tab I have a list of keywords. What I'd like to do is to have a search done on the names in column A and if any of the keywords on the separate tab match for it to then put in the company type into column B. Example of a company name in column A: Get Things Done Construction Company Keyword on separate tab: "construction: Need the formula to return "construction" to Column B. Thanks in Advance for any insight on this one! Look in the help index for VLOOKUP -- Don Guillett Microsoft MV...

Help with Column Lables
I am trying to use Filtering in Excel 2003. I highlight the columns I wish to include in my filter. I then select advanced filtering and click ok. When I do I am told that "Microsoft Office Excel can not determine which row in your list or selection contains column labels, which are required for this command. " I select OK and it, by default selects the first row of the selection or list to be used as labels and not data. This is ok but � Just how do I create column labels so that I do not constantly get this prompt. I have looked in help under Creating Labels, Creating Data L...

split a column
Hello! I have a worksheet with only two columns but 1800 records. I would like to print this sheet and use the least paper possible. The solution of course is to divide the two rows in multiple rows and thus filling up the paper. I would howerver like to have the records in alphabetical order on every sheet, so just cutting the rows at row 600, 1200 and 18000 doesn't do. I would very much appreciate any good idea! Thanks in advance! Pete Hi Tokyogroove, See David McRitchie's Snake Columns page at: http://www.mvps.org/dmcritchie/excel/snakecol.htm --- Regards, Norman ...

Excel: printing with hiddent columns
My worksheet with hidden columns in the middle should fit easily on one sheet, but print preview shows a page break after the hidden columns. Apparently the width of the hidden columns was included. How do I get the print setup to ignore the hidden columns when determining page breaks? ...

Adding an apostrophe to a column of numbers
Hello, I need to add an apostrophe to each cell in a long column of numbers. I do not want to format them as text or format them as custom - our system requires the apostrophe at the beginning of the cell. My question is - is there a way to format one cell with the apostrophe and then copy that apostrophe down to the other cells?? or can someone write me something that will allow me to do it quickly? I want to avoid having to go into each cell and put the apostrophe in! Thank you in advance.... One way. Change 9 to your column Sub addapostophe() mc = 9 'column I For i = ...

Make cells match
In sheet 1 I am asking several Yes/No questions. I have a cell after the Yes and after the NO that I want the value "X" entered into. It will of course either be one or the other. I then want that "X" to show up in sheet 2, which is a form I want filled in with the data from sheet 1. If I simply do A1 = C1 and B1 = C2 I get a value of "0" in the cell they didn't put an "X" in. So if the put an "X" in the Yes cell in sheet 1 then an "X" shows up in the Yes cell in sheet 2, BUT since they didn't put anything in the No cell...

From VBA: How to select a column by column number instead of letter? #2
I'm in MS Access and don't know from column letters - all my constants are in terms of column number. I'd like to select, say, column number 7 and hide it.... ? -- PeteCresswell Gord and I sent you a macro solution for EXCEL as this is an Excel ng. -- Don Guillett SalesAid Software donaldb@281.com "(Pete Cresswell)" <x@y.z> wrote in message news:uch701tveuot081n0q0h9rpq1r0c24v1sc@4ax.com... > I'm in MS Access and don't know from column letters - all my constants are in > terms of column number. > > I'd like to select, say, column number...

Index match or Sumif ???
Hi, I need on the following: The Fee Table (FeeTbl) have price by the 4 categories namely: Enrol, Adult, Stdnt & Snr and by year for 2008 to 2011. The Main Table (MainTbl) are number of persons under each categories. The TotFees column are the sumproduct of thenumbers in the MainTbl x the prices in the FeeTbl. For example 48 (in 2008) = 0*10+2*15+2*6+1*6 or 55 (in 2009)= 1*15+2*20+0*6+0*6 What would be the best formula that also does not take too much resource, would be very appreciative. Regards Ed ------------------------------------------------------------ MainTbl...

Money 2004 will not match transaction with my bank
I am using Money 2004 buiness edition. I received payment for three of my invoices and that payment was added to the correct bank register. Then money downloaded the matching transaction from my bank. No matter what I do it will not match the two up (same date, same amount). 1.. Automatic: It adds it as a new cleared transaction so it shows a double depost 2. Manual: I click on the 'unaccepted' bank transaction and try to match it to an existing transaction. When MS money gives me the list of transactions to match it to the 'manually' entered one is not in the list. I even t...

The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the most occurrence in that column? Thanks, cpliu Hi =MODE(A1:A100) -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4... > I have 1 to 5 as values in a column. How can I find the number with the > most occurrence in that column? > > Thanks, > > cpliu Try: =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF (A1:A5,A1:A5),0)) Array-entered, meaning press ctrl/shift/enter. H...

How attach data in a row so it can be sorted by the date column?
I have a four spread sheets all the same that I have copied and pasted into one. I need to know now how to sort the rows by the date column. When I select the date column and hit the tool bar a-z button only the dates sort and not other information in the adjacent columns. What can I do? Hi! Select the entire range that you want to sort. Goto Data>Sort. Sort by the column that contain the dates. Biff >-----Original Message----- >I have a four spread sheets all the same that I have copied and pasted into >one. I need to know now how to sort the rows by the date column. W...

Way to put multiple rows into the same column.
I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. Hi, Try my EastyText_Rev1.xls at http://www.geocities.com/excelmarksway It might work, er, maybe, hmmm... - Mark >-----Original Message----- >I have data that is in the format of 3 columns wide by about 3000 r...

alphabetize a column of words in MS word
can i alphabetize a column of words in MS word In Word 2003 or earlier, use Table > Sort. In Word 2007, click the Sort button on the Home ribbon (the one with an A and Z and an arrow). -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On Thu, 20 May 2010 15:36:01 -0700, Dale <Dale@discussions.microsoft.com> wrote: >can i alphabetize a column of words in MS word ...

Access/Excel: Create multiple Lines/Column out of one mulit-line Column?
Hallo everyone! I spent a lot of time today searching for a response to my question but I had unfortunately no success. The problem is the following: I have got an Access-table called Employee with an ID and one corresponding Field/Column called comment. These comments are stupidly multi-line (separated by a Return). It's my aim to have access (he he, wordplay) to these two fields in Excel whereas the field shouldn't be longer that 255 characters. I considered three solutions: The table could be changed after being exported to excel in the following two ways: - Multiple Comment Column...

Multiple Column Conditional Formatting
Hi, I'm needing some help with performing a multiple column condition formatting. I have tried many "forumula is" in the conditional formatting with no success. I'm missing something. What I would like to have happen is, if the 2 conditions are true then I want the field to be color filled. Example: if the numeric value in k2>10 and the numeric value F2>100 then I would like cell K2 to be filled in red. Many thanks for your help Colleen one way: Select K2: CF1: Formula is =AND(K2>10,F2>100) Format1: <patterns>/Red In article &...