Extracting Values Common To 4 Lists

Hello!

Sorry, another duplicate question... I couldn't find the answer to m
problem in any of the usual websites or recent posts so here it is:

Very simple. I have 4 list of location names, listed in 4 columns.
The number of entries in each column varies.

I would like to list the names that appears in ALL of these lists (i.e
column A, B, C,D) in, let's say, column E. (in other words, column 
would be a list of names that are in column A AND column B AND column 
AND column D).

Thanx you for your help!

Benoit H

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

0
3/1/2004 5:44:06 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
372 Views

Similar Articles

[PageSpeed] 19

Hi
enter the following formula in E1
=IF(AND(COUNTIF($A$1:$A$10,D1)>0,COUNTIF($B$1:$B$10,D1)>0,COUNTIF($C$1:
$C$10,D1)>0),D1,"")
and copy down

after this you may copy the values with 'Paste Special' and
filter/delte the blank rows



--
Regards
Frank Kabel
Frankfurt, Germany

> Hello!
>
> Sorry, another duplicate question... I couldn't find the answer to my
> problem in any of the usual websites or recent posts so here it is:
>
> Very simple. I have 4 list of location names, listed in 4 columns.
> The number of entries in each column varies.
>
> I would like to list the names that appears in ALL of these lists
> (i.e. column A, B, C,D) in, let's say, column E. (in other words,
> column E would be a list of names that are in column A AND column B
> AND column C AND column D).
>
> Thanx you for your help!
>
> Benoit H.
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
3/1/2004 5:53:09 PM
Thank you Frank (once again!) 

The formula works perfectly :)

Cordially,
Benoi

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

0
3/1/2004 7:15:57 PM
Reply:

Similar Artilces:

Counting cells with a calculated value.
I have been trying without success to do the following in 2007: Count cells in a named range (Col."H" abt 300 out of 1000 lines containing data) that are >= to columns "(C+H)+2". The format is "Currency" in both columns. I'm sure there is a way, but........ Thanks -- Sylvio If I correctly understand what you are asking, try something like =SUMPRODUCT(--(H1:H1000>=C1:C1000+H1:H1000+2)) or =SUMPRODUCT(ISNUMBER(H1:H1000)*(H1:H1000>=C1:C1000+H1:H1000+2)) These array formulas use the fact that the logical values TRUE and FALSE can be coerce...

Office 2010 Beta installation gets stuck at Step 4 of 4
I started the Office 2010 Beta installation, running the file that I downloaded from the MS site. It's been stuck for over an hour at "Step 4 of 4: Setting up Microsoft Office Home and Business 2010 (Beta)." Please advise. ...

Error: Value in Formula of wrong data type
I have a worksheet that uses custom formulas on several inputs from that page. I've used it for a while and never had a problem, but made some changes elsewhere in the worksheet (not linked to this part) and now on many of the cells using these formulas, I get a '#VALUE!' error, stating 'Value in Formula of wrong data type.' The data type is the same it has always been, so I don't know why it's not working now (formula just uses if thens to compare different numbers and return values like 0, 1, or 2). What's even more perplexing is that several ...

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...

Can't browse for a list server for offline address book
OK - new info - I tried to rebuild the GAL and the offline address book - I'm getting error (0x8004010F) when I click on tools send recieve address book in outook 2007 and 2003. I tried to click on browse under exchange system manager address list - list server and nothing happens and nothing shows up in the event viewer. I tried to create a new offline address book but when I enter a name and then click browse for list server nothing happens so I can't create a new offline address book. Ok, you meant to say when you download the OAB you get error 0x8004010F as this erro...

Drop Down List Woes
I have a drop down list in excel 2000 (data validation) whose contents cannot read. If I make the entire column width wider, I throw off th whole page layout. Is it possible to widen the drop down list withou changing the entire column width? Also, how do I change the font o this list? Thanks, Kevi -- Message posted from http://www.ExcelForum.com You could use a SelectionChange event to temporarily widen the column. There are some instructions here: http://www.contextures.com/xlDataVal08.html#Wider You can't change the font or font size for a Data Validation dropdown list. ...

Multiplied Values
Hi, Is it possible to design a VBA Macro to execute the following? I have in C6-C76 currency values. I'd like to have these values to be multiplied automatically by the month of the year located in A2. Ex: C6 = $168.92 Ex: A2 = Nov-04 I'd like to have C6 * 11 or C6*12 (If A2 =Dec-04) Hi if A2 is a real date value try: =C6*MONTH(A2) -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > Hi, > > Is it possible to design a VBA Macro to execute the following? > > I have in C6-C76 currency values. > > I'd like to have these values to be multiplied automa...

Tracking item quantities and values
I'm wondering if there is a way to track the quantity of items in a category or department over a history of time, including the total value of those categories or departments at that specific time. For example, I tried a report on Reports > Items > Value List and was able to get extended quantities and extended cost for the current time, but I am unable to change the time in the filter. I have also tried Reports > Items > Item Movement History and changed the range to Year to Date. This includes the change in quantity, but not the change in extended cost. It seems like ...

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...

Can not see CString's value when debugging
I'm very sure I'm in the CString's life scope, but I can not see its value but only some "???". Any other type of variables are ok. But some days ago, in the same project, I can see CString's value when debugging. So, what's wrong of CString in debug mode? Now I nearly can't do the debugging job! >So, what's wrong of CString in debug mode? It's a limitation of the debugger. Newer versions of CString have optimisations for the storage of small/long strings that the debugger doesn't cater for :( Dave ...

I am trying to change the text size of the folder list in Outlook
not the preview pane, the actual folder list in the Inbox Window. It's so tiny. "Bayoubelle" <Bayoubelle@discussions.microsoft.com> wrote in message news:D431653F-FA94-4AD2-9668-380846EE6CB2@microsoft.com... > not the preview pane, the actual folder list in the Inbox Window. It's so > tiny. Always state your Outlook version. Click View>Arrange By>Current View>Customize Current View>Other Settings. Change the Column Font and Row Font settings to suit. If you're using Outlook 2007, then it's View>Current View>... I...

how can i create and edit pull down list
I need to create a list of e.g, names so that i can select a name to include in a cell, instead of typing them. Thanks -- excel57 ------------------------------------------------------------------------ excel57's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17386 View this thread: http://www.excelforum.com/showthread.php?threadid=395401 You can use Data Validation to create a dropdown list of names. There are instructions in Excel's Help, and here: http://www.contextures.com/xlDataVal01.html excel57 wrote: > I need to create a list of e.g, nam...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

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? ...

Value-of Select
Hi there, Im in a pickle chaps. I need to fetch information out from our raw XML files into a Style XSL sheet. Basically I have the commant <xsl:value-of select="Product/ProductCode"/> However the structure of the XML file is based like the following example <root> <Product> <ProductCode type="Seller">V1145</ProductCode> <ProductCode type="Buyer">H05439</ProductCode> </Product> </root> As you can see, we have different product codes based on Buyer and Sellers. However in my style sheet I only wan...

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...

Duplicate Entries #4
Hi All Can Excel somehow detect that you have two same entries within a different worksheet but in the same file? This may sound a bit ambigous, so here is an example of what I mean: say I had a file called "my_spreadshit.xls" and with in it, sheet1 and sheet2 contained some info that was the same. Can I highlight it or identify it using some search function? Cheers http://www.cpearson.com/excel/duplicat.htm you can use the same technique as in the above link or from the same website you can download compare.zip a utility written by Myrna Larson and Bill Manville -- Regards...

Publisher Clip Art #4
I have MS Publisher 2003 and office 2003. I try and go into the clip art and get the error 0x8007007F. This fix to this is listed as updating the MDAC to 2.8. This has already been done and I still get the error. Any Ideas on this issue? The article suggests re-installing Publisher. How about the Jet service? The article also suggests installing it as well. You receive a "Clip organizer cannot complete operation the specified procedure could not be found error code 0x8007007f" error message when you try to insert Clip Art into an Office document or publication http://sup...

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(" ",...

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, ...

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...

most frequently occurring value
hello how do i find out the most frequently occurring value, digit, or numbe in a set of rows & columns ? then i need to find out the second most occurring value then the 3rd then find the least frequently occurring value then the second least occurring value then the 3rd thanx.. -- Pivotren ----------------------------------------------------------------------- Pivotrend's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=406 View this thread: http://www.excelforum.com/showthread.php?threadid=49572 Look in HELP for the FREUQUENCY() function -- Kind reg...

modeless dialog #4
I have a formview class which uses a dialog on a tab control. On this dialog I call a modeless dialog. That is all working okay, but I want to uncheck a check box on the main dialog when the Ok button is pressed on the modeless dialog. I can't seem to figure out how to do this and I was wondering anyone could give me a hand. How do I get access to the variable on my main dialog from my modeless dialog? Z.K. Z.K. wrote: > I have a formview class which uses a dialog on a tab control. On this > dialog I call a modeless dialog. That is all working okay, but I want > to ...

blank cells plot as zero values
This is a problem that's been discussed before, but I haven't seen this aspect addressed: If you select the chart in question and go to Excel>Preferences>Chart, you can indicate that empty cells should be plotted as 'Not plotted (leave gaps)". (This is one of 3 options including "Zero" and "Interpolated".) Thing is, this doesn't work! As far as I'm able to determine, the ONLY thing that Excel will do is treat your blank cells as zero values (yeah, yeah, unless I replace those blank cells with "#N/A"). The work-arounds are fine, but ...

Equation of items value
Dears, We have GP 10.0 SP2, we’re using average cost for inventory items. We’re trying to make an equation for inventory value from inventory module Equation of value = (IV BB + ADJ IN + PUR RCT + Sales Returns) – (Sales Invoices + ADJ OUT). We’re using the Smartlist inventory transactions to get above data, and filter by document type for each part, the result of this report should tie to the result of stock status Report since we are working on a snap shot of 2008 “no TRX in 2009 entered” so when we export the item QTY from Smart list with current cost the result of “QTY * Current Cost...