Find Rows that have a different value

[lmoran89]

This sounds like it should be simple, but I am struggling. Thank you in advance

I have 2 columns of data similar to the below (but 44,000 rows). First column is an account number, second is an identifier. I need to identify the where an account number has both a 2 and 1.

[TABLE="width: 95"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]212210[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]212210[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[BlueHornet]

Welcome to the board.

Yeah, this is an ugly formula, for sure. Interesting problem, though. Assuming your Account values start at A1, then:
=IFERROR( IF( AND( FIND( "1", A1), FIND( "2", A1)), "both", "?"), IFERROR( IF( FIND( "1", A1), "1", "??"), IFERROR( IF( FIND( "2", A1), "2", "???"), "neither")))

 

[Joe4]

Welcome to the Board!

All things being equal, I think this is something that is probably easier accomplished in Microsoft Access, through a "Grouped" and a "Totals" Query. Access may handle that amount of data a little more efficiently too.
However, I realize that you may not have access to Access. If you do, though, let me know, and I can probably help you get what you need.

 

[lmoran89]

Joe4 - Agreed this is something that should be in Access. I had already loaded it, but your help is appreciated

 

[Joe4]

Let's say the table named is "Table1" and the field names are "AccountNumber" and "Identifier", and the possible value for Identifier are the numeric values 1 and 2.
Then, go to Query Design, change to SQL View, and paste this query code in:

SELECT AccountNumber
FROM Table1
GROUP BY AccountNumber
HAVING (Sum(IIf([Identifier]=1,1,0))>0) AND (Sum(IIf([Identifier]=2,1,0))>0);

This should return all AccountNumbers who have both 1 and 2.

 

[lmoran89]

Thank you. I'm getting the "Enter Parameter Value" on the Identifier field though. The field was initially text, but changed the data type to number, but still getting that message

 

[Joe4]

Whenever you see that, and you didn't set a parameter, it typically means you have a typo in the field name (in this case, the field name you are using in the query here does not exactly match the field name in the table).

 

[lmoran89]

Got it!

Thank you so much for your help

 

[Joe4]

You are welcome.