• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Excel Macro Question

H

hoyaguru

Senior member
Anyone know about macros is Excel? I have some data that has to be put together every day, here's an example:

Column
A............B............C............D............E
B-5603............................................"If" Macro
B-2234............................................"If" Macro
C-2934............................................"If" Macro

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

So, if I put a list of part numbers in column B, and one of them matches a part number in column A, that number is supposed to show up in column E. This works, but it is not very accurate, because it is picking up parts of numbers and putting them in E. For example, the first part number is B-5603, if a B-5603 is in the B column, it will show up in the E column. But if there is a B-560, that will also show up in the E column. I need it to be exact, and not pick up just parts of a number. Any suggestions?
 
use an if statement with a vlookup, which you can designate to only give you an exact match...
E1 would be =if(iserror(vlookup(b1,a1:a500,1,false)),"",vlookup(b1,a1:a500,1,false))

The 500 would depend on how far down you wanted to look in column A - the 'false' part forces Excel to only look for an exact match, not a partial one.
 
Try this formula in column E:

=IF( ISERROR((MATCH(B2,$A$2:$A$4,0))),"",B2)

If it works the way I think it should, it matches whatever's in cell B2 (part number) with an absolute list of part numbers (cells A2 through A4 or whatever). If MATCH doesn't find it, it returns an "#N/A" error which the ISERROR function catches. If no error is generated, we display the value of the part number.
 
Originally posted by: NeoV
use an if statement with a vlookup, which you can designate to only give you an exact match...
E1 would be =if(iserror(vlookup(b1,a1:a500,1,false)),"",vlookup(b1,a1:a500,1,false))

The 500 would depend on how far down you wanted to look in column A - the 'false' part forces Excel to only look for an exact match, not a partial one.
Click to expand...


Hmmm, if I don't have it exact, it gives me nothing, which is good. If I do have it exact, it is giving me "N/A".
 
Originally posted by: darktubbly
Try this formula in column E:

=IF( ISERROR((MATCH(B2,$A$2:$A$4,0))),"",B2)

If it works the way I think it should, it matches whatever's in cell B2 (part number) with an absolute list of part numbers (cells A2 through A4 or whatever). If MATCH doesn't find it, it returns an "#N/A" error which the ISERROR function catches. If no error is generated, we display the value of the part number.
Click to expand...


This seems to work, thanks!
 
Well, the above solved one problem, but now I have a harder problem, I'm not sure it's even possible. I'll try to make an example below:


A | B | C | D | E |
1| ETA P/N| OOS Date |Database| formula 1 | formula 2 |
2| AA-B10 | 5/02/2004 | AC-C13 | formula 1 | formula 2 |
3| AA-C14 | 5/08/2004 | AC-C15 | AC-C15 | formula 2 |
4| AC-C15 | 4/25/2004 | DK-B25 | formula 1 | formula 2 |
5| AA-C40 | 5/15/2004 | DK-B40 | formula 1 | formula 2 |

OK, sorry for the crudeness, wish I could upload a spreadsheet. Anyway, lets take the part number AC-C15. The
ETA file shows that is is out of stock (A4) until 4/25/2004 (B4). Thanks to the formula from the previous post, =IF( ISERROR((MATCH(C3,$A$2:$A$27,0))),"",C3), that part
number is showing up in D3, because it is matching A4. This works all the way down, no matter
how many times it is in column C. Now, the hard part is, I need to get the date from B4 that corresponds to
A4, to appear in E3. Is this possible?
 
use the exact same formula, but instead of the last part being B2, (or C2, depending on how you used it), have it be the column that the dates are in - which looks like B in your example

so in col D you have =IF( ISERROR((MATCH(B2,$A$2:$A$4,0))),"",c2)

and in Col E you would have =IF( ISERROR((MATCH(B2,$A$2:$A$4,0))),"",B2)

make sense?

 
No, I can't get it to work that way. If I'm following your example, the formula =IF( ISERROR((MATCH(B2,$A$2:$A$4,0))),"",B2) is saying to compare B2 to everything in column A. Nothing will match, as column B is dates and column A is part numbers.

If you meant something like =IF( ISERROR((MATCH(C3,$A$2:$A$27,0))),"",B3) in E3, that will work, but only for that particular cell, if the same part number is listed further down, it will not work. Sorry if I totally misunderstood your example!
 
Try this:

=IF( ISERROR( VLOOKUP(D3,$A$2:$B$5,2) ), "", VLOOKUP(D3,$A$2:$B$5,2) )

Again, if it works the way I think it should...it matches whatever's in D3 with a list of values in column A and outputs the corresponding value of column B. The ISERROR's are to hide nasty #N/A errors. Be sure to test this out before applying it in a production environment.

By the way, whatever column you put this formula in (I'm guessing E), make sure the number format is set to Date instead of General.
 
Didn't work for me, darktubbly, whatever cell I put your formula in in column E, it showed whatever value is in cell B4.
 
Yes! That's it! I don't know why it wouldn't work for me, I copied and pasted it exactly. Thank you so much, this will save me a ton of time every day.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top