how to sort for unique data

[lalaexcel]

Hi everyone

I have been using the advancedfilter and the vbe for filter specificed
data. Though I have marked unique=true,same data of different invoices
still appear. I even tried by delete the invoice number column but it
did not help

Regards
lalaexcel

 

[Debra Dalgleish]

The 'unique records only' option will return records where the entire
record is unique, not just one field. If you describe the data, and the
criteria you're using, perhaps someone can help you return the results
you expect.

 

[lalaexcel]

The worksheet that I am using has five columns

invoice no.
customer
materials
work
price

There are chances of same customer ,material,work appear together wit
different prices in different invoices, though they are expected to b
exactly the same. My objective is to find them out.

Thank you

lalaexce

 

[Peo Sjoblom]

So you want to extract the values where the customer, material and work
occurs more than once?
One possible way, assume the whole table with headers are

A4:E30, leave G1 blank and in G2 put

=SUMPRODUCT(--($B$5:$B$28&$C$5:$C$28&$D$5:$D$28=B5&C5&D5))>1

now select A4:E30, do data>filter>advanced filter, select copy to another
location, in the criteria range put

$G$1:$G$2

click OK

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Peo Sjoblom]

Oops,

change B28 to B30 in the formula

now if you want to remove those that occurs more than 1 change the criteria
formula in G2 to

=SUMPRODUCT(--($B$5:$B5&$C$5:$C5&$D$5:$D5=B5&C5&D5))<2

will just filter the rows where same customer, same materials and same work
occurs once

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon