# google sheets: use numbered column to list duplicates in specific order

#### rinkjames

##### New Member
Hello,

I have a sheet of fictional data here: duplicates. Below is a screenshot for convenience.

I'd like to list, in the `duplicate` column, duplicates of values in the `id` column, with one condition: that the corresponding row in the `complete` column must contain a `1`, not a `0`. Furthermore, I'd like to list duplicates in ascending order according to the `#` column, so that rows with lower numbers in the `#` column have their duplicate value listed before rows with higher numbers in the `#` column — even if I change the sort order of the columns using a data filter.

All values are hard-coded, except for my current working formula in the `duplicate` column; cell `F2` contains:

Excel Formula:
``=IF(SUMPRODUCT(\$D\$2:\$D\$21=D2,\$E\$2:\$E\$21=1)>1,E2*(SUMPRODUCT(\$D\$2:\$D2=D2,\$E\$2:\$E2=1)-1),0)``

To illustrate the problem, the columns in the above sheet are currently sorted in ascending order acording to column `#`, producing the following desired result in the `duplicates` column:

Rich (BB code):
``````#    name_first   name_last   id           complete   duplicate
4    Dominique    Jordan      kDgDIFL2St   0          0
7    Dominique    Jordan      kDgDIFL2St   1          0
15   Dominique    Jordan      kDgDIFL2St   1          1
19   Dominique    Jordan      kDgDIFL2St   1          2``````

However, if I use the data filter to change the sort order of the columns —e.g. the `#` column in descending order — then my current working formula produces the follwing undesired result in the `duplicates` column:

Rich (BB code):
``````#    name_first   name_last   id           complete   duplicate
19   Dominique    Jordan      kDgDIFL2St   1          0
15   Dominique    Jordan      kDgDIFL2St   1          1
7    Dominique    Jordan      kDgDIFL2St   1          2
4    Dominique    Jordan      kDgDIFL2St   0          0``````

As I hope the above illustrates, the specific rows marked as duplicates change when I change the sort order of the columns (e.g. blue from 2 to 0; yellow from 0 to 2). I'd like to adjust the formula in the `duplicate` column to avoid this and always produce the former of the two results above irrespective of the sort order of the columns. Any help would be appreciated.

Kind regards,
James

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IFNA(MATCH(A2,SORT(FILTER(\$A\$2:\$A\$21,(\$D\$2:\$D\$21=D2)*(\$E\$2:\$E\$21=1))),0)-1,0)``

#### rinkjames

##### New Member
Excel Formula:
``=IFNA(MATCH(A2,SORT(FILTER(\$A\$2:\$A\$21,(\$D\$2:\$D\$21=D2)*(\$E\$2:\$E\$21=1))),0)-1,0)``
Wonderful — thanks.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
1
Views
158
Replies
8
Views
370
Replies
11
Views
4K
Replies
11
Views
97
Replies
0
Views
200

1,171,864
Messages
5,877,961
Members
433,304
Latest member
niresh28

### 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