Hello,
I have a sheet of fictional data here: duplicates. Below is a screenshot for convenience.
I'd like to list, in the
All values are hard-coded, except for my current working formula in the
To illustrate the problem, the columns in the above sheet are currently sorted in ascending order acording to column
However, if I use the data filter to change the sort order of the columns —e.g. the
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
Kind regards,
James
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