+ Reply to Thread
Results 1 to 24 of 24

number repetitions highlighted

  1. #1
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Question number repetitions highlighted

    hi. i am somewhat new to excel. i have attached an excel table... a work sheet to develop numbers. i am almost completed it and i am trying now to get repeated numbers from all categories to be highlighted even if they are not in the same sequence that is eg.4695 appearing as 5964 or 6954. etc. and then i need it to appear on a seperate sheet as the highleted numbers... is there a possible way i can do such. thnxs
    Attached Files Attached Files
    Last edited by librababy; 11-27-2010 at 10:32 PM.

  2. #2
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: number repetitions highlighted

    Hello
    Where are we meant to be looking for the numbers?
    How many didgits are the numbers going to be? How many numbers are we talking about?
    Regards
    Peter

  3. #3
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    hey peter,

    OK. SO I WAS GIVEN A SET OF FORMULAS TO TRY AND MAKE A WORKSHEET TO DEVELOP NUMBERS. AS U WOULD SEE IN THE ATTACHMENTS IN "red"


    THE NUMBERS TO BE USED ARE 1-9


    THE KEYS FOR FORMULAS ARE
    S = SAME
    + = ADD 1
    - = SUBTRACT 1


    IF I WAS GIVEN 1483 TO THE FORMULA S-+S
    THE ANSWER WOULD BE 1393
    THAT IS
    1 : S = 1
    4 : - = 3
    8 : + = 9
    3 : S = 3



    I HOPE IT IS CLEAR THUS FAR.

    HOWEVER THERE IS ONE MORE TO ADD TO THE FORMULA KEYS.

    THAT IS

    9 : + ( WHERE 1 IS ADD ONE AS WE KNOW) MUST BE EQUAL TO 0 AND NOT 10.

    EXAMPLE: SS++ TO THE NUMBERS 1289
    THE ANSWER WOULD BE 1290
    THAT IS
    1 : S = 1
    2 : S = 2
    8 : + = 9
    9 : + = 0




    OK AS SEEN IN THE ATTACHEMENTS. EACH CATEGORY CARRIES ITS OWN FORMULAES AND THERE IS AN INPUTED NUMBER SLOT ASSIGNED FOR EACH CATEGORY. EACH FORMULA WORKS USING THESE ASSIGNED NUMBERS..

    I HAVE SO FAR DONE ALL THE CALCULATIONS FOR THE FORMULAS GIVEN IN EACH CATEGORIES..

    NOW THIS IS WHERE I AM STUCK??

    I NOW HAVE TO ON A SEPERATE SHEET SHOW THE NUMBERS THAT ARE REPEATED DOESNT MATTER IN WHAT ORDER IT COME FROM EITHER CATEGORY.
    FOR EXAMPLE

    1234 UNDER YING YANG MAY APPEAR AS 3241 UNDER YANG YANG OR 4231 UNDER YING YING.

    THERE FORE I WOULD NEED THAT NUMBER SEQUENCE TO BE HIGHLIGHTED AND PLACED ON A SEPERATE SHEET OR DIFFERENT HEADING ON THE SAME PAGE (WHICH EVER IS EASIER) SO THAT I KNOW THESE ARE THE 4 NUMBERS THAT KEEPS REPEATING ITSELF REPEATS ITSELF.

    (THERE MAY BE MORE THAN ONE SETS OF NUMBERS THAT ARE BEING REPEATED)


    I HOPE THIS EXPLANTION IS BETTER AND THAT SOMEONE CAN FINALLY ASSIST ME
    Attached Files Attached Files
    Last edited by librababy; 11-10-2010 at 10:48 AM. Reason: CLEARER EXPLANATION

  4. #4
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    Bottom line is that i just need tho highlight repeated numbers, taking into considerations that the inputed number can change at any time. Plz

  5. #5
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    bump no response

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: number repetitions highlighted

    Can you add some examples of desired results to your file? I understand what you're doing but I don't understand what you mean by numbers being repeated.

  7. #7
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    ok stephen.... i have attached a copy of what i mean..... they are colour coded for clarification
    eg. yellow,purple,blue,pink and green shows repeated patterns according to their numbers( no matter the pattern) For eg the num repeated are (yellow..2133)... or in (green....1224)...(purple...1134)....(blue...1243....)and (pink....2234....).
    so on a seperate sheet i would get the information that these are the repeated numbers giving the digits and the color its in..

    NB. all the inputed numbers for the different category can be changed but will all carry the same numbers as it inputed numbers. These inputed numbers can be changed at any time so the color codes and patterns should be reflected.

    <<< or what else could i use i nstead of color codes, but carry out the same respons???e>>>>

    ***********DRAFT IS A COPY OF THE ORIGINAL DOCUMENT. IT WAS JUST EDITED FOR CLARIFICATION PURPOSES*******
    Attached Files Attached Files
    Last edited by librababy; 11-19-2010 at 01:47 PM. Reason: ATTACHMENTS

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    You might be able to do this with conditional formatting depending on the version of Excel you are using.

    Your profile indicates 2003 but your posted sample suggests 2007 or later.

    For example
    Formula is:=
    Please Login or Register  to view this content.
    Applies to:=
    Please Login or Register  to view this content.

    At worst you could add the formula to your sheet in R8 then drag down.
    This could be the rule for the rows you want to copy to another sheet.

    Just a suggestion, I haven't looked at your first attachment.

    [EDIT]
    Forgot to upload attachment

    I have only applied the formatting to the area contained in the grid,
    Attached Files Attached Files
    Last edited by Marcol; 11-19-2010 at 02:46 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  9. #9
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    Thnx marcol,
    but what seems to be happening is that if i change the inputed number, the results changes but the color codes are not reflected in what u did. (=$m$8:$p$18).
    Do i copy and edit it in all the catefories???. And also the colours was just a few i used, i would eventually have to check the whole worksheet to give each a particular color.

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    I didn't do anything other than offer a different approach to your problem.

    Should I look at your thread and see if this method is viable?

  11. #11
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: number repetitions highlighted

    =MOD(CHOOSE(FIND(MID($A8,COLUMNS($D8:D8)+1,1),"-+s"),-1,1,0)+D$6,10)

    Shorter version of the formula!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    Your explanation of colour coding is not clear to me.

    It seems to me that you want to highlight rows with all the given digits in any order returned.

    You have coloured some rows where this is not true.

    You seem to be saying that if one given digit appears more than once then that row should be highlighted even though some of the given digits are missing.

    I think this rule needs further explanation.

    Is this what you mean

    1/. If all digits are present in any order then colour = colour 1
    2/. if any three digits are returned and any one is duplicated then colour = colour 2
    3/. If any two digits are returned and they are duplicated then colour = colour 3
    4/. If any given digit occurs three times with any other given digit then colour = colour 4
    5/. If any givin digit apears in all four positions then colour = colour 5

    I don't know if all these condition will ever be possible, but can you clarify what you need highlighted and copied?

    I have added a bit more to the sample attachment to see if it is on the right tracks.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    ok marcol,
    yes i want to highlight rows with any 4 digit numbers that are repeated in any order returned.
    "the colour codes was just me looking through to see which number combination matchs up."
    i just need to know the combinations that are repeated.

    JUST FOR EXAMPLE eg. numbers 4567,1596 and 2569

    IN THE CATEGORIES YINGYING YANGYANG YING YANG
    4567 may appear like 6754 5674 4657
    (just so that i knnow that theses numbers are the same and are repeated ill put it in lets say red wherever its been repeated)ofcourse possibility exists that all may not be repeated.[/B]

    1596 may appear like 9561 6591 1695
    (i may choose to use the colour blue, wherever its being repeated)

    2569 may appear like 6592 2965 9652
    (i may chhose to use the color orange, wherever the combinations is being repeated.

    Is this clearer????
    Last edited by librababy; 11-22-2010 at 02:25 PM. Reason: CORRECTIONS

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    As clear as mud!

    You have so many Yings and Yangs I'm going to end up Winging the Wong numbah...

    Try this

    I have Formatted your tables:= If all digits are present in any order for each individual table.

    The colours that will appear if the condition is true are indicated in the selection cells for each table.

    The columns with TRUE/FALSE are helper columns and if TRUE indicate the rows to be copied later.

    These columns can either be hidden or, depending on the copy procedure, deleted later.

    Does this look anything like what you are after?
    Attached Files Attached Files

  15. #15
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: number repetitions highlighted

    Hello librababy,

    I have added the following User Defined function to the attached workbook. It will return True or False based on whether the digits repeat or not. No coloring was added because you can use conditional formatting to set the colors as you like.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  16. #16
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    thnx marcol,
    you are getting where i am coming from....
    the next thing is that if there are any other number sequence in any group that is repeated it to must be highlighted.
    in the attached<<<<<pay attention to the digits in orange>>>>>
    see the numbers 3123
    are repeated in the BASIC STRATEGY as 1233
    and in 2 DIGITS DECREASE AND 1 DIGIT INCREASE 1323 and 2133,,,,
    so i would need to get this group of numbers into a color...

    ALL GROUPS OF REPEATED NUMBERS MUST BE IN ONE COLOR.
    so marcol, inorder words 1234 must be in one color

    If there are numbers that are not repeated any where then the number remains as it is.
    P.S. WHERE IS THE TRUE/FALSE FOR THE BASIC STRATEGY..

    @ LEITH,THNX.
    CAN I NOW GET IT TO WORK WITH OTHER REPEATED NUMBERS.. LOOK AT THE ATTACHED AND CHECKOUT WHAT I AMM TRYING TO DO.


    THNX GUYS, WE WILL MAKE THIS WORK.
    Attached Files Attached Files

  17. #17
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: number repetitions highlighted

    Hello librababy,

    Can you provide a more detailed example of what you want? I really can not see what you are trying to do from the worksheet.
    Last edited by Leith Ross; 11-23-2010 at 02:33 AM.

  18. #18
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    I asked this in post #12
    Is this what you mean

    1/. If all digits are present in any order then colour = colour 1
    2/. if any three digits are returned and any one is duplicated then colour = colour 2
    3/. If any two digits are returned and they are duplicated then colour = colour 3
    4/. If any given digit occurs three times with any other given digit then colour = colour 4
    5/. If any given digit apears in all four positions then colour = colour 5
    Your reply only asked for the first condition, although your post #5 seemed to ask for something else.
    yes i want to highlight rows with any 4 digit numbers that are repeated in any order returned.
    "the colour codes was just me looking through to see which number combination matchs up."
    i just need to know the combinations that are repeated.
    this is why the table "Basic strategy" was omitted.

    How many of the above conditions do you want to apply?


    From post #1
    THERE FORE I WOULD NEED THAT NUMBER SEQUENCE TO BE HIGHLIGHTED AND PLACED ON A SEPERATE SHEET OR DIFFERENT HEADING ON THE SAME PAGE (WHICH EVER IS EASIER) SO THAT I KNOW THESE ARE THE 4 NUMBERS THAT KEEPS REPEATING ITSELF REPEATS ITSELF.

    (THERE MAY BE MORE THAN ONE SETS OF NUMBERS THAT ARE BEING REPEATED)
    What do you mean by this?
    Where do you want the results returned?


    Leith
    I agree, VBa is a much easier way to solve this one, I was trying to find a macro free solution, at least initially.
    The TRUE/FALSE columns were my addition, and only intended as a parking place for the C/F formulae until it was clear where we were going with this one

  19. #19
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: number repetitions highlighted

    Hello Marcol,

    Thanks for the update about the True/False column. I was perplexed about the problems with conditional formatting when the operational result was present in another column.

    The downside of the UDF is volatility. Every time a change is made to cell with the UDF then all the other UDF cells are updated. This can slow things down when there is a lot of data.

  20. #20
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    Okay give this a try, still no macros involved.

    I have based the colour groups on the number of times a given digit repeats in the result.

    If there are too many results returned this is relatively easy to fix by deleting the appropriate colour format.

    Use the Grouping buttons (+ / - icons below the formula bar) to see how it works.

    Columns U:V are not required, I left them in to help explain the final formulae.

    Are we getting close to your requirements?
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    hey marcol,

    the blue coloring is somewhat , what i am looking for......
    the thing is it will only give me the results for whatever digits are present in the inputed fields lets say 1234,, ... now thats good,, and that color lets say yellow,,,, now all 1234 repeated in whatever combinations under which ever category should be coloured in yellow,

    in any other category 5689 might be repeated in the said category and in another category in a different combination,,, now all 5689 should be in another color,,, ltes say green

    same way,,,3451 might be repeated aswell,, different combintion,,, it too can carry a color...

    get it.
    we are lmost there.
    Is excel my best choice to carry out that execution????

  22. #22
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: number repetitions highlighted

    Sorry I am completely lost now, you seem to be asking for, something in that is near a bottomless pit.

    What colour should, 3729, or any combination of that be?

    Can you provide a result table that gives me some better idea of what you are after?

    Forget Yings and Yangs for the time being, ah, bi, que, deh will do nicely.

  23. #23
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    . I sat there and i was thinking,,
    this thing isnt as hard as it seems..lol..
    So i sat down and manually got the result that i am looking for,
    then i placed them into color categories,,,
    and i am thinking... thats it,,
    because if all inputed numbers in each category is going to be the same at all times then the results will always remain the same..
    lol....

    So now i need a way to link the result table to a reference row in the table.( as shown in the attached))

    all this work. Call it a shortcut if u will, lol

    thnks
    Attached Files Attached Files
    Last edited by librababy; 11-27-2010 at 09:53 PM.

  24. #24
    Registered User
    Join Date
    11-08-2010
    Location
    Tortola
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: number repetitions highlighted

    thnx ervy one i have managed to complete my task,
    for the latter part i used the concatenate formulae... thank you
    stephen r, peterjunkhe, squiggler, leith and esp marcol, withouth you guys i would still be stuck..
    '
    thnx a million
    attached is the finish product.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1