×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Identiying records that meet a peculiar condition2

Identiying records that meet a peculiar condition

(OP)
hello

I have a question that has me stumped.

Consider a large table as shown below...

ID A B X1 X2 X3 X4 X5 X6
====================================================================
1000 1 6 1 6 6 10 6 1
1001 3 15 15 15 3 3 3 3
1002 13 8 13 8 13 8 8 13
1003 34 16 99 16 16 34 16 34

etc …

I need to be able to identify all ID values where the A and B values are repeated exactly 3 times each in the X columns. In this table sample, only ID = 1002 has this property. The X columns have exactly 3 values of 13, and 3 values of 8.

Much thanks for any ideas.
Vicky

RE: Identiying records that meet a peculiar condition

  ID   A  B X1 X2 X3 X4 X5 X6
============================
1000  1  6  1  6  6 10  6  1
1001  3 15 15 15  3  3  3  3
1002 13  8 13  8 13  8  8 13
1003 34 16 99 16 16 34 16 34

Don't you agree that this would be a lot easier to see?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Identiying records that meet a peculiar condition

Try

CODE --> SQL

Select ID
From tablename
Group by Id
Having sum(a+b)=Sum(X1+ X2 +X3 +X4+ X5 +X6)/3 

RE: Identiying records that meet a peculiar condition

PWise,
I'm not sure why you are using Sum() when you reference only a single row. I think you could get a lot of false positives. Consider

A	 B	X1	X2	X3	X4	X5	X6
4	33	34	2	27	5	40	3 

RE: Identiying records that meet a peculiar condition

@PWise, I like your approach. However this row would also count as the sum is 7...
1004	1	6	5	5	5	2	2	2


I think that the table should be normalized (using this process in Excel FAQ68-5287: NORMALIZE Your Table using the PivotTable Wizard)...
]
ID	A	B	Field	Value
1000	1	6	X1	1
1000	1	6	X2	6
1000	1	6	X3	6
1000	1	6	X4	10
1000	1	6	X5	6
1000	1	6	X6	1
1001	3	15	X1	15
1001	3	15	X2	15
1001	3	15	X3	3
1001	3	15	X4	3
1001	3	15	X5	3
1001	3	15	X6	3
1002	13	8	X1	13
1002	13	8	X2	8
1002	13	8	X3	13
1002	13	8	X4	8
1002	13	8	X5	8
1002	13	8	X6	13
1003	34	16	X1	99
1003	34	16	X2	16
1003	34	16	X3	16
1003	34	16	X4	34
1003	34	16	X5	16
1003	34	16	X6	34
1004	1	6	X1	5
1004	1	6	X2	5
1004	1	6	X3	5
1004	1	6	X4	2
1004	1	6	X5	2
1004	1	6	X6	2


So my result is 1002.

Then my SQL ...

CODE

Select ID

From (

SELECT
n1.ID
, count(n1.Value)

FROM C:\Users\Skip\Documents\tt_tableCount.xlsx.Norm$ n1 Where n1.A=n1.Value Group By n1.ID Having count(n1.Value)=3 union all SELECT n1.ID , count(n1.Value) FROM C:\Users\Skip\Documents\tt_tableCount.xlsx.Norm$ n1

Where n1.B=n1.Value

Group By n1.ID

Having count(n1.Value)=3

)

Group by ID

Having count(ID)=2 
..of course, your path and Sheet/Table Name might be different (my Sheet name is Norm)

Skip,

Just traded in my OLD subtlety...
for a NUance!

RE: Identiying records that meet a peculiar condition

(OP)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!