# Formula to find the same duplicate values in multiple columns

#### mehulatree

##### New Member
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.

 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>

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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

Replies
10
Views
512
Replies
8
Views
758
Replies
12
Views
770
Replies
18
Views
769
Replies
15
Views
544

1,196,078
Messages
6,013,304
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back