Excel 2016 Formula 1

[unmlobo]

Im try to modify data in a spread sheet (see image attached). I know this is an IF statement. 1. IF Column A is the same number, 2. AND has both 14 and 18 in Column B, 3. THAN delete BOTH Columns. Im just stuck on the Syntax. Thanks for the help!

 

[SkipVought]

Hi,

You can’t delete data with formulae.

A formula returnns a value.

Did you intend to write a macro to modify the data on your sheet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's what you could do.

I inserted a heading row.

I Named the ranges based on the headings.

I used the following formula to COUNT the occurrences of 14 and 18 in like groups.
[tt]
C2: =SUMPRODUCT((Column1=A2)*(Column2=14))+SUMPRODUCT((Column1=A2)*(Column2=18))
[/tt]
Then use the AutoFilter feature to either filter out those rows or manually DELETE the offending rows.

Column1	Column2	Delete
24	14	2
24	12	2
24	18	2
24	13	2
23	14	1
23	13	1
23	12	1
25	18	2
25	10	2
25	14	2
26	12	0
26	13	0
26	10	0

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[unmlobo]

Skip, Thanks for the help. I get what your doing. I setup your data set to try and get the same answers as you but luck. In C3 is where I have placed you code but I have a feeling that is not where is supposed to be. Where am I going wrong? Thanks again!

 

[SkipVought]

1) SELECT all data including headings

2) Formulas > Defined Names > Create from selection > CHECK - Create names from values in the TOP row

MAGIC... your formula works!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[unmlobo]

That was Brilliant!! It worked!! Thank You!!!

 

[SkipVought]

BTW, Named Ranges are a VERY powerful feature. I use Named Ranges a LOT.

In conjunction, there are Structured Tables that use Table Ranges along with other super advanced features.

These features are well worth exploring and asking question about here.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]