need excel FORMULA Help 2017

Reply #14

phileight — Fri, 22 Sep 2017 18:41:30 GMT

there is an error in the above file.

change line If rw 32 Then rw = 6: cl = 21

to If rw 32 Then rw = 6: cl = cl+4

now the second column will not be overwritten and you will see all the combinations.

Reply #13

Bob — Thu, 21 Sep 2017 21:44:28 GMT

And a thank you to PHILEIGHT for the macro----will be trying the macro out later on tonight

We got good replys from good people here on LP and I forgot thanks to ALLENB he also

sent me a file and JADE ----You guys know your excel thanks again

Have a good day

BOB in Michigan

Reply #12

JADELottery — Thu, 21 Sep 2017 18:24:04 GMT

yeuh, it's big.

just a mind's eye blink, in to how we think.

happy hitting

Reply #11

Bob — Thu, 21 Sep 2017 18:04:14 GMT

JADE woowa...

Hey thanks it works super long butt got the job done...

and thanks jade ...also trying to make a MACRO to work

it out ...not to good with a macro butt learning ..

bob in Michigan

Reply #10

JADELottery — Thu, 21 Sep 2017 13:11:07 GMT

Select all below the line, copy then paste to Excel

Good luck

____________________________________________________________________________________________________

=if(and(min(int($f5/100),int(($f5-100*int($f5/100))/10),($f5-10*int(($f5-100*int($f5/100))/10)-100*int($f5/100)))=median(int($f5/100),int(($f5-100*int($f5/100))/10),($f5-10*int(($f5-100*int($f5/100))/10)-100*int($f5/100))),median(int($f5/100),int(($f5-100*int($f5/100))/10),($f5-10*int(($f5-100*int($f5/100))/10)-100... [ More ]

Reply #9

phileight — Thu, 21 Sep 2017 12:23:17 GMT

this would be a difficult formula. fairly easy macro.

Sub reorder_count()

Dim n(999)

For x = 6 To 15

For y = 6 To 15

a = Cells(x, y)

l = Len(a)

If l = 1 Then n(a) = n(a) + 1 ' 001,002,005 etc

If l = 2 Then n1 = 0: n2 = Val(Left(a, 1)): n3 = Val(Right(a, 1)): GoSub sort: n(b) = n(b) + 1

If l = 3 Then n1 = Val(Mid(a, 1, 1)): n2 = Val(Mid(a, 2, 1)): n3 = Val(Mid(a, 3, 1)): GoSub sort: n(b) = n(b) + 1

Next y

Next x

rw = 6: cl = 17

For q = 0 To 999

If n... [ More ]

Reply #8

Bob — Mon, 18 Sep 2017 15:35:57 GMT

F G H I J K L M N O

5 660 661 662 663 662 665 666 660 668 669

6 110 111 112 113 112 115 116 110 606 119

7 000 001 002 003 002 005 600 000 008 009

8 550 551 552 553 552 555 556 550 558 066

9 330 331 332 333 332 335 336 330 338 339

10 885 881 882 883 882 885 886 880 885 889

11 775 771 772 773 772 775 776 770 775 779

12 995 991 992 993 992 995 996 990 995 999

13 445 441 442 443 442 445 446 440 110 449

14 225 221 222 223 222 225 226 220 225 110

example

would like

Reply #7

Bob — Mon, 18 Sep 2017 14:50:12 GMT

BAD COPY SHEET ------

butt need formula

thanks

bob Michigan

Reply #6

Bob — Mon, 18 Sep 2017 14:48:45 GMT

Still giving me wrong count for the duplicates

=LEN($F$5)-LEN(SUBSTITUTE($F$5, 660 , ))

EXAMPLE--HOW MANY DUPS?ANSWER SHOULD BE 5 ---BUTT IT SAYS 3

F G H I J K L M N O

660 1

066 2

606 3

4

066 5

6

660 7

8

anyway WRONG FORMULA

THANKS ANYWAY Raven62

Still need a formula----need help

bob in Michigan

Reply #5

Raven62 — Mon, 18 Sep 2017 13:36:22 GMT

Take a look at this:

https://www.lotterypost.com/thread/312837

Reply #4

Bob — Mon, 18 Sep 2017 13:18:33 GMT

thanks raven62 butt not working right...

=SUMPRODUCT(LEN(f5:O14)-LEN(SUBSTITUTE(f5:O14, 660 , )))

ctrl-shift-enter-----------array formula

=SUMPRODUCT(0+(MID(F$5:O$14,{1,2},660)= B1))

=SUMPRODUCT(0+(MID(A$1:A$10,{1,2},1)= 660 ))

=SUMPRODUCT(LEN(F5:O14)-LEN(SUBSTITUTE(F5:O14, 660 , )))answer is giving me 6 and we only have 4-----

looking for another formula to make it work RIGHT.....

Still need HELP from the EXCEL People

thanks

bob in Michigan

Reply #3

Raven62 — Mon, 18 Sep 2017 12:09:13 GMT

This may help:

https://stackoverflow.com/questions/31963208/formula-to-count-each-individual-digit-in-a-cell-per-column-in-excel

Reply #2

Bob — Mon, 18 Sep 2017 11:18:48 GMT

yes all cells 3 digit....

Reply #1

AllenB — Mon, 18 Sep 2017 00:43:44 GMT

Are Cells F5:F14 all 3 Digit Numbers

need excel FORMULA Help 2017

Bob — Sun, 17 Sep 2017 21:01:04 GMT

NEED HELP For a FORMULA in EXCEL--Running 2016excel

FORMULA To count 3 digit numbers (all in one cell range...

=COUNTIF($F$5:$O$14,$F5)

--------------------------------------------------------

Need formula to Count All Duplicates (all ways

EXAMPLE -- 123--132--213--231--312--321

NUMBERS are 3digits each in 0ne cell (123)

Just cant figure out the right formula ...Can Someone HELP??

Thanks

Bob in Michigan