I am trying to find the lowest value in a data set.
The data set to be searched is referenced based upon its ID which includes both the Project Name and its Subcode.
I tried using:
=MIN(IF($F$2:$F$9="*"&$B2&"*"&$A$2,$G$2:$G$9))
as an array, but it returns either 0 or - when the correct output should be 9.
Any help would be greatly appreciated! Thanks in advance!
Last edited by meherenow9; 09-19-2011 at 01:49 PM.
Please see the attachment.
I've created a user defined function to calculate the values in column C.
You can see the code in the Module1 sheet in the VBA editor (Alt F11).
Martin
There's probably a better way, but try:
with Ctrl-Shift-Enter.
Please Login or Register to view this content.
Cheers,
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
I never cease to be amazed at the prompt responses on these forums. Thank you both for your time!
mrice: I am not very familiar with VBA so it will take some time for me to decipher the code and tweak it for my use. Thank you, though, it gives me a lot to learn from.
Conne: I much prefer an Excel formula to accomplish the task required, so your contribution is invaluable. After testing it, however, I noticed it only takes one field (the Sub Code) into account. Nowhere does it search for the Project Name, so this formula really only works for the first data set and not the second. Still, this is far closer than what I had and gives me a much better idea on how to finish the spreadsheet for my boss. I'm going to try to work out the changes still needed, but any additional assistance in accomplishing this will definitely be appreciated!
Edit: I apologize Conne. It turns out I forgot to update the cell address for the Project Name when I copied it down the list. It was a simple, careless oversight. After making the correction, I found your formula works as advertised. Thanks again for your help!
Last edited by meherenow9; 09-14-2011 at 03:15 PM.
Once I edited the formula Conn provided for use with my database, I seem to have broken it and can't figure out where I went wrong. It keeps returning #N/A errors.
From the sample one he provided above, I made the following changes:
* The IDs are variable-length and there are actually three (not two) strings to be searched for in the ID name. I added wildcard (*) characters to the search string before, after, and between each reference.
* The MID's text field was changed to refer to the imported XML data on another sheet in the same workbook with a row range of 2-5000. Since the ID is of variable length, I had the MID start_num begin at 1 and the num_chars extended to 255 characters.
* The $G$2:$G$9 references were also updated to refer to the appropriate range on the imported data sheet.
Here is the revised formula being used in the actual spreadsheet:
Please Login or Register to view this content.
Do you see any errors here that I may be overlooking? I can e-mail the actual spreadsheet with the imported data if that will help.
Thanks again for your time!
Please post another workbook with the changes you've described.
in h2 put
=SUBSTITUTE(MID(F2,FIND(" ",F2),255)," ","") drag down then use
{=MIN(IF($H$2:$H$9=$A2&$B2,$G$2:$G$9))} in c2
if you want to drag it down youll have to fill column a up first or use another helper doing just that
see attached
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I probably should have been a little more detailed with my first example. I thought it was going to be enough to figure it out from there. Here is a much better approximation to the spreadsheet I am working on. All the relevant data and formulae are in the same cells as in the real one. The x's in the Imported Data IDs represent any random characters.
Thanks again for your time and prompt responses. They are greatly appreciated!
The addition of the random (x) characters in the ID field make it far less an "ID" and far more a "game changer" from the previous success. Sorry, but you'll need to find a way to clean the ID field during the import before a reasonable solution might be applied.
Unless someone else here can pull this rabbit from the hat?
I would be possible (though a huge pain) to clean up the IDs. The x's aren't entirely random and, though the list would be huge, it is possible to list every field possibility in all the IDs. For the purposes of my sample sheet, it would be safe to assume that all x's, xx's, xxx's, xxxx's, and xxxxx's are the same sets of random characters.
I have added an updated sample worksheet with the IDs neatly cleaned up in a manner that remains consistent with the actual worksheet.
On the IMPORTED DATA sheet, highlight A2:B13 and sort ascending so the lowest numbers are first, at the top of the list.
Then on Sheet1 F3 put in this little beasty:
=INDEX('Imported Data 2'!$A:$A, MATCH("*"&$B3&"*"&LOOKUP(2,1/($A$1:$A3>""),$A$1:$A3)&"*"&LOOKUP(2,1/($A$1:$A3+1>1),$A$1:$A3)&"*", 'Imported Data 2'!$B:$B, 0))
Copy that down... you'll get N/A for the ones that don't match. to a row on the IMPORTED sheet.
The first part of the MATCH() formula above translates into a wildcard search of: *P01*Name1*2000*, which is then matched to column B of the imported data. Since you've sorted the data, the first row it finds that matches will be the lowest.
Last edited by JBeaucaire; 09-16-2011 at 01:24 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JB: Thanks! I think this might work, though I was really hoping to avoid having to manually edit any part of the imported data. Is there a function that can accomplish this task for me? If no other solution can be found, I'll do it your way.
Sorting the data each time it is imported, simple. Anything more is just too much plumbing...
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
To avoid sorting try this formula in F3
=MIN(IF(ISNUMBER(SEARCH("* "&B3&" *"&LOOKUP("zzz",A$2:A3)&" *"&LOOKUP(99^99,A$2:A3)&" *"," "&'Imported Data 2'!B$2:B$13&" ")),'Imported Data 2'!A$2:A$13))
confirmed with CRL+SHIFT+ENTER and copied down
or this variation would give you "No match"
=IFERROR(SMALL(IF(ISNUMBER(SEARCH("* "&B3&" *"&LOOKUP("zzz",A$2:A3)&" *"&LOOKUP(99^99,A$2:A3)&" *"," "&'Imported Data 2'!B$2:B$13&" ")),'Imported Data 2'!A$2:A$13),1),"No match")
Audere est facere
daddylonglegs: It appears to work with the sample worksheet just fine, but I keep getting "-" (or "No match" with the second version) when I copy it to the real worksheet.
I'm trying to sort out your formula to attempt a fix, but I can't quite make sense of the purpose behind the "zzz" and "99^99" in the LOOKUP functions.
Edit: I figured it out and it all seems to work just as I need it to. Thanks for all your help!
Last edited by meherenow9; 09-19-2011 at 01:50 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks