Duplicates in Excel

[CD1981]

Hello,

I have a file that has rows of data that are only defined by H or V in each cell, I need to see if there are any duplicates within a selected number of cells to another selected number of cells throughout the spreadsheet. So I need to see if cells B2:B14 if the data is H, H, V, V, V, H, H, V, H, V, V, H, H; if that same data is in that identical same order anywhere else on the sheet. What is the quickest way to go about this or is it even possible?
Thanks,

Cathy

 

[CD1981]

Sorry, to clarify, I see my platform says MacOS but it's not, I'm on a Chromebook and I use online Office 365.

And Here is an example:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

1

V

H

V

V

V

H

V

V

V

V

H

H

V

H

V

V

V

V

V

V

H

V

H

V

V

2

H

H

H

H

H

V

H

H

H

H

H

V

V

H

V

V

V

V

V

V

V

V

H

V

V

3

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

4

H

V

H

V

V

H

H

V

H

H

V

H

V

H

V

V

H

V

V

H

H

V

V

H

H

5

V

H

H

V

V

H

V

H

V

V

V

H

V

H

H

H

H

V

V

V

H

H

V

V

H

6

V

H

V

H

H

H

H

H

H

H

H

H

V

H

H

V

H

V

H

H

H

H

H

V

H

7

V

H

V

H

H

H

H

H

V

V

H

V

V

H

H

V

V

H

V

V

V

V

V

H

H

8

H

V

H

V

H

V

H

V

H

V

H

V

H

V

H

V

H

V

H

V

H

V

H

V

H

9

V

V

H

V

V

H

H

H

H

H

V

H

H

H

V

V

H

H

V

H

V

H

V

V

V

10

V

H

H

H

H

H

V

H

H

V

V

H

H

V

V

H

V

H

H

V

V

V

V

H

V

11

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

H

12

V

V

H

H

V

H

V

H

V

H

V

H

H

H

V

H

H

V

H

H

H

V

V

H

V

13

H

H

V

V

H

H

V

V

H

H

V

V

H

H

V

V

H

H

V

V

H

H

V

V

H

 

[Fluff]

Hi & welcome to MrExcel.

Not entirely sure if this is what you want, but how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	V	H	V	V	V	H	V	V	V	V	H	H	V	H	V	V	V	V	V	V	H	V	H	V	V
2	H	H	H	H	H	V	H	H	H	H	H	V	V	H	V	V	V	V	V	V	V	V	H	V	V
3	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
4	H	V	H	V	V	H	H	H	H	H	V	H	V	H	V	V	H	V	V	H	H	V	V	H	H
5	V	H	H	V	V	H	V	v	V	V	V	H	V	H	H	H	H	V	V	V	H	H	V	V	H
6	V	H	V	H	H	H	H	v	H	H	H	H	V	H	H	V	H	V	H	H	H	H	H	V	H
7	V	H	V	H	H	H	H	v	V	V	H	V	V	H	H	V	V	H	V	V	V	V	V	H	H
8	H	V	H	V	H	V	H	h	H	V	H	V	H	V	H	V	H	V	H	V	H	V	H	V	H
9	V	V	H	V	V	H	H	v	H	H	V	H	H	H	V	V	H	H	V	H	V	H	V	V	V
10	V	H	H	H	H	H	V	v	H	V	V	H	H	V	V	H	V	H	H	V	V	V	V	H	V
11	H	H	H	H	H	H	H	h	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
12	V	V	H	H	V	H	V	v	V	H	V	H	H	H	V	H	H	V	H	H	H	V	V	H	V
13	H	H	V	V	H	H	V	h	H	H	V	V	H	H	V	V	H	H	V	V	H	H	V	V	H
14
15	2	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Data

Cell Formulas
Range	Formula
A15:Y15	A15	=SUM(--(MMULT(SEQUENCE(,ROWS($A$1:$A$13),,0),--(A1:A13=$A$1:$Y$13))=13))

 

[CD1981]

Hi & welcome to MrExcel.

Not entirely sure if this is what you want, but how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	V	H	V	V	V	H	V	V	V	V	H	H	V	H	V	V	V	V	V	V	H	V	H	V	V
2	H	H	H	H	H	V	H	H	H	H	H	V	V	H	V	V	V	V	V	V	V	V	H	V	V
3	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
4	H	V	H	V	V	H	H	H	H	H	V	H	V	H	V	V	H	V	V	H	H	V	V	H	H
5	V	H	H	V	V	H	V	v	V	V	V	H	V	H	H	H	H	V	V	V	H	H	V	V	H
6	V	H	V	H	H	H	H	v	H	H	H	H	V	H	H	V	H	V	H	H	H	H	H	V	H
7	V	H	V	H	H	H	H	v	V	V	H	V	V	H	H	V	V	H	V	V	V	V	V	H	H
8	H	V	H	V	H	V	H	h	H	V	H	V	H	V	H	V	H	V	H	V	H	V	H	V	H
9	V	V	H	V	V	H	H	v	H	H	V	H	H	H	V	V	H	H	V	H	V	H	V	V	V
10	V	H	H	H	H	H	V	v	H	V	V	H	H	V	V	H	V	H	H	V	V	V	V	H	V
11	H	H	H	H	H	H	H	h	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
12	V	V	H	H	V	H	V	v	V	H	V	H	H	H	V	H	H	V	H	H	H	V	V	H	V
13	H	H	V	V	H	H	V	h	H	H	V	V	H	H	V	V	H	H	V	V	H	H	V	V	H
14
15	2	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Data

Cell Formulas
Range	Formula
A15:Y15	A15	=SUM(--(MMULT(SEQUENCE(,ROWS($A$1:$A$13),,0),--(A1:A13=$A$1:$Y$13))=13))

No this isn't quite what I want to do. I have 12 tables like this one on the one sheet and I want to know if there are any duplicates on the 12 tables, I want to have all different combinations of H, V in the possible 13 spots, but I don't want any of the columns to be the same. So I want something like, H, V, H, H and then on the next column I want H, V, V, H, and then on the next H, V, V, V, etc.

 

[Fluff]

In that case I'm afraid I don't understand what you do want.

 

[CD1981]

Hi & welcome to MrExcel.

Not entirely sure if this is what you want, but how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	V	H	V	V	V	H	V	V	V	V	H	H	V	H	V	V	V	V	V	V	H	V	H	V	V
2	H	H	H	H	H	V	H	H	H	H	H	V	V	H	V	V	V	V	V	V	V	V	H	V	V
3	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
4	H	V	H	V	V	H	H	H	H	H	V	H	V	H	V	V	H	V	V	H	H	V	V	H	H
5	V	H	H	V	V	H	V	v	V	V	V	H	V	H	H	H	H	V	V	V	H	H	V	V	H
6	V	H	V	H	H	H	H	v	H	H	H	H	V	H	H	V	H	V	H	H	H	H	H	V	H
7	V	H	V	H	H	H	H	v	V	V	H	V	V	H	H	V	V	H	V	V	V	V	V	H	H
8	H	V	H	V	H	V	H	h	H	V	H	V	H	V	H	V	H	V	H	V	H	V	H	V	H
9	V	V	H	V	V	H	H	v	H	H	V	H	H	H	V	V	H	H	V	H	V	H	V	V	V
10	V	H	H	H	H	H	V	v	H	V	V	H	H	V	V	H	V	H	H	V	V	V	V	H	V
11	H	H	H	H	H	H	H	h	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H	H
12	V	V	H	H	V	H	V	v	V	H	V	H	H	H	V	H	H	V	H	H	H	V	V	H	V
13	H	H	V	V	H	H	V	h	H	H	V	V	H	H	V	V	H	H	V	V	H	H	V	V	H
14
15	2	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Data

Cell Formulas
Range	Formula
A15:Y15	A15	=SUM(--(MMULT(SEQUENCE(,ROWS($A$1:$A$13),,0),--(A1:A13=$A$1:$Y$13))=13))

Upon further examination I think I could actually use this, but when I put the formula in it's giving me an error. It says: "Function SEQUENCE parameter 1 value is 0. It should be greater than or equal to 1." How do I fix this? I'm pretty sure I'm entering what you gave me wrong, but I just need a little guidance on how to do it properly! Thanks for this Fluff, appreciate the help!

 

[Fluff]

What exactly have you entered? As you can see from post#3 the formula works ok.

 

[CD1981]

What exactly have you entered? As you can see from post#3 the formula works ok.

This is what I entered:

=SUM(--(MMULT(SEQUENCE(,ROWS($A$1:$A$13),,0),--(A1:A13=$A$1:$Y$13))=13))

 

[Fluff]

That absolutely fine, do you use an English language version of Excel & do you use commas as the separator in functions?

 

[CD1981]

This is what I entered:

=SUM(--(MMULT(SEQUENCE(,ROWS($A$1:$A$13),,0),--(A1:A13=$A$1:$Y$13))=1

That absolutely fine, do you use an English language version of Excel & do you use commas as the separator in functions?

English only yes, not sure what you mean about the commas. I'm pretty novice, so please feel free to explain to me like I'm a 6 year old....lol