Comparing two values in different worksheets and extracting value

Hello,

I have the following problem with which I need some help:

I have two different worksheets with article numbers and quantity data. One 
of these includes the whole set of articles, while the other holds only a 
selection of articles. I need to compare the article numbers and extract a 
value from another column in the first worksheet when these article numbers 
match. E.g.:

worksheet 1
article number        quantity
12345                    12
98765

worksheet 2
article number        
12345

I want to extract the quantity number from worksheet 1 into worksheet 2, 
only for the matching article numbers.

Many thanks

Regards,

Ben
0
Roij (1)
1/24/2005 1:03:06 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
438 Views

Similar Articles

[PageSpeed] 27

Sheet1
Article number = Column A
Qty = Column B

Sheet2
Article number = Column A
Enter this formula in B2:

=IF(ISNA(MATCH(A2,Sheet1!$A$2:$A$50,0)),"",VLOOKUP(A2,Sheet1!$A$2:$B$50,2,0)
)

And drag down to copy.
This will leave an empty cell if there are no matches found in Column A.
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Ben van Roij" <Ben van Roij@discussions.microsoft.com> wrote in message
news:11E1FE24-3173-4AF3-A167-7BDAB27463FB@microsoft.com...
Hello,

I have the following problem with which I need some help:

I have two different worksheets with article numbers and quantity data. One
of these includes the whole set of articles, while the other holds only a
selection of articles. I need to compare the article numbers and extract a
value from another column in the first worksheet when these article numbers
match. E.g.:

worksheet 1
article number        quantity
12345                    12
98765

worksheet 2
article number
12345

I want to extract the quantity number from worksheet 1 into worksheet 2,
only for the matching article numbers.

Many thanks

Regards,

Ben


0
ragdyer1 (4060)
1/24/2005 2:38:05 PM
Reply:

Similar Artilces:

How do I build a workbook from the worksheets another workbook?
Is it possible to make up the sheets of an Excel workbook from another workbook that is in a remote address (i.e. a folder below) If you're asking whether you can copy (or move) those sheets from that remote workbook into another workbook, then the answer is yes. If you're asking if a workbook can contain worksheets of another workbook, then the answer is no. Rico wrote: > > Is it possible to make up the sheets of an Excel workbook from another > workbook that is in a remote address (i.e. a folder below) -- Dave Peterson Thanks Dave, I know you can populate cells from...

copy paste value
I have a colum of vlookup numeric data and want to add it up, but each cell may not return a value depending on how many items are needed. I belive I will need to add a colum in and grab the numeric data that is returned and then use a formula to do a copy paste value and if no value equal zero. I am not sure how to do this thoe. here is the vlookup formula i am using. Can you please help =IF(ISNA(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,FALSE)),"",(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,...

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

How do I print 4 different postcards on the same page?
I am trying to print a mail merged document of postcards. I only need one copy of each post card. The only options I can find are to either print four of the same postcard or one per page. I would like to print four different post cards on one page. Does anyone have any idea how I can do this. You're doing a mail merge for four postcards? "EMCL" <EMCL@discussions.microsoft.com> wrote in message = news:BA4A5215-CA5F-4144-941C-295770B06219@microsoft.com... > I am trying to print a mail merged document of postcards. I only need = one=20 > copy of each post car...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

Counting occurances of same data in different fields
I am creating a conference registration program that allows registrants to select up to three events to participate in. Is there an easy way to count the total number of registations for each event that occurs in the three selection fields? On Mar 16, 2:54 pm, Fred Morris <FredMor...@discussions.microsoft.com>wrote:> I am creating a conference registration program that allows registrants to> select up to three events to participate in. Is there an easy way to count> the total number of registations for each event that occurs in the three> selection fields?I gather you hav...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Select case syntax for continuous range values?
I know you can use Select case statements to cover a range of integer values, such as: Case 100 To 500 Case 501 to 650 etc However, I'm not clear on how to work with non-integer values to ensure that every possible value is covered, while ensuring no overlap in categories. With an IF statement I might use: If x >100 and x <=500 elseif x >500 and x <=650 which would properly assign a value of 500.4395 to the second condition Is there a way to do this with select case? Or is my best option to try to use more decimals than the data might need, e.g. C...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Pivot Chart
Hello, I am trying to create a stacked area Pivot Chart from a Pivot Table. I have negative values but am having trouble getting them to be displayed properly in the pivot chart. Currently, the negative values are displayed at the top and are "eating into" the positive series. I need the negative values to be displayed below the x-axis. Any help would be greatly appreciated. Aleksandr, This may be silly to ask, but... Did you try and adjust the y-axis scale from 0 to some number to a negative value (greater than the lowest negative value) to some positive value. Ex...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

how to summerise values from specific columns
Hi, I have tried to find the right formulah, but failed. I have three column Amount A Amount B Amount C 30 10 20 34 and a fourth column where I have to fill in % that applies to all values A-C in the row. 12% 45% How to make a formula in a cell that summerise all amount A's * the respective % For example: 30 * 12% + 10 * 45% = Also for two other cells for B and C Bart Vista/excel 2007 try sumproduct() =SUMPRODUCT(A2:A3*D2:D3) HTH Regards Sebation "AA Arens" <bartvandongen@gmail.com> ??????:1189666230....

Plot trend of inventory values for mulitple branches
I need to plot the trend of core inventory values for 87 branches over time, but haven't enough experience with Excel's charting feature to do this correctly. I am using MS Excel 2003 SP3. Can someone explain the best way to begin. I have compiled the data and it is ready for graphing. I have tried to use the feature, but I do not know how to get all the data into the graph because of the three types of data - time range, inventory values, individual branches. I need all three types of data included for comparison. ...

Updating the value of a multiple-investment account
If I don't want to track all the component investments in a multiple-investment account, just the value for total account performance tracking purposes, what is the best way in Money Plus Home & Business? I see where you can just update the share amount, but not the value... "david_b2" <davidb2@discussions.microsoft.com> wrote in message news:57FCFC7D-0F30-46AA-AE5A-5D8FCECBF72F@microsoft.com... > If I don't want to track all the component investments in a > multiple-investment account, just the value for total account > performance > tracking pur...

differences between two tables
I'm trying to find the differences between two tables, A and B. There are some records in A that are not in B, there are some in B that are not in A, and there are some in both but with differences in the fields. I'm not getting everything. Does anyone have any ideas how I can tackle this? Thanks! Use a UNION ALL query. -- KARL DEWEY Build a little - Test a little "denise" wrote: > I'm trying to find the differences between two tables, A and B. There are > some records in A that are not in B, there are some in B that are not in A, > and there are s...

Different Account Balance
I have a different account balance when I view all transactions vs. view transactions by past 12 months. My account balance from balance account is also different from my account balance vs. all transactions. I have read the KB article at: http://support.microsoft.com/default.aspx?scid=kb;en-us;838713&Product=mny I exported then deleted all my existing transactios and re-imported them (method 3), but all my account balances are wrong. I don't want to use this file since it is almost twice the size of my original file. I know there are 7 transactions that are wrong (the remaining t...

0 value in pivot table
We are pulling pivot tables from OLAP cubes and would like to filter out the lines that show 0 in the data area. Does anyone know how to do that? thanks ...

collect clipboard value into string
I have a macro that pastes the clipboard string (I think it's the "clipboard"), Selection.Paste, into the body of a letter. I would like to, within vba, inspect that string first before pasting. How do I capture it into a var which I can inspect? Thank you. "oldyork90" <oldyork90@yahoo.com> wrote in message news:ec8d0dd3-e9b4-47fb-8009-7a2f7c1baf3d@q5g2000hsa.googlegroups.com... >I have a macro that pastes the clipboard string (I think it's the > "clipboard"), Selection.Paste, into the body of a letter. I would > like to, within vb...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

X-axis values in xlLine chart problem
Hi, I have the following code: $Range = $Sheet->Range( "A3:A93" ); if ($Range) { $Chart->SeriesCollection( 1 )->{XValues} = $Range->{Value}; } it doesn't set the X-axis values as I would expect. my range contains values "-60" to "40". it works fine if the range contain positive values . any help would be greatly appreciated. -Gary ...

Why does Outlook open two windows?
When installed Outlook 2003 with a new profile about two hours ago, it would open one window each time I started Outlook. But now, after I've been doing a lot of work on the folder structure, particularly transferring dozens of folders by drag and drop within a single non-default PST file. I notice that the computer has been very noisy for a while, apparently furiously coping with the changes I've been making. Now when I start Outlook, it opens two windows; presumably that's an outcome of something I inadvertently did when transferring all the folders. I'd rather it ...