Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Excel Cell Manipulation

This is a discussion on Excel Cell Manipulation within the Excel Questions forums, part of the Question Forums category; I am looking to be able to have more than one number in a cell and create a range for ...

  1. #1
    New Member
    Join Date
    Dec 2008
    Posts
    7

    Default Excel Cell Manipulation

    I am looking to be able to have more than one number in a cell and create a range for a formula to identify that cell.

    Example: Cell "B2" will have the numbers 12 and 13 so that when formula is directed to identify the number 12 it will identify that cell.

    I may even have more than two numbers like 12 to 14 but I can't seem to get a formula to identify that cell when more than one number is in it????

    Can this be done?

    Thank you mightly Excell Wizards!!

  2. #2
    Board Regular
    Join Date
    May 2007
    Location
    Brisbane, Australia
    Posts
    1,035

    Default Re: Excell Cell Manipulation

    I'm not clear on what you want..

    "Cell "B2" will have the numbers 12 and 13" means what?
    - Cell has 1213 - Cell has 12 13 - Cell has either 12 or 13?

    "when formula is directed to identify the number 12 it will identify that cell"
    - Does this mean you are trying to "search" for the number 12?
    One of the reasons I love Excel so much is every time I think I have it figured out someone shows me a better/more efficient/ cleaner or faster method to do what I have been doing the long way for years....


    http://www.rentacoder.com/RentACoder...RL=AId_7206185

  3. #3
    Board Regular crimson_b1ade's Avatar
    Join Date
    Sep 2008
    Location
    Yonkers, New York
    Posts
    1,557

    Default Re: Excel Cell Manipulation

    out of curiosity...what formula are you using (or trying to use) now

    ....and also, why do you have multiple numbers in a cell (eg: 12,13,14,15 etc) in a one cell? what are you trying to accomplish?
    Last edited by crimson_b1ade; Dec 20th, 2008 at 11:15 PM.
    You may not know the secrets of the Universe but there is one thing you should know. Everyone born in and of this Earth knows not more to such matters than you and possesses no more clairvoyance or holiness than you. So if you ought to believe in something…believe in YOURSELF!

  4. #4
    New Member
    Join Date
    Dec 2008
    Posts
    7

    Default Re: Excell Cell Manipulation

    Hello,

    Sorry for not being clear. I am creating a template to score a standardized test that I use in my profession. What I have is some cells just have a standardized single number and I can get my formula to recognize those cells. The problem arises when the standardized results have two or more numbers in a cell. When I was refering to the numbers 12 and 13 (or more numbers) I am saying that any single cell could have those numbers or more but when my formula is looking up lets say the number 12 but I have both 12 and 13 in the same cell, I get an #NA result not the actually number or 12 or 13 etc.

    I hope this is more clear, the bottom line is that my formula needs to identify specific numbers in the various cells but most of the cells have single numbers but the cells that have multi numbers result in #NA due to the fact that more than one number is in the cell and I can't get it to identify one of the numbers in the cell or the range of the numbers (I tried 12:13 and 12-13 etc.).

  5. #5
    Board Regular crimson_b1ade's Avatar
    Join Date
    Sep 2008
    Location
    Yonkers, New York
    Posts
    1,557

    Default Re: Excel Cell Manipulation

    i'm still a bit confused. Below is how I'm interpreting your posts:




    ******** ******************** src="www.interq.or.jp http:>*********>
    http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>
    cellSpacing=0 cellPadding=0 align=center>
    Microsoft Excel - Book1___Running: 12.0 : OS =
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    SomeNumbers FindResult
    2
    12 13 14 15 16 17 18 13Found it
    3
    18 19 20 21 22 23 24 15No match
    4
    84 85 95 86 82 11 13 95Found it
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    You may not know the secrets of the Universe but there is one thing you should know. Everyone born in and of this Earth knows not more to such matters than you and possesses no more clairvoyance or holiness than you. So if you ought to believe in something…believe in YOURSELF!

  6. #6
    New Member
    Join Date
    Dec 2008
    Posts
    7

    Default Re: Excell Cell Manipulation

    Your recent post was helpful but I am not sure how to enter it into my formula so I am going to attach it. I am also having trouble responding to your last response.

    =IF(AND($B$1>=4,$B$1<=4.03),VLOOKUP($C$3,$J$3:$GB$37,175,FALSE),(IF(AND($B$1>=4.04,$B$1<=4.07),(VLOOKUP($C$3,$J$3:$GB$37,175,FALSE)))))

    I have b1 refering to an age range of data. Then vlookup is looking up the data column that has the numbers that I have mentioned to you and transfers the correct number to a specified cell. The column below refers to column "J" and you can see the numbers 38 and 41, those are the numbers I need my formula to understand that in that cell if the number 38 or 39 or 40 or 41 means that it should take that cell and reference it to column GB which is the second column below and return that number. In my example, any number from 38 to 41 should return the number 35. I hope am making myself more clear??

    Female Ages 4.0 - 4.03 Scale Scores Corresponding To Subtest Point Scores
    Subtest 1: Fine Motor Precision
    38 41
    37
    36
    35
    34
    33
    30-32
    29
    28
    25-27
    23-24
    22
    21
    20
    19
    18
    17
    16
    15
    13-14
    11 12
    10
    8-9
    7
    5-6
    3-4
    2
    1
    0


    Scale Score
    35
    34
    33
    32
    31
    30
    29
    28
    27
    26
    25
    24
    23
    22
    21
    20
    19
    18
    17
    16
    15
    14
    13
    12
    11
    10
    9
    8
    7
    6
    5
    4
    3
    2
    1

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    27,082

    Default Re: Excell Cell Manipulation

    jonnyg

    Welcome to the MrExcel board!

    I'm a little cofused about your two columns - they seem to be different lengths and one has gaps while the other does not. However, I think this should point you in the right direction (at least if I have understood correctly).

    1. You need to reverse the order of your data in column J (that is, from smallest to largest).

    2. Also in col J, you need just the lowest value in each range. I have put some of your old col J values in col I to show what I mean.

    3. Instead of col GB, I have used col K to make my screen shot simple. I also didn't know how the numbers matched up but you should get the idea.

    4. Formula in N2 copied down.

    Also, since your VLOOKUP table is very large (cols J:GB) you could be slowing your sheet down a bit. Any change in data in J3:GB37 will cause your formulas referring to that range to recalculate. You could improve that by using an INDEX/MATCH construct as I have shown in col O. This way, only changes to the two relevant columns will cause the formula to recalculate.

    Post back if you need more help with modifying my suggestion to your layout.

    VLOOKUP

     IJKLMNO
    1    Col J valueCol K resultCol K result
    2    233
    3001 444
    4112 402929
    5223    
    63-434    
    75-655    
    8776    
    98-987    
    1010108    
    11 119    
    12 1310    
    13 1511    
    14 1612    
    15 1713    
    16 1814    
    17 1915    
    18 2016    
    19 2117    
    20 2218    
    21 2319    
    22 2520    
    23 2821    
    24 2922    
    25 3023    
    26 3324    
    27 3425    
    28 3526    
    29 3627    
    30 3728    
    3138-413829    

    Spreadsheet Formulas
    CellFormula
    N2=VLOOKUP(M2,$J$3:$K$31,2,1)
    O2=INDEX(K:K,MATCH(M2,J:J,1))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  8. #8
    New Member
    Join Date
    Dec 2008
    Posts
    7

    Default Re: Excel Cell Manipulation

    Okay, I am getting more confused. I really appreciate all of your help. The reason I don't want to have to reverse the columns is because I want the spreadsheet to duplicate the test that I am using for ease of undertanding where my data is placed.

    I know that I have a large spread sheet but the timing is not an issue. I get a proper response quickly enough. I also want to clarify that when there are blank cells it is because the test does not have data in those cells. The test will take a "Point score" number (the correct responses they give out of a total) and calculate that "Point score" with their age to look up a column from "J" to "GA" and obtains the number in that is referenced to the cell in "GB".

    The only issue I am having with this formula is that due to the test itself, they have some cells with multiple numbers. I am really just trying to figure out how do you get the formula (my (if(and.....(vlookup(....) that i have created to read a cell that has multiple numbers to identify that cell.

    Example was for the cell that has the numbers "38 to 41" (which is J3 in my sample columns). If I identify a child that is age 4 and they get a "point score" of 38, how can I get the cell to be identified so that it will locate the number "35" in column "GB" that is associated with "J3".

    I hope that I am not being more confusing than I need to be but I really and HOPEFULLY am looking for a simple solution. I just can't get the excell spreadsheet to identify a cell that has more than one number, my formula will then reply #N/A if there is more than one number. If i only had one number "38" (only) I would retrieve the "35" from "GB".

    Thank you all!!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    27,082

    Default Re: Excel Cell Manipulation

    Let's deal with a smaller sample data set to make it easier to post (suggest installing Excel jeanie ).

    VLOOKUP (1)

     GHIJKLGAGB
    1Col J valueCol GT result      
    231       
    3   38-41   35
    4   37   34
    5   36   33
    6   35   32
    7   34   31
    8   33   30
    9   30-32   29
    10   29   28
    11   25-28   27
    12        


    Excel tables to the web >> Excel Jeanie HTML 4



    Suppose we have this data in J:GB and we want to look up a value of 31. Now as I understand it, you would expect a result of 29. However, you are asking how to get Excel to recoginise that 31 belongs to cell J9 when 31 does not appear anywhere in J9.

    On that basis it would appear you are actually looking for some sort of 'approximate' match in column J. Any match/lookup functions that I know, when looking for an approximate match, require the data to be in ascending order.

    Your VLOOKUP formula was using 'FALSE' as the final argument, which means you are looking for an exact match. With an exact match lookup the data does not need to be in ascending order, but there does need to be an exact match (pretty logical really). So, if you want the data in descending order, would it be feasible to list all the possible values in col J like this? Then the VLOOKUP (excat match) would work.

    VLOOKUP (2)

     GHIJKLGAGB
    1Col J valueCol GT result      
    23129      
    3   41   35
    4   40   35
    5   39   35
    6   38   35
    7   37   34
    8   36   33
    9   35   32
    10   34   31
    11   33   30
    12   32   29
    13   31   29
    14   30   29
    15   29   28
    16   28   27
    17   27   27
    18   26   27
    19   25   27
    20        

    Spreadsheet Formulas
    CellFormula
    H2=VLOOKUP(G2,J3:GB19,175,FALSE)


    Excel tables to the web >> Excel Jeanie HTML 4


    If you don't want to do this either, then I can't see a formula solution for you and I think you would need to turn to a vba solution.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  10. #10
    New Member
    Join Date
    Dec 2008
    Posts
    7

    Default Re: Excel Cell Manipulation

    Quote Originally Posted by Peter_SSs View Post
    Let's deal with a smaller sample data set to make it easier to post (suggest installing Excel jeanie ).

    VLOOKUP (1)

    GHIJKLGAGB
    1Col J valueCol GT result
    231
    3 38-41 35
    4 37 34
    5 36 33
    6 35 32
    7 34 31
    8 33 30
    9 30-32 29
    10 29 28
    11 25-28 27
    12


    Excel tables to the web >> Excel Jeanie HTML 4



    Suppose we have this data in J:GB and we want to look up a value of 31. Now as I understand it, you would expect a result of 29. However, you are asking how to get Excel to recoginise that 31 belongs to cell J9 when 31 does not appear anywhere in J9.

    On that basis it would appear you are actually looking for some sort of 'approximate' match in column J. Any match/lookup functions that I know, when looking for an approximate match, require the data to be in ascending order.

    Your VLOOKUP formula was using 'FALSE' as the final argument, which means you are looking for an exact match. With an exact match lookup the data does not need to be in ascending order, but there does need to be an exact match (pretty logical really). So, if you want the data in descending order, would it be feasible to list all the possible values in col J like this? Then the VLOOKUP (excat match) would work.

    VLOOKUP (2)

    GHIJKLGAGB
    1Col J valueCol GT result
    23129
    3 41 35
    4 40 35
    5 39 35
    6 38 35
    7 37 34
    8 36 33
    9 35 32
    10 34 31
    11 33 30
    12 32 29
    13 31 29
    14 30 29
    15 29 28
    16 28 27
    17 27 27
    18 26 27
    19 25 27
    20

    Spreadsheet Formulas
    CellFormula
    H2=VLOOKUP(G2,J3:GB19,175,FALSE)


    Excel tables to the web >> Excel Jeanie HTML 4


    If you don't want to do this either, then I can't see a formula solution for you and I think you would need to turn to a vba solution.






    Okay, I am on the same page as you and finally understand what you are saying. The problem is that I can't change the column data due to the fact that I will have various data results that are spread out across all the columns I mentioned (j to GB will have data all over the scale). I guess what I am trying to do can't be done, what is this VBA solution?

    So sad, don't know why a computer can't just recognize that a cell has a range to look into? But I guess they aren't as smart as they should be. Just curious, would my problem work in Windows Vista? The only problem with that is that I have Vista at home but my school program doesn't and therefore, wouldn't be compatible.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com