I have a column with three numbers in each row. I want to assign specific numbers to a set.
For instance, if either one of these numbers below (does not matter the position) is in the first column I want it to be named "Set 4"
334, 335, 336, 337, 338, 339, 044, 144, 244, 344,
445, 446, 447, 448, 449, 055, 155, 255, 355, 455,
556, 557, 558, 559, 066, 166, 266, 366, 466, 566,
Last edited by Jordans121; 03-24-2010 at 05:51 PM.
The attachment is included in case you are confused as to what I'm looking for. I simply want to assign various numbers to one set using a formula, as described above and in the attachment
Still not clear to me what you're trying to do; generate the comma-delimited list from the data in cols A & B, or compute the result in col B from the comma-delimited list?
Entia non sunt multiplicanda sine necessitate
Compute the result in col B from the comma-delimited list.
Why not put the elements currently in the comma-delimited list in a column, with the set association in the adjacent column, and just use VLOOKUP?
Try putting this formula in B5 then copy down:
=IF(COUNTIF($K$6:$K$8,"*"&A5&"*")>0,"set 4", "set 1")
_________________
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!)
Here is the complete sets so anyone wont be confused
I tried using it JBeaucaire but it continues to only give me "set 4" for numbers that are not included in this set.. Refer to to the last attachment
I assembled the numbers the way you suggested SHG. However I'm not familiar with "V lookup" so please look at the attachment.
I've attached the original workbook with a solution using the find function. It's a rather lengthy function, but it works. For verification, I converted each column from the last posted workbook into a string of numbers for each set.
I've also attached a modified version with vlookup doing the work. This workbook might do the work more quickly and the formulae will not be so painful to edit.
Thanks so much Jnewby72! Both are fine and I will keep them as a reference for future projects.
The difference for what I understand is that Vlookup has to be in ascending order or in other words a perfect match
The help says...
Since you want to find an exact match and the range was not in ascending order, I set range_lookup to false.If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.
If you liked my solution, add to my reputation please. Yes, I'm rep whoring.
I need a formula to rearrange my numbers from low to high in order for the Vlookup formula to work. For example...
032 needs to be converted to 023
This is very frustrating because the 0 is not recognized as the first number. Thus, not allowing me to use the sort function.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks