Identify Rows Based on 3 Columns

[Irishred]

Hello,

I am currently taking the data into Access in order to do this but I feel like I should be able to do this in Excel but I am not getting the results that I need. I tried various IF and AND functions without success.

I have data in which I am trying to identify the rows where the worksheet does not contain the same serial and version with a one record having a sign of "Pos" and and one record having a Sign of "Neg" When both are present then I want to treat it as an offsest and ignore it. It's only an offset if the Serial and Version are the same and there is at least one Pos and one Neg Sign for each set.

In the below the first two records are an offset as I have a Sign of Pos and Neg for the same Serial and Version.

The next set, Serial 433720 with version 940 has 2 Pos but no Neg so I want to identify this as "No Offset"

The last record, 857405 with version 832 only has a Pos sign so I would want to identify that also as "No Offset"

Serial	Version	Sign
246732	749	Pos
246732	749	Neg
433720	940	Pos
433720	940	Pos
857405	832	Pos

Thank you

 

[StephenCrump]

Perhaps:

	A	B	C	D	E
1	Serial	Version	Sign		Result
2	246732	749	Pos		Offset
3	246732	749	Neg		Offset
4	433720	940	Pos		No Offset
5	433720	940	Pos		No Offset
6	111111	222	Pos		Offset
7	222222	333	Neg		No Offset
8	222222	333	Neg		No Offset
9	111111	222	Pos		Offset
10	111111	222	Neg		Offset
11	857405	832	Pos		No Offset
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=IF(AND(COUNTIFS(A$2:A$11,A2,B$2:B$11,B2,C$2:C$11,{"Pos","Neg"})),"","No ")&"Offset"

It would be good if you could let us know in your Account details what version of Excel you're using, as the best solution will sometimes vary by version.

If you're not using the latest version of Excel, you will need to array-enter the above formula, i.e. Control-Shift-Enter rather than Enter.

 

[Cubist]

What if you have 2 Pos and 1 Neg. Would the 2nd Pos labeled as "Offset"?

 

[Irishred]

Perhaps:

	A	B	C	D	E
1	Serial	Version	Sign		Result
2	246732	749	Pos		Offset
3	246732	749	Neg		Offset
4	433720	940	Pos		No Offset
5	433720	940	Pos		No Offset
6	111111	222	Pos		Offset
7	222222	333	Neg		No Offset
8	222222	333	Neg		No Offset
9	111111	222	Pos		Offset
10	111111	222	Neg		Offset
11	857405	832	Pos		No Offset
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=IF(AND(COUNTIFS(A$2:A$11,A2,B$2:B$11,B2,C$2:C$11,{"Pos","Neg"})),"","No ")&"Offset"

It would be good if you could let us know in your Account details what version of Excel you're using, as the best solution will sometimes vary by version.

If you're not using the latest version of Excel, you will need to array-enter the above formula, i.e. Control-Shift-Enter rather than Enter.I a

I am using Excel 2016, I should have mentioned that. Thank you for the reminder.

Yes, this worked. Thank you so much. I spent a good amount of time attempting to figure this out and then more taking it to Access and back. Really appreciate the help on this !

 

[Irishred]

What if you have 2 Pos and 1 Neg. Would the 2nd Pos labeled as "Offset"?

I tested and it does not mark the second as an offset. I will dedupe as a prestep to running this until I find a way to treat multiple as an offset if there is a negative. Thank you for raising this question.

 

[StephenCrump]

It's only an offset if the Serial and Version are the same and there is at least one Pos and one Neg Sign for each set.

My post #2 took this to mean that a set of 3, say, with 2 Pos and One Neg, would all be marked "Offset".

What if you have 2 Pos and 1 Neg. Would the 2nd Pos labeled as "Offset"?

I tested and it does not mark the second as an offset.

The formula in Post #2 does mark the second as an offset.

So I'm confused about the results you're looking for?

If you want the "Offset" tag to apply only to Pos/Neg pairs, then you could try:

	A	B	C	D	E
1	Serial	Version	Sign		Result
2	A	111	Pos		Offset
3	A	111	Neg		Offset
4	B	222	Pos		Offset
5	B	222	Neg		Offset
6	C	333	Pos		Offset
7	D	444	Neg		No Offset
8	D	444	Neg		No Offset
9	C	333	Pos		No Offset
10	C	333	Neg		Offset
11	E	555	Neg		Offset
12	F	666	Neg		Offset
13	E	555	Neg		Offset
14	F	666	Neg		Offset
15	E	555	Pos		Offset
16	F	666	Pos		Offset
17	E	555	Neg		No Offset
18	F	666	Pos		Offset
19	F	666	Pos		No Offset
20	E	555	Pos		Offset
Sheet1

Cell Formulas
Range	Formula
E2:E20	E2	=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)>MIN(COUNTIFS(A$2:A$20,A2,B$2:B$20,B2,C$2:C$20,{"Pos","Neg"})),"No ","") &"Offset"
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StephenCrump]

I am using Excel 2016

Thanks for this.

It's best to put this in your Account details, so that it's clear in all your threads.