Number of multiple records

  • Thread starter Thread starter Brenda Hutton
  • Start date Start date
B

Brenda Hutton

I have 21,000 records in a database. Some of those records have a single
code associated with them, some have multiple codes. I need to figure out
the number of records that have more than one code.

Data looks like:

Number Code
1234 A
2345 B
1234 B

I need to be able to figure that there is (1) record here containing
multiple codes. How is this done please?
 
I have 21,000 records in a database. Some of those records have a single
code associated with them, some have multiple codes. I need to figure out
the number of records that have more than one code.

Data looks like:

Number Code
1234 A
2345 B
1234 B

I need to be able to figure that there is (1) record here containing
multiple codes. How is this done please?
Click to expand...

I can't help thinking this is possible by just using SQL, but if it is I
can't figure it!

So, I've done it this way

Use the following query to get your results

SELECT DISTINCT Number, Code FROM TableName ORDER BY Number

Bring these results back into a separate worksheet.

Your results will be a list of unique number and code variations ordered by
number, so for your example above the output would be:

1234 A
1234 B
2345 A

If your example had both 1234 numbers set to A the output would be:

1234 A
2345 B

Now, add the following formula to column c of your output range:

COUNTIF(A:A,A1)

You shold see the following in the output range now:

1234 A 2
1234 B 2
2345 A 1

All you need to do is loop through each row in the output range with a bit
of VB and remove all rows with a 1 in column C so your output range will be:

1234 A 2
1234 B 2

An alternate method would be to put a filter on your output range and only
display rows where the third column is greater than 1

Hope this helps.

Chris
 
Thanks Chris. Unfortunately you lost me with "All you need to do is loop
through each row in the output range with a bit of VB". I was given a web
site for cpearson.com which had an article on "Tagging Duplicate Entries".
I was able to muddle my way through that and did come up with a workable
multi-step process similar to yours that worked. Thanks for the help.
 
Back
Top