Advanced filtering in Excel VBA

[Hypocrates]

Hi,

My problem is pretty trivial but i can't wrap my head around it.

I have a excel file with tons of data, example of the data that matters:

NR COMPLETION
1 100
2 50
3 20
4 100
4 100
4 80
5 100
5 50


So the thing is the numbers are not unique, if the number occurs more than once it means that there are sub"questions".

What i want to do is show all numbers which have ALL sub"questions" completed. With a normal filter i can show all 100% completed data, but it would show that nr 5 for example is completed while it actually hasn't been completed in total.

So basically i want a filter that shows me all 100% completed rows and only show them if ALL rows with the same number also have 100%, if there is one with the same number the row can simply be left out.

It's a real headcracker for me with poor VBA knowledge, anyone that could help?

My rows start at the 3rd row, the numbers are in my I column and the completion status is in the AI column.

Thanks alot for taking the time to read/reply to my post.

Greetings,

hypocrates

[arlu1201]

It will be good if you attach a sample file.

[Hypocrates]

Having trouble uploading a sample since the corporate network blocks pretty much any site that has file sharing.

The example mentioned above should however be enough, what i do now is just filter the second column and check for 100 but as said before it shows that for example 5 is complete when a sub is only at 50%.

The filter that i need would only show nr 1 since it's the only one which has everything completed.

Hope this is clear enough;

Hypocrates

[arlu1201]

Will an entry show as completed only if it is present in 1 row? Like 1?

[Hypocrates]

It may show as completed if all the rows with the same number are 100, in my case only the 1 since it's the only one which has everything complete.

Another example:

1) 1 50
2) 2 100
3) 2 100
4) 2 100
5) 3 50
6) 3 100
7) 4 100
8) 5 100
9) 5 50



In this example row 2,3,4 and 7 have to be visible, the rest should be filtered out since not all with the same number have been completed.

[Andy Pope]

If you can use a helper column then there are a couple of formula to choose from in the attached.

[Hypocrates]

Thanks alot for your answer, i actually found a working formula similar to yours a couple of minutes ago from: http://www.mrexcel.com/forum/showthr...-is-not-unique.

Now I got another problem because of this technique,

The company uses a system to generate this excel and they refuse to/ it might be complicated to get this column and some adjustments in,

My boss needs to periodically download this updated excell file and make some adjustments to it (which i put into a macro).

Is there a way where i can insert some VBA code into this macro to make it put the formula in for each row in column AK, then apply a filter to show only the ones which are true?

If you could make this work, you're my true hero!

(the amount of rows is dynamic, starting from the 3rd row)

Greetings,

Hypocrates

[Hypocrates]

When I simply do this when recording a macro i get this code:

Please Login or Register  to view this content.

Which likely works, the only problem here still is the dynamic range in the selection.autofill, if I have a way to make that rule automatically go to the last row I think i'm pretty much done ?

[Andy Pope]

For example file.

Please Login or Register  to view this content.

[arlu1201]

Use code tags in future. This time, i have inserted them for you. Check my signature for more info on how to do it.

Also, you do not need any of the select & scroll statements. Your as-is code can be shortened to this

Please Login or Register  to view this content.

[Hypocrates]

Thanks alot for your assistance and apologies for the code tags.

Your code works perfectly but it uses my A and B columns whilst my numbers are in the I column and my completions is in AI columns.

Since i have no clue how your code determines these fields i'm having difficulties adjusting it to my columns.

Would it be possible to alter it just a little so it works for my fiels ?

For the rest, really, awesome help in a very short timeframe, hats off.

I might not reply again today since my shift is over but if you do wish to post the alteration, I will definetly be using it tommorrow and thanking you for it!


Greetings,

Hypocrates