finding duplicates with condition

[rocki]

First off Please tell me if I have failed in any way to wrap or format anything in this post. I dont think the text I put here is code, but I want to be sure,
after receiving a moderators infraction for failing to properly wrap code in a previous post. Thanks.

Now:

I have a wks in which the first column is a list account #'s and the folowing columns are specifics of transactions or interactions.

Lets say it is a movie rental customer list that lists each rental, and column "A" is the customer number, column "B" declares if it was returned late.

I need to compile a list of "all" rentals by customers who have "EVER" had a "late return" or a "YES" in column "B".

I need to find all account records/rows of accounts that at any time had a "YES" in column "B" even if the some or many of that customers rentals/entries have "NO" in column "B"

so say:

1---------A---------------B-----------------C--------
2-------123------------yes--------------yes--------
3-------123------------no---------------yes--------
4-------123------------no---------------yes------
5-------156------------yes--------------yes-----
6-------298------------no----------------no------
7-------298------------no----------------yes---
8-------356------------no----------------yes----
end
I would want to end up with a list like this:

1--------A---------------B------------------c--------
2--------123-----------yes----------------yes------
3--------123------------no----------------yes----
4--------123------------no----------------yes-----
5--------156-----------yes-----------------no----
end

notice only the two entries of 298 and one entry of 356 were removed because those customers never had "yes" in column "B".

I would ask not just what the formula would be but where an how to input/apply it.

Never done a filter/sort like this before.

Thanks.

[rocki]

Also please tell me if I've posted this in the wrong locations..

This may be a VBA programming thread but I believe this is the right location.

Thanks.

[mikerickson]

VB Programming is not needed, Advanced Filter can do this.

Put headers on each of your columns.

And use a CustomFormula Criteria Range. A 2 row X 1 column Criteria range that is blank in the first row and having this formula in the second row will show the records you want. (If you have more than 99 records (increase the size of the ranges.)
=0<SUMPRODUCT(--($A$2:$A$100=A2),--($B$2:$B$100="Yes"))

[Simon Lloyd]

This is a macro, you enter it by pressing Alt+F11 and then right click in the vba project left hand side and choose insert module then past this code in:

Please Login or Register  to view this content.

you can run it from the VBE (the place where you added this code) or you can add a command button or keyboard shortcut to run it. The code will copy all found occurrences to sheet 2!

[rocki]

ok, so the colums have headers. The "customer number" is Column "A" and "CK" is the column that is the equivilent of column "B" in my example.

there are about 6500 rows. yes, 6 5 0 0 .

and if it matters the "A" / customer number column is made up of alphanumerics with dashes in some with upt to 12 characters.

so chck me on this

Please Login or Register  to view this content.

[mikerickson]

yes that looks like what you would need.
You also need unique headers in each cell of A1:CK1 (="header"&COLUMN() might be a way to generate those)
And a blank column between your data and the criteria range.

[rocki]

also I altered your code since the column with the "Yes" is "CK" and the output page needs to be sheet 6

So I highligted sheet one in the VBE (named "Search Results")

hit insert module

and pasted this

Please Login or Register  to view this content.

check me on my edits..

And ran it via the "RUN" feature and choosing the "find_late"

it worked on it but I didn't get any results on sheet6 (completely blank)or anywhere else.

FYI, I do have a userform in this spreadsheet with macros already added if that might interfere.

I will be trying Mikes way while waiting..

I absolutely love seeing the various ways that the same goal can be accomplished.

Thanks

[rocki]

mikerickson

the colums do have headers.
I tried what I thought I understood you instructing.

and that was to highlight all the data
goto the DATA tab
in "Sort & Filter"
click "Advanced" which pulls up the "Advanced Filter" and autoloads the "list Range"

I then pasted in

Please Login or Register  to view this content.

and hit "OK"

And got "Reference is not Valid" error popup.

Where am I blowing it?

[rocki]

Here is a very quick mockup that I think might help. with "CK" being the "Late" column, and sheet6 as the potential output page if there is to be one in the case of the VB route.

If you could get the formula or VB to work on this I could see them in action which is typically how I best understand this.

the real workbook is obviously much more convoluted and, well HUGE, but I figure if you can make it work on this, I can modify it to work on the master.

Thanks guys for bearing with me.

[Simon Lloyd]

also I altered your code...
check me on my edits..

And ran it via the "RUN" feature and choosing the "find_late"

it worked on it but I didn't get any results on sheet6 (completely blank)or anywhere else.

FYI, I do have a userform in this spreadsheet with macros already added if that might interfere.

I will be trying Mikes way while waiting..

I absolutely love seeing the various ways that the same goal can be accomplished.

Thanks

As you didn't give correct parameters in the first place i could not guess which column you were working with, try this:

Please Login or Register  to view this content.

[rocki]

I'm stumped.

I tried multiple variations and experiments with both methods, VBE and with Advanced Filter and I cant get anything to work.

I would greatly appreciate anyone even making the example I uploaded work in any way.

Thanks.

[rocki]

sorry, posted the Stumped reply right before I saw your last response Simon.

One more parameter that might be an issue:

Sheet4 is the sheet I am trying to filter, with sheet6 being the output, or even just filter sheet4 and I can make a pivot or something to show it.


I've tried it and keep getting

Run-time error '9':
Subscriipt out of range

which highlights

Please Login or Register  to view this content.

[Simon Lloyd]

sorry, posted the Stumped reply right before I saw your last response Simon.

One more parameter that might be an issue:

Sheet4 is the sheet I am trying to filter, with sheet6 being the output, or even just filter sheet4 and I can make a pivot or something to show it.


I've tried it and keep getting

Run-time error '9':
Subscriipt out of range

which highlights

Please Login or Register  to view this content.

Yep, the fault is with your naming of the sheet, its probably a typo, is it definately Sheet6 or is it Sheet 6 or does it have another name?

[mikerickson]

Criteria for Advanced Filter have to be put on a sheet, not the dialog box.
What I did with your file:
1) Turn Auto-Filter off
2) put headers in Sheet1! C1:CJ1
3) put =0<SUMPRODUCT(--($A$2:$A$6500=A2),--($CK$2:$CK$6500="Yes")) in Sheet1!CN2
4) Selected Sheet6
5) pressed AdvancedFilter, selected Copy To Different Location and entered
List Range: Sheet1!$A$1:$CK$14
CriteriaRange: Sheet1!$CN$1:$CN$2
Copy To: Sheet6!$A$1

And the 9 rows(including headers) were copied to sheet 6.

When using Advanced Filter between sheets, if one is using Copy to other location, the destination sheet needs to be active when AdvancedFilter is pressed. If FilterInPlace, the sheet with the Criteria Range must be active.

[rocki]

Ok.. Interesting turn of events.
And an interesting math/programming quandary.

So I was able to get both methods to function. (very excited)

Now here comes the interesting part. They have different outputs. Different totals.

I think the Advanced Filter method was accurate (still need to run exhaustive tests on control groups but I'm 99% sure)

They both collected all the Customer Rows with "yes" in "CK"

And

Both found the Customer Rows with the same customer numbers that at any time had a "yes" in "CK"

However the VBE found strange multiples of duplicates greater than 1.

So well.. I think it would be easier to show you.
the first list is VB results, the second the Advanced Filter

Please Login or Register  to view this content.

Now the interesting thing and the reason I chose this grouping, it that originally I thought the VB was doubling anything greater than 1. then I looked and thought it was actually squaring (2x2, 3x3, 5x5 etc) but then I found a 5= 25 then I noticed a 7=21 and a 14=84.. so that shot those theories..

So I figure it seems as though the VB actually counts all Customer entries "EACH" time there is a "yes", thus recounting all entries with each "yes"

So taking the 14=84 instance,.. there were "6" "yes" entries from that customer 199906330.. and 14 total entries.. so it actually counted all 14 every time there was a "yes" totaling 14x6 "84"

And if a customer had 5 entries, and all were "yes" then it totaled 25.
or if a customer had 5 entries and only 1 was "yes" it would total 5
if customer had 5 entries and 2 "yes" it would total 10.


Ideally I would love to figure a way to get the VB working, as once it is working I can easily integrate it (ok , maybe not easily for me, but potentially) into a standardized function in my Userform.

So... thoughts???