J
jc132568
Dear Experts,
I have four lists each on separate worksheets being fed into one central
sheet. Each list (100 entries) may only differ by 10 entries so I end up
with one list of 400 entries with duplicates. With the help of an expert
previously I was able to use:
Assume your data in column A with a header in row1. Defined name range
"data" no quotes
In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
copy down. Defined name range in columnB "helper" of course no quotes
In C2:
=IF(COUNT(helper)>=(ROWS($1:1)),INDEX(data,SMALL(helper,ROWS($1:1))),"")
copy down
to filter for unique entries (no user intervention, I just wanted it to
happen automatically) to create a master list free of duplicate entries. My
problem is that my lists contain zero entries which seems to upset the above
and I miss out on data that comes before a zero entry.
a
b
0
d
e
f
g
h
0
will return the unique list
a
b
0
d
e
f
g
I won't see h until I enter something in below it, other than zero. It seems
once a zero has been encountered once, then it causes problems for data
coming immediately before the next zero, ie. the entry won't appear in the
unique list.
Can I accomodate these zeros and the effect they seem to have on the unique
filtering?
Many thanks
Martina
I have four lists each on separate worksheets being fed into one central
sheet. Each list (100 entries) may only differ by 10 entries so I end up
with one list of 400 entries with duplicates. With the help of an expert
previously I was able to use:
Assume your data in column A with a header in row1. Defined name range
"data" no quotes
In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
copy down. Defined name range in columnB "helper" of course no quotes
In C2:
=IF(COUNT(helper)>=(ROWS($1:1)),INDEX(data,SMALL(helper,ROWS($1:1))),"")
copy down
to filter for unique entries (no user intervention, I just wanted it to
happen automatically) to create a master list free of duplicate entries. My
problem is that my lists contain zero entries which seems to upset the above
and I miss out on data that comes before a zero entry.
a
b
0
d
e
f
g
h
0
will return the unique list
a
b
0
d
e
f
g
I won't see h until I enter something in below it, other than zero. It seems
once a zero has been encountered once, then it causes problems for data
coming immediately before the next zero, ie. the entry won't appear in the
unique list.
Can I accomodate these zeros and the effect they seem to have on the unique
filtering?
Many thanks
Martina