number repetitions highlighted

[librababy]

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

[peterjuhnke]

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

[librababy]

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

[librababy]

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

[librababy]

bump no response

[StephenR]

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.

[librababy]

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*******

[Marcol]

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,

[librababy]

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.

[Marcol]

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?

[squiggler47]

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

Shorter version of the formula!

[Marcol]

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.

[librababy]

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????

[Marcol]

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?

[Leith Ross]

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.

[librababy]

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.

[Leith Ross]

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.

[Marcol]

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

[Leith Ross]

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.

[Marcol]

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?

[librababy]

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????

[Marcol]

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.

[librababy]

. 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

[librababy]

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.