Removing Duplicates with rules

[guy13]

Hi all,

Interesting problem which I hope someone can help me with - trying to remove duplicates of company names with rules. I have been able to remove the duplicates of names and display the non duplicates but now I want to add a rules that if column B = "n" do not display this company.

Best way to understand is to look at the excel file attached.

Thanks for your help in advanced.

[oeldere]

with an filter on column B?

Before filtering store the other data on sheet 2, so they don't get deleted.

[guy13]

Thanks for the reply. I need this to be done on the fly as one sheet has data on and the other displays it.

The problem is some companies might have "y" and "n" but if it has 1 "y" i want it to be displayed. its quite a complex problem.

[Fotis1991]

Hi

Using a helper and hidden column. Let's say column D. So in D2 and copy down.

Please Login or Register  to view this content.

In E7 and copy down this ARRAY formula.

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[guy13]

Cant get this to work can you send me an excel file?

[sktneer]

Try these Array Formulas. Array Formula requires special keystrokes Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular excel formula.

in E7

Please Login or Register  to view this content.

and copy down until you get blank cells.

In F7

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

[Fotis1991]

..............................

[guy13]

Excellent work sktneer.

I have a problem where one company might have "y" and "n" but I still want this to be displayed. The problem with your forumlar is it looks at the first one.

[sktneer]

In that case the formulas would be like this.....

In E7

Please Login or Register  to view this content.

In F7

Please Login or Register  to view this content.

[guy13]

You are a genius

[sktneer]

Thanks for the feedback.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.