=IF(SUMPRODUCT macro question

I've been reading through previous posts and have not been able to fin
an answer to my problem, any help would be greatly appreciated.

I have a list of part numbers for laptop batteries corresponding to al
of the laptops they work in, so one part number may show up severa
hundred times.

I receive a daily list showing which part numbers are available an
which are backordered. I run a macro that takes each part number fro
the backorder file and compares it to the parts I sell. For example:

A
B-5630
AC-C10
B-5666
DK-C25

Column B is where I put the list of part numbers that are out of stock
Column E has my formula that is supposed to compare the part numbers i
B to A, which is:

=IF(SUMPRODUCT(ISNUMBER(SEARCH(B2,A1:A6))+0)>0,B2,"")

Here is my problem: Using the first part number above, B-5630, i
B-5630 is in column B, it will show up in column E. But, if only par
of the number is in column B (ie B-563, B-5, even just B), that wil
show up in E also. I need to make is so that only the exact number wil
show up in E, any suggestions? Thanks in advance! Oh, I just noticed 
can attach a file, I will attach a small sample of what I work with

                 Attachment filename: macro.xls                
Download attachment: http://www.excelforum.com/attachment.php?postid=51479
--
Message posted from http://www.ExcelForum.com

0
4/16/2004 6:44:49 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
451 Views

Similar Articles

[PageSpeed] 14

First, you can attach files via excelforums, but a lot of people connect to the
microsoft servers directly and won't see your attachment.  And many won't open
any attachments--too many threats of malicious code.

But maybe you could use a simpler formula if you're looking for an exact match:

=IF(COUNTIF(A1:A6,B2)>0,B2,"")





"hoyaguru <" wrote:
> 
> I've been reading through previous posts and have not been able to find
> an answer to my problem, any help would be greatly appreciated.
> 
> I have a list of part numbers for laptop batteries corresponding to all
> of the laptops they work in, so one part number may show up several
> hundred times.
> 
> I receive a daily list showing which part numbers are available and
> which are backordered. I run a macro that takes each part number from
> the backorder file and compares it to the parts I sell. For example:
> 
> A
> B-5630
> AC-C10
> B-5666
> DK-C25
> 
> Column B is where I put the list of part numbers that are out of stock.
> Column E has my formula that is supposed to compare the part numbers in
> B to A, which is:
> 
> =IF(SUMPRODUCT(ISNUMBER(SEARCH(B2,A1:A6))+0)>0,B2,"")
> 
> Here is my problem: Using the first part number above, B-5630, if
> B-5630 is in column B, it will show up in column E. But, if only part
> of the number is in column B (ie B-563, B-5, even just B), that will
> show up in E also. I need to make is so that only the exact number will
> show up in E, any suggestions? Thanks in advance! Oh, I just noticed I
> can attach a file, I will attach a small sample of what I work with.
> 
>                  Attachment filename: macro.xls
> Download attachment: http://www.excelforum.com/attachment.php?postid=514799
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
4/16/2004 11:59:00 PM
Well, actually I figured it out with: =IF
ISERROR((MATCH(B2,$A$2:$A$4,0))),"",B2) but now i have a differen
problem, I'm going to have to post another question

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

0
4/17/2004 7:11:37 PM
Reply:

Similar Artilces:

Outlook Macro To Forward Emails
Hello, Can someone please pass along the code that is required to automatically forward all emails in an Outlook folder to a new email adress? What macro? Use Rules. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, graeme.wyant@gmail.com asked: | Hello, | | Can someone please pass along the code that is required to | automatically forward all emails in an Outlook folder to a new email | adress? graeme.wyant@gmail.com schree...

Question regarding compare fields
Hello: I have a created a form with two textboxes on there with the intention of letting users fill in dates on there for further input. In order to prevent entry errors, I have created the Calendar object that will be activated when the user double clicks in a textbox and will fill in the date when the user select the date. Now having two of these textboxes taking in dates means that I would like to create a range for this. But I would like to program a check for this, where textbox 1 (From) should not have a later date than textbox 2 (To). If that error occurs, there should be an er...

OL 2003 crashes on macro (Alt-F8) or macro editor (Alt-F11)
I posted this problem to the VBA group, but I'm not sure it's a vba problem, so I'm posting it here as well; my apologies. With Windows XP Pro, SP-2, and Office 2003 SP-3: I recently installed Office 2003 (recovering, migrated to a new HD, and cleaning up the mess). I now find that either macro (Alt-F8) or macro editor (Alt-F11) causes an almost immediate OL crash, with no useful error information that I can see. I've deleted vbaProject.otm. OL doesn't even create a new one. I've also disabled all add-ins. No help. Any suggestions would be GREATLY appreciated. THANKS....

macro for work book
How can I write a macro to have it refresh data and text to column/ fixed width How can I develop this macro not for a sheet but for a work book. Thanks Daniel Daniel; Hate to say so, but I don't understand anything of your question. Also, what does this have to do with "Excel charts"? -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Daniel" wrote: > How can I write a macro to have it refresh data and text to column/ fixed width > > How can I develop this macro not for a sheet but for a work book. > > Thanks > Daniel > ...

macro embedded in an xml file
Hi, just wondering if anyone knows if you can embedd a macro in an xml file? I have an xml file that I create with a .net web application and it opens with Excel, but would be nice to attatch a macro to it as well. Thanks. -- Paul G Software engineer. Paul wrote: > Hi, just wondering if anyone knows if you can embedd a macro in an > xml file? XML isn't a programming language, so it can't have macros in the sense that you mean them. See http://xml.silmaril.ie/authors/execute/ Some processors (including browsers) may react to special features like Processing Instructions to...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Using an Excel Macro to move a folder with XP
Hi, I've posted this question 2 or 3 times, but I haven't gotten any responses. Can someone please give me a little help The following macro takes files that are in one directory ("C:\Fishbowl\Req_Serv\one"), and puts them in a newly created directory ("C:\Fishbowl\Solved_Service_Cases\one". This works in Excel 2000 running on 98, but does not work in Excel 2000 running on XP In 98, this creates a new subdir named "one" under "Solved_Service_Cases" and MOVES everything from the first directory into it (Before running this macro, there is a...

another socket and thread question
Hi! I have a question about sockets and threads, but I think threads are not the part of my problem. I made a server which uses simple socket, bind, listen etc functions and threads to handle connections. Basicly all incoming connection get a new thread. You(forum members :) ) showed me there are a conceptual problem with it and I shall use CAsyncSocket. I will try to reimplement this part of the server as soon as I have a little(?or much) time. Today I start to do some performance and stability tests and I found a strange behaviour. If I open some connections at the same time (about 10-200) ...

macro error #2
Whenever I press enter or tab., etc. to exit a cell I get the error "The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros." The thing is, I do not believe I have any macros - I was playing around before but I believe I deleted them all. (When I go to Run Macros, there are none, and when I view code under General Declarations and Work Sheet, there is no code listed). Does anyone know of any solution to this problem other than to change my Macro security from High to Medium? Th...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

How to get an reply to your question
The web forum that interfaces with the Office newsgroup (NNTP) has been broken since Sept. The people that volunteer their time to answer questions do not use the Mactopia forum and can't see your messages. If you use a newsreader rather than the Mactopia interface to this newsgroup you can download old messages and search for answers or start a new question. If you are not familiar with a newsreader, Entourage's basic newsreader already has the Microsoft News server included. All you do is subscribe to the newsgroups you want. See this page for help: <http://www.entourage.mvps.o...

Importing Data
Hello, I have a series of excel documents being sent to me over a period of time, and I would like to import this information into one sheet automatically. The documents being sent to me are all identically designed, so importing is no problem. Here is an example of what I am receiving: ALPHA 1 0 0 1 1 BETA 2 1 0 1 0 OMEGA 0 0 1 0 2 Each line represents a single excel doc I get back. ALPHA, BETA, etc are the names of the people who sent it in. The filenames are always ALPHA_01_01_2006.xls or BETA_02_04_2006.xls, etc. What I would like to do is create a MASTER sheet that contains a...

Macro or Function to make text size to suite text Length?
I would like to find Macro or Function that will make text size to suite text Length? eg. if the first cell has "bill smith" then the text size would be 10. But if you had "Christina Vandermear" then the text size would be as small as 7 in order to fit in that same size cell. I know it's a bit of an odd request, but is there a way to do that? thanks Hi only possible with VBA. You may download the following addin for this extension of the conditional format (as Excel's build in format can't change the font size): http://www.xldynamic.com/source/xld.CF...

general questions.. please help
can i know normally do people convert text files to xml format? how can we do to convert data from text files to xml? is it possible? to generate xml from jsp is it done automatically or manually? thanks in advance! appreciate if there are any links helpful... thanks all of u! jeremy *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Hello Jeremy, > can i know normally do people convert text files to xml format? not normally by hand, but it can be done. Doing it with a program is better. > how can we do to convert data from text files to x...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

cell reference changes after macro runs
I have two workbooks with simple identical formulas that update control number after the macro runs. One workbook is ok. The other wil not update the control number. Instead, it changes the cell referenc to one row higher and returns the previous control number. Here's wha each workbook looks like: Sheet one: cell C7 is the control number:20040004.formula for C7 i :=sheet2!'B5+1' Macro: Insert new row 5 on sheet 2,cut data from sheet 1 row 7, past into sheet 2 row 5. After the macro runs, the cell ref. on sheet 1, C7 changes to =B6+1. tried adding $ around $B5$+1, locking cel...

Office Macros
What is the latest about when we will be able to upgrade our Office for Mac so that it can run all of our spreadsheets with VB macros? -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison The only official word from Microsoft is that VBA will return in the next release of Mac Office which is tentatively scheduled for "Holidays 2010" - I take that to mean late 4th quarter of next year. I would not expect to see anything m...

Run a Macro after a cell changed due to a calculation
I'm sorry but i posted this already in programming. Yet i noticed the remark that it would be better to post it here. As it isn't the most simple question around. Goodday, I tried to use the worksheet change functions, yet they only seem to act when you actively enter into a certain cell. However i need to call a certain macro when the output in a certain cell changes due to the change in the specific if function. Therefore without user intervention. Is there any way to do this? Kind regards, Ivo Geijsen --- Message posted from http://www.ExcelForum.com/ Take a look at the w...

Run a macro that applies to any selected row
I want to be able to start a macro and have it do some copy/past functions on the cells in the specific row that I am in when the macr is started. In other words, lets say I move down Col A and stop in ro A23, I want the macro to do some copy/paste in that specific row. If decided to stop in A45, I want to do the same copy/paste functions i the same columns relative to the selected row. Can I do this with single macro? I'm still learning..but who isn't. Thanks in advance...Gre -- Message posted from http://www.ExcelForum.com The keyword here is activecell. You didn't say i...

Sumproduct or ?????
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

Force users to enable macros on a protected workbook?
Hello! I found this code for ensuring that users always enable macros, but I have a few questions about it: http://www.vbaexpress.com/kb/getarticle.php?kb_id=379 a) What would needed to be added to the code to make it work on a protected workbook? b) Does the code need some kind of error handling? (For my workbooks, there is a "Print" button on each sheet that when you push it, autofits the rows, pops up the spell checker, compares a few cells to make sure there are no duplicates, end dates don't come before start dates, etc. So without macros being always ...

DHCP question
Hi, I have 2 IP which I want to exclude from a range. One of the IP is used by a switch and the other one is assigned to a phone system - so I dont know the mac address. How do I exclude those 2 Ip's from the range. I only see the option to reserve in DHCP in window 2003. Thanks biren wrote: > Hi, > > I have 2 IP which I want to exclude from a range. One of the IP is > used by a switch and the other one is assigned to a phone system - so > I dont know the mac address. > > How do I exclude those 2 Ip's from the range. I only see the option to...

Macros #37
I get an error message when I do not have any macros in my workbook. They have all been deleted. Here is the error message: the macros in this project are disabled. Hi, If you have macros in your workbook, depending on your Securit settings (assuming Medium) you will be prompted to Enable/Disable. I you have a Security setting of Low, you will not be prompted and th book will open with macros Enabled. If you have a Security setting o High, you will not be prompted and the book will open without macro Enabled. Excel 2003 has an additional setting for macros above high t use in Sha...