Formula to find the same duplicate values in multiple columns

[mehulatree]

Hi,

I have some data wherein, I wish to create groups out of the same. The logic behind the groups is that if multiple columns have the same number present, they form a group.

In the example,
Group A: Column 1 ("1" is not present in any other columns)
Group B: Columns 2-18 ("11" and "12" are present in all these columns)
Group C: Columns 19-22 ("20" is present in all these columns)
Group D: Column 23 ("23" is not present in any other columns)
Group E: Columns 24-25 ("24" and "25" are present in all these columns)
Group F: Column 26 ("26" is not present in any other columns).

The first step towards getting this answer (the eventual aim is to form groups for any number of rows, columns and values), is to get a formula that checks if a particular number is common in ALL the columns of the selected range.

I will be grateful if someone can help me with the formula/<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> code required towards reaching my eventual aim.

Thanks in advance!

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	26
	3	2	2	2	2	2	2	2	2	2	2	3	3	7	7	11	11	20	19	20	20		25	24
	4	4	3	3	3	3	3	3	3	3	3	4	4	8	8	12	12		21	22	21
	5	5	5	4	4	4	4	4	4	4	4	5	5	9	9	13	13		22
	6	6	6	6	5	5	5	5	5	5	5	6	6	10	10	14	14
	7	7	7	7	7	6	6	6	6	6	6	7	7	11	11	15	15
	8	8	8	8	8	8	7	7	7	7	7	8	8	12	12	16	16
	9	9	9	9	9	9	9	8	8	8	8	9	9	13	13	18	17
	10	10	10	10	10	10	10	10	9	9	9	10	10	14	14
	11	11	11	11	11	11	11	11	11	10	10	11	11	16	15
	12	12	12	12	12	12	12	12	12	12	11	12	12	17	17
		13	13	13	13	13	13	13	13	13	13	14	13	18	18
		14	14	14	14	14	14	14	14	14	14	15	15
						15	15	15	15	15	15	16	16
						16	16	16	16	16	16	17	17
										17	17	18	18
										18	18

<tbody>
</tbody>

 

[Mackers]

Hi mehulatree

If you want to check how many of the columns contain a particular number, you can use a slightly adjusted version of XOR LX's array formula as follows (use control+shift+enter):

=SUM(IF(MMULT(TRANSPOSE(N(IFERROR($A$1:$Z$17=AE1, 0))), (ROW($A$1:$A$17)^0))>0, 1, 0))

Where A1:Z17 contains the above data and AE1 has the number you wish to check. This returns, for example, 17 columns for number 11 and 12, 16 columns for number 13 and only 1 column for number 1, as you would hope. You can compare this to a COUNT() of the top row A1:Z1 to see if every column has that number in it.

Hope that helps in your quest

Mackers