Unique records formula & zero entries

[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

 

[Ashish Mathur]

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]

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

 

[T. Valko]

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,SMALL(helper,ROWS(C$2:C2))))

 

[T. Valko]

Ooops!

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

Should be:

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

 

[Ashish Mathur]

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]

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]

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!

 

[jc132568]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!