Unique records formula & zero entries
 

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(h elper,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

Unique records formula & zero entries
 

Hi,

You could try this. Go to Data Filter Advanced Filter and select "Copy
to another location". In the list range, select the range (including the
header row). Leave the criteria blank. In the Copy to box, select any
blank cell and check the box for unique records.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jc132568" wrote in message
...
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(h elper,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

Unique records formula & zero entries
 

Thank you for your reply. I would like for the filtering to happen
automatically, the master list being generated without user intervention,
other than loading the initial four lists.
kind regards
Martina

"Ashish Mathur" wrote:

Hi,

You could try this. Go to Data Filter Advanced Filter and select "Copy
to another location". In the list range, select the range (including the
header row). Leave the criteria blank. In the Copy to box, select any
blank cell and check the box for unique records.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jc132568" wrote in message
...
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(h elper,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

Unique records formula & zero entries
 

Works OK for me.

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL( helper,ROWS($1:1))),"")

Just make sure the named ranges are properly defined.

I would tweak the formulas like this:

=IF(COUNTIF($A$2:A2,A2)=1,ROWS(B$2:B2),"")

=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMAL L(helper,ROWS(C$2:C2))))


--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
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(h elper,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

Unique records formula & zero entries
 

Ooops!

I would tweak the formulas like this:
=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMA LL(helper,ROWS(C$2:C2))))

Should be:

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,SMALL (helper,ROWS(C$2:C2))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Works OK for me.

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL (helper,ROWS($1:1))),"")

Just make sure the named ranges are properly defined.

I would tweak the formulas like this:

=IF(COUNTIF($A$2:A2,A2)=1,ROWS(B$2:B2),"")

=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMAL L(helper,ROWS(C$2:C2))))


--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
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(h elper,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

Unique records formula & zero entries
 

Hi,

Try this

1. Give a heading to the consolidated list, say Heading1
2. Select the range (including the heading) and assign it a name, say
dummy1
3. Select the range again (including the heading) and press Ctrl+L
4. Select any blank cell and save the file
5. Now go to Data Import External Data New database query
6. Select Excel files
7. Navigate to the folder where the file is saved and select the file name.
Press OK
8. In the next box, select dummy1 on the LHS box and then press the greater
then symbol to get the singly column on the right
9. Press the Next button three two times till you come to last screen
10. Select View or Edit data using MS Query
11. Go to View Query properties and check the box for unique records
12. Now go to File Return data to MS Office Excel
13. Select the cell where you want the output

Now you just have to right click on any cell in the output range and select
Refesh
Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jc132568" wrote in message
...
Thank you for your reply. I would like for the filtering to happen
automatically, the master list being generated without user intervention,
other than loading the initial four lists.
kind regards
Martina

"Ashish Mathur" wrote:

Hi,

You could try this. Go to Data Filter Advanced Filter and select
"Copy
to another location". In the list range, select the range (including the
header row). Leave the criteria blank. In the Copy to box, select any
blank cell and check the box for unique records.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jc132568" wrote in message
...
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(h elper,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

Unique records formula & zero entries
 

Thank you for your reply, this is helping me towards a solution. What would
it mean if after a certain point in my data set the helper numbers correctly
identify the unique entry but this fails to translate over to column C as the
right unique entry, in fact it returns the cell above it. This happens after
the first patch of zero entries. Am I allowed to send you the single
worksheet? I have pored over this but lack the expertise to see the problem.
kind regards
Martina

"T. Valko" wrote:

Ooops!

I would tweak the formulas like this:
=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMA LL(helper,ROWS(C$2:C2))))

Should be:

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,SMALL (helper,ROWS(C$2:C2))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Works OK for me.

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL (helper,ROWS($1:1))),"")

Just make sure the named ranges are properly defined.

I would tweak the formulas like this:

=IF(COUNTIF($A$2:A2,A2)=1,ROWS(B$2:B2),"")

=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMAL L(helper,ROWS(C$2:C2))))


--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
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(h elper,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





.

Unique records formula & zero entries
 

Ok, I see the problem. I should've caught this earlier!

Change the formulas to:

=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,MATCH (SMALL(helper,ROWS(C$2:C2)),helper,0)))

That should do it!

--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
Thank you for your reply, this is helping me towards a solution. What
would
it mean if after a certain point in my data set the helper numbers
correctly
identify the unique entry but this fails to translate over to column C as
the
right unique entry, in fact it returns the cell above it. This happens
after
the first patch of zero entries. Am I allowed to send you the single
worksheet? I have pored over this but lack the expertise to see the
problem.
kind regards
Martina

"T. Valko" wrote:

Ooops!

I would tweak the formulas like this:
=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMA LL(helper,ROWS(C$2:C2))))

Should be:

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,SMALL (helper,ROWS(C$2:C2))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Works OK for me.

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL (helper,ROWS($1:1))),"")

Just make sure the named ranges are properly defined.

I would tweak the formulas like this:

=IF(COUNTIF($A$2:A2,A2)=1,ROWS(B$2:B2),"")

=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMAL L(helper,ROWS(C$2:C2))))


--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
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(h elper,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





.

Unique records formula & zero entries
 

Thank you soooo much, this now works beautifully.
Many thanks
Martina

"T. Valko" wrote:

Ok, I see the problem. I should've caught this earlier!

Change the formulas to:

=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,MATCH (SMALL(helper,ROWS(C$2:C2)),helper,0)))

That should do it!

--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
Thank you for your reply, this is helping me towards a solution. What
would
it mean if after a certain point in my data set the helper numbers
correctly
identify the unique entry but this fails to translate over to column C as
the
right unique entry, in fact it returns the cell above it. This happens
after
the first patch of zero entries. Am I allowed to send you the single
worksheet? I have pored over this but lack the expertise to see the
problem.
kind regards
Martina

"T. Valko" wrote:

Ooops!

I would tweak the formulas like this:
=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMA LL(helper,ROWS(C$2:C2))))

Should be:

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,SMALL (helper,ROWS(C$2:C2))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Works OK for me.

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL (helper,ROWS($1:1))),"")

Just make sure the named ranges are properly defined.

I would tweak the formulas like this:

=IF(COUNTIF($A$2:A2,A2)=1,ROWS(B$2:B2),"")

=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMAL L(helper,ROWS(C$2:C2))))


--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
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(h elper,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





.



.

Unique records formula & zero entries
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
Thank you soooo much, this now works beautifully.
Many thanks
Martina

"T. Valko" wrote:

Ok, I see the problem. I should've caught this earlier!

Change the formulas to:

=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,MATCH (SMALL(helper,ROWS(C$2:C2)),helper,0)))

That should do it!

--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
Thank you for your reply, this is helping me towards a solution. What
would
it mean if after a certain point in my data set the helper numbers
correctly
identify the unique entry but this fails to translate over to column C
as
the
right unique entry, in fact it returns the cell above it. This happens
after
the first patch of zero entries. Am I allowed to send you the single
worksheet? I have pored over this but lack the expertise to see the
problem.
kind regards
Martina

"T. Valko" wrote:

Ooops!

I would tweak the formulas like this:
=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMA LL(helper,ROWS(C$2:C2))))

Should be:

=IF(ROWS(C$2:C2)COUNT(helper),"",INDEX(data,SMALL (helper,ROWS(C$2:C2))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Works OK for me.

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL (helper,ROWS($1:1))),"")

Just make sure the named ranges are properly defined.

I would tweak the formulas like this:

=IF(COUNTIF($A$2:A2,A2)=1,ROWS(B$2:B2),"")

=IF(COUNT(helper)(ROWS(C$2:C2),"",INDEX(data,SMAL L(helper,ROWS(C$2:C2))))


--
Biff
Microsoft Excel MVP


"jc132568" wrote in message
...
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(h elper,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





.



.