checking for combinations in multiple rows

6

7

4

2

1

1247

7

3

1

7

7113

8

1

3

1

4

4131

9

4

6

4

3

10

8

5

4

11

2

1

7

3

12

8

7

2

13

2

8

1

14

8

3

7

4

15

4

2

16

7

3

1

7

17

7

1

3

1

20

Cell Formulas
Range	Formula
G4:G17	G4	=F4&E4&D4&C4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Cell Value	=1	text	NO
G:G	Cell Value	=1	text	NO

edit: for the one below
got it with multiple =AND(COUNTIFS(

and if it's allowed another small question:
for testing and calc the data for the above question,
it seems i can't use IF and AND with numbers?
trying to to check if and combination of 3's from 1234 are repeating
1,2,3 or 1,2,4 or 1,3,4 or 2,3,4
and like you see it retures true=1 when it shouldn't

00.xlsx

A

B

C

D

E

F

G

H

1

2

1

3

1

2

1

3

1

4

1

3

1

4

1

4

1

00

Cell Formulas
Range	Formula
E1:E4	E1	=IF(AND(A1:D1,"1", A1:D1,"2", A1:D1,"3"),1,0)
F1:F4	F1	=IF(AND(A1:D1,"1", A1:D1,"2", A1:D1,"4"),1,0)
G1:G4	G1	=IF(AND(A1:D1,"1", A1:D1,"3", A1:D1,"4"),1,0)
H1:H4	H1	=IF(AND(A1:D1,"2", A1:D1,"3", A1:D1,"4"),1,0)

Eric W · Jul 7, 2022

You can try something like this:

Book1

A

B

C

D

E

F

G

H

I

J

K

1

2

3

4

2

1

8

5

5812

2185

5

7

4

1

3

7413

6

7

4

2

1

1247

7421

7

3

1

7

7113

3117

8

1

3

1

4

4131

1314

9

4

6

4

3

4643

10

8

5

4

8854

11

2

1

7

3

2173

12

8

7

2

8872

13

2

8

1

2281

14

8

3

7

4

8374

15

4

2

4422

16

7

3

1

7

7317

17

7

1

3

1

7131

18

Sheet10

Cell Formulas
Range	Formula
G4:G17	G4	=F4&E4&D4&C4
H4:H17	H4	=MAX(MMULT(--ISNUMBER(FIND(C4:F4,J5:J14)),{1;1;1;1}))
K4:K17	K4	=MAX(MMULT(--ISNUMBER(FIND(MID(J4,{1,2,3,4},1),J5:J14)),{1;1;1;1}))

The H4 formula works if you have the digits separated, the K4 formula works if the digits are joined (it looks at column J). The result is the maximum number of digits that match when comparing the current row with the next 10 rows. So by your example, the rows with 4 should be highlighted in red, the ones with 3 should be highlighted in blue. You can do the Conditional Formatting off of the helper column, or you can embed the formula into Conditional Formatting itself without a helper column.

A few thoughts about your example. You did not highlight row 4, but there are 3 matches in row 13. Oversight? Also note row 8 (1314). It says there are 3 matches with row 11 (2173). The 3 matches are the 3, and the 2 "1"s both match the single 1 in 2173, so it adds up to 3. How do you want to calculate duplicated characters? Only count them if there's a unique 1 for every unique 1 in the source number? That's a tougher formula I'll have to think about.

And do you still want the second question answered? I wasn't sure with the strikethrough, but I can see that the AND wasn't formatted in any very useful way.

excelNewbie22 · Jul 7, 2022

row 4 = oversight, yes...
as for rows 8 and 13, yes, i would please like to count them as uniques, if the source have one "1" then the counting will count only once, and if the source have two 1's then the counting will count only twice (if there's twice...)
maybe the unique function can help?

didn't understand what "the AND wasn't formatted" but if it's 'easy peasy' and no trouble for you, i'll glad to a better solution although for the time being i managed

last note, if you please... i wanted it backworks, for example:
in row 16
=MAX(MMULT(--ISNUMBER(FIND(C16:F16,J6:J15)),{1;1;1;1}))
no biggie, can always changed it

thank you so much for all the effort!

Eric W · Jul 7, 2022

The formula doesn't care if the numbers are forwards or backwards. To get the "multiple instances" problem solved, I had to come up with a completely different approach:

Book1

A

B

C

D

E

F

G

H

1

2

3

4

2

1

8

5

5812

5

7

4

1

3

6

7

4

2

1

1247

7

3

1

7

7113

8

1

3

1

4

4131

9

4

6

4

3

10

8

5

4

11

2

1

7

3

12

8

7

2

13

2

8

1

14

8

3

7

4

15

4

2

16

7

3

1

7

17

7

1

3

1

18

Sheet10

Cell Formulas
Range	Formula
G4:G17	G4	=F4&E4&D4&C4
H4:H17	H4	=MAX(4-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(G5:G14="","aaaa",G5:G14),C4,"",1),D4,"",1),E4,"",1),F4,"",1)))

Note that this formula requires both the single digit columns and the concatenated column, but that can be changed.

As far as your second request, I don't understand what you want, your description doesn't seem to match the example. But I can see that the AND statement is not formatted in a useful way. It is accepted but doesn't really do anything useful. If you have something you're happy with, that's fine. If not, then please provide an example of what you want and I'll look at it.

excelNewbie22 · Jul 7, 2022

"The formula doesn't care if the numbers are forwards or backwards."
didn't meant to the numbers 1234 or 4321 but to the range, instead of checking, say, row c14:f14 against g15:g24 meant to check it against g13:g4

for the other question, no need, thank you

P_E_R_F_E_C_T !

THANK YOU!

2 more little (hoping) things:
any way to add an option to somehow wrote how many times 3 or 4 returns for each number in the next column (i) to the array in question? like g17 return in g16+g11

and also maybe copying to next column (j) the ones repeated of 3 or 4?

20.xlsm

C

D

E

F

G

H

I

J

K

L

9

4

6

4

3

10

8

5

4

count repeated 3's?

which 3's repeated?

count repeated 4's?

which 4's repeated?

11

2

1

7

3

12

8

7

2

13

2

8

1

14

8

3

7

4

15

4

2

16

7

3

1

7

17

7

1

3

1

18

7

4

1

8

8147

19

2

4

6

6442

20

4

6

2

5

5264

21

6

4

7

1

1746

22

7

1

4

8

8417

2

6471-8374

1

7418

23

6

4

2

6

6246

20

Cell Formulas
Range	Formula
G9:G23	G9	=F9&E9&D9&C9
H11:H23	H11	=MAX(4-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(G1:G10="","aaaa",G1:G10),C11,"",1),D11,"",1),E11,"",1),F11,"",1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Cell Value	=1	text	NO
G:G	Cell Value	=1	text	NO

Eric W · Jul 7, 2022

Try:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

Max match in next 10 rows

# of 3 matches

3 matches

# of 4 matches

4 matches

4

2

1

8

5

5

7

4

1

3

1247, 7113, 4131, 3712, 4738

5

6

7

4

2

1

7

3

1

7

8

1

3

1

4

9

4

6

4

3

10

8

5

4

11

2

1

7

3

12

8

7

2

13

2

8

1

14

8

3

7

4

15

4

2

16

7

3

1

7

17

7

1

3

1