formula to compare columns?

Hello

Does anyone know how to write a formula that compares two adjacent columns of names and lists those names that aren't exact matches in a blank column. The columns of names are in ascending order and the quantity of names in each column may be different

Thanks

btk
0
anonymous (74722)
4/2/2004 9:31:07 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
589 Views

Similar Articles

[PageSpeed] 26

Hi
enter the following in C1
=IF(COUNTIF($A$1:$A$1000,B1)>0,B1,"")
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany


btk wrote:
> Hello,
>
> Does anyone know how to write a formula that compares two adjacent
> columns of names and lists those names that aren't exact matches in a
> blank column. The columns of names are in ascending order and the
> quantity of names in each column may be different.
>
> Thanks,
>
> btk

0
frank.kabel (11126)
4/2/2004 9:54:35 PM
Hi Frank,

I was wondering how this formula could be modified to compare column
of names from two different workbooks and list the items that aren'
exact matches on a new sheet. The columns are in alpha order and th
quantity of names in each column may vary from month to month. 

Thanks for any help you can provide. 

Pa

--
Message posted from http://www.ExcelForum.com

0
4/5/2004 1:13:13 PM
Hi
quite similar:
- adapt the ranges
- filter the results and create a new sheet.
See
http://www.cpearson.com/excel/duplicat.htm#InOneNotOther

--
Regards
Frank Kabel
Frankfurt, Germany


> Hi Frank,
>
> I was wondering how this formula could be modified to compare columns
> of names from two different workbooks and list the items that aren't
> exact matches on a new sheet. The columns are in alpha order and the
> quantity of names in each column may vary from month to month.
>
> Thanks for any help you can provide.
>
> Pat
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/5/2004 1:51:46 PM
Hello again,

My formula: 

=IF(COUNTIF([File.xls]sheet1!$P$2:$P$3000,'sheetA'!A1)=0,'sheetA'!A1,"")

How do I adjust my formula to omit blank lines from the output list? 
know filtering can clean it up, but I'm wondering if there is a way t
supress blank rows altogether...

Can I output multiple columns too?  In addition to A1, I would like t
include B1 and C1. I can't seem to get the formula syntax correct. 



Thank you once again. 

Pa

--
Message posted from http://www.ExcelForum.com

0
4/5/2004 6:21:06 PM
Reply:

Similar Artilces:

Sorting or aligning columns
I am trying to sort two columns so the data in both of them line up. For example column a will have a, b, c, d, e, f and so on in it. Column B will have a, c,e, f, but missing some of the letters that are in a. How do I line up both of these columns so a lines up with a and b lines up with b and so on. Where there is not a match in both it leave a blank in the second column. Thank You For Your Help Jeremy I saved this from a previous post. Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim ColA As Range Dim ColB As Range ...

Return Value in Column 2 Q
I have a table which contains 2 columns and 30 rows of Data Col1 = Sales Col2 = ManHours I wish to "lookup" a value that is input into A1 (which will be a sales value) and Return the appropriate ManHours associated with these sales. My only problem is that my value in A1 will probably not match exactly what is in Col1. For example Col1 increments in 100's, so it will show sales of 9,000, 9,100, 9,200 etc Opposite these Sales values will be Manhours, say, 50, 55, 58 etc Cell A1 might contain 9,040, thus the value I want returned is 55. Likewise if A1 = 9,025 this should al...

Formula to print a datum in color contingent on value.
I need a formula that will change the displayed color of a value based on amount. For example: under 200=yellow, over 200=red. Also, can I add input from a third column? For example: under 200 and Column D=No, then yellow. under 200 and column D=yes, then green. Try conditional formatting for the cells Click on /scroll over cells you need to format FORMAT>CONDITIONAL FORMATTING "Shadyhosta" wrote: > I need a formula that will change the displayed color of a value based on > amount. > For example: under 200=yellow, over 200=red. > Also, can I add input from a thi...

how to get data automatic moves to next column?
I am using a barcode scanner to read in barcode numbers into excel. But everytime after I scan a barcode, excel moves to the cell in the next row. What I want is moves down to the next column. Anyone with information please help. Thankyou very much! Hi, Maybe there is way of setting up Excel and/or the barcode scanner such that the values go to successive columns rather than rows. However, the following formula may be a workaround for your current setup. If the scanned data are going to, say A1, A2, A3, ......, and you want them to go to A1, B1, C1 ...., enter the following formula...

how to totalize only column value from certain accounting -> Currency format
For a sheet with invoices I need to totalize column values from a different currency (cell format Accounting). How to perform? All cells can have any currency USD or EUR. Column A Column B: USD 5,000.00 EUR 3,765,00 USD 100.00 Total USD 5,100.00 Total EUR 3,765.00 Thank you, Bart Excel 2003 =SUMPRODUCT(--(A1:A3="USD"),(B1:B3)) similar for euros -- Gary''s Student - gsnu2007c "AA Arens" wrote: > For a sheet with invoices I need to totalize column values from a > different currency (cell...

How to replace column letter in refferences with a function using the old column letter?
Hello How to replace column letter(s) (or column numbers) in refferences with a result of a function using the old column letter(s) (or column numbers)? I think you mean =INDIRECT(A1&7) where A1 holds the letter in this instance. -- HTH Bob Phillips "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl... > Hello > How to replace column letter(s) (or column numbers) in refferences with a > result of a function using the old column letter(s) (or column numbers)? > > Thanks for your reply. How to change...

comparing values
Hello, I would like to compare values in a excel spreadsheet. I want to have it compare two columns that would be in scrambled order. For example: If anything in column A equals anything in column B then place a check in column C. Here is an example of what it could look like. I appreciate any suggestions. 1 2 2 3 X 3 4 X 4 X Thanks Tom Tom, Put this formula in C1 and copy down =IF(NOT(ISNA(MATCH(A1,B:B,0))),"X","") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct)...

Delete data from one column only
I have a table with 8 columns lets say (A-H) there is data in these columns consisting of 128+ rows. All I need to do is delete the information in column "A" only while keeping the data stored in columns ( B-H). I would like to do this by adding a button on the form is possible. Thank you First things first ... BACKUP! Now, create a new query in which you update the value of that field/column (by the way, Access is NOT a spreadsheet -- you'll want to stop thinking about "columns A-H"!). You can add a command button to your form that runs that update query. No...

Average Formula
I'm trying to find the average of bulletins used each Sunday. I'm keeping track of how many I produce each Sunday and how many are left. Basic table... Week Bulletin Amt. Bulletins left Week 1 100 34 Week 2 100 17 Week 3 110 20 I'm keeping track for the year (52 weeks) What kind of formula would I use? Thanks, wiersma7 You would add another column titled "Bulletins Used", calculated with: =c2-b2 Then average column D, as in: =average(d2:d4...

How do I incorporate 2 columns into 1 column
All, My issue is that I have 2 columns of data, Column A and Column B. What I would like to do is combine these 2 columns into 1 that would result in something like this: A1 B1 A2 B2 A3 B3 etc There are 1000+ entries in each column and I have tried creating a 3rd column plugging into the cells the following, hoping to create the ability to click and drag and fill the rest of the relative values: =A1 =B1 =A2 =B2 =A3 =B3 etc However, when I click and drag, I am not able to keep the pattern going. It always seems to revert to something like this after I try to click and drag the fill box:...

How do I reposition a column on an excel spreadsheet
I want to move column J with data over to become column B. How to do this? Select Col J. Right click>Cut Select ColB. Right click>Insert Cut Cells -- Jacob "JonesALewis" wrote: > I want to move column J with data over to become column B. How to do this? Jacob's was is a good way of doing it. Here is another. Highlight the column. Hold down the Shift Key and Drag it to column B and Drop it. Note that when you are holding the shift key and drag you get an I Beam to indicate where you are about to drop the row. If you do not hold down the Shift key...

Coping formulas to new workbook
Is there a way to copy formulas from one workbook and them paste it to another workbook without it having a link in the formula to the previous workbook? This is a formula I copied but all I need is the last part that said =INVENTORY$d$177 not the link to another workbook ='C:\Documents and Settings\Desktop\SONIC REPORTS\IDEALS\aug ideals\[XDQ IDEALS 2005 rev0805.xls]INVENTORY'!$D$177 -- Jim Salyer Area Supervisor Home: 505-474-4863 Cell: 505-670-4138 Fax: 505-474-4540 Email: jims01@comcast.net I like to do this: Select all the cells. edit|replace what: =...

INDEX/MATCH Formula?
Hi, I need help with a formula. My data looks like this: Column A Column B 1 3 2 7 3 5 4 8 5 1 6 7 8 9 10 And I need it to end up like this: Column A Column B 1 1 2 3 3 4 5 5 6 7 7 8 8 9 10 So, in other words, the numbers in Column B need to end up in the same row as their corresponding numbers in Column A. To do it manually will take forever as the real data is thousands of rows long. Can this be done with an INDEX/MATCH formula of some kind? I=92ve tried a few different things on my own but I clearly don=92t know what I=92m doing. Any help would be greatly appreciated. Can you plea...

Excel
I need to mix some columns like this: Suppose A1:A3 = 1,2,3 and B1:B3 = a,b,c How can I get C1:C6 = 1,a,2,b,3,c ? Is it possible just with formulae (no macro) ? Any help would be appreciated TIA GM -- Message posted from http://www.ExcelForum.com try this in C1 =A1&","&A2 -- 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 2000 & 97 ** remove news from my email address to reply by email ** "GML >" <<GML.16ot1c@...

Cell Formats in formulas
I have a worksheet set up with formulas to automatically copy values from another cell as it is changed. Is there a way to include the format of the reference cell (font, color) when referencing it in a formula. Example: when I change to a different symbol font in the referenced cell, the referencing cells only change the value, not the font, so I don't end up with the correct symbols in the referencing cell. No, it's not possible to do that using formulas, only format formulas can do are number formats and only using the text function Regards, Peo Sjoblom "C. Lewis"...

Formula referencing other file show formula not result
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a formula in a cell that selects data from another spreadsheet. Both cells are text and often the formula shows in the receiving spreadsheet not the value. I have both cell formated as &quot;text&quot;. In older versions of excel, you could never have an &quot;=&quot; sign start in cell without excel always defaulting that as a formula. Now in 2008 it does not work that way any more. <br><br>Along the same line I have a formula in a cell that selects data from another spreadsheet. S...

cell displays formula instead of value #2
Hi, Excel 2003 SP2 I have a new blank workbook, and on a new blank worksheet I format columns A - D as Text. A1 = [This_] B1 = [is_] C1 = [text.] D1 = [=CONCATENATE(A1, B1, C1)] D1 now displays [=CONCATENATE(A1 ,B1, C1)] instead if the expected [This_is_Text.] Please help. I have hours in this :(( Thanks Jeff Higgins Jeff D1 should have been formatted as "general". Format it, re-type formula? HTH Beege "Jeff Higgins" <oohiggins@yahoo.com> wrote in message news:RFBzf.218$Cp2.147@fe03.lga... > Hi, > Excel 2003 SP2 > I have a new blank workbook, > a...

Extract AutoFilter Column Values?
Hi all, I have a column of road names, with various repititions of the same name. I want to extract each individual road name to a separate column (exactly the same as the Data>Filter>Autofilter command). The column has about 500 records of 50 unique road names, and its these unique road names that I want?? Please help, its greatly appreciated!! Regards, Dwayne -- dwayneh ------------------------------------------------------------------------ dwayneh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21211 View this thread: http://www.excelforum.com/show...

printing formula definitions
I've got some fairly elaborate formula definitions and I'd like to be able to print the worksheet to show the definition and not the output of the formula. Terri, CTRL+` (that's the key to the left of "1" on a standard keyboard) toggles the view between formulae and results. You can then print to your heart's content! Cheers, Pete >-----Original Message----- >I've got some fairly elaborate formula definitions and I'd like to be able to print the worksheet to show the >definition and not the output of the formula. > Choose Tools/Option...

How do I transpose multiple rows into columns?
I have data organized (in Office 2007) as such: A B C D E F and so on I want to tranpose it so it appears in columns as: A B C D E F and so on. Is there a way to transpose multiple rows so the information is stacked into columns? Thanks a million! Copy > PasteSpecial > Transpose.........as many times as you need......can be set to a macro if done frequently.. Vaya con Dios, Chuck, CABGx3 "Nick" wrote: > I have data organized (in Office 2007) as such: > > A > B > C > > D > E > F > > and so on > > I want to tranpose it so...

Return Corresponding Value Based on Comparing Two Sheets of Data
Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ...

Physical inventory formula problems
I am having a problem with the physical inventory feature. This is the example I pull a Item Value List report with only supplier as the filter. The total on this report is as follows QTY: -5 EXTENDED COST - $10 (these are minus's) Now I go to the physical inventory and select NEW then ONLY ITEMS FROM SELECTED SUPPLIERS and I choose the same supplier I choose on the ITEM VALUE LIST I choose to update maching item and replace existing items. I am importing 10 of the item that had a -5 when I started. Now when I calulate now expected should read what my ITEM VALUE LIST started wi...

Add a column to look up records webpage dialog
Hello All, When in the account record and wanting to add "An Existing" contact, I click on contacts and then I click the add existing contact. If I type Jim Smith, I get 10, but I want to also display the state in the available records list, how does one add that column. I tried making state recommended and required on the contact record, but that does not do the trick. I remember doing that years ago in 3.0, but I don't recall how to do that in 4.0? Thanks e Go to customise entities, choose Contact, go to "forms and views" and customise the "<Contac...

Formula to count no. of months between 2 dates
What is the formula to display the number of months between 2 dates? Thanks, Melissa Have a look at this site: http://www.cpearson.com/excel/datedif.aspx -- Regards! Stefi „Melissa” ezt írta: > What is the formula to display the number of months between 2 dates? > > Thanks, > Melissa =DATEDIF(A1,B1,"m") On 23 Mar, 08:27, Melissa <Meli...@discussions.microsoft.com> wrote: > What is the formula to display the number of months between 2 dates? > > Thanks, > Melissa I forgot to mention I am using Excel 2000. I us...

view worksheet formulas
Friends, Is there a way to view only the worksheet formulas inside a worksheet, or print them out? I know how to do this for pivot tables and pivot charts, but what about a normal worksheet? Thanks, Bill Morgan Bill Ctrl+` -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in message news:3475E172-19E9-4199-B974-709FE618FB5E@microsoft.com... > Friends, > > Is there a way to view only the worksheet formulas inside a worksheet, or > print t...