duplicate count stats forumla

I have an excel report, and in it are 2 fields named number and code. (columns N and O). In column T I want a sum total of each time that specific row of data's combination of name and number appears in the dataset. Similar to a count and group by clause in access, but rather than just a filtered list of each unique combination of number and code, with a count of how many times it appears in the data set, I want column T to show that for this row of data, that this number and code appears 5 other times in the dataset. what formular could be applied in column T to get me these stats. I can do the group by and count in access but that doesnt help here...
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

=SUMPRODUCT(--(N:N&O:O=N1&O1))-1

Open in new window

Regards
helpfinderIT ConsultantCommented:
could you please attach sample excel to see exactly data structure?
pma111Author Commented:
@Rgonzo1971 - that doesnt seem to of worked. as if you data filter on coumn T, its saying one of the combinations has over 120 "matches", so if you filter on that value, you'd expect at minimum 120 rows of data to be returned, but it only filters the data and returns about 10 rows!
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Rgonzo1971Commented:
Could you send a dummy
pma111Author Commented:
and those that it does filter on all have completely random number and codes.
pma111Author Commented:
I cant really upload any data as its confidential.. bu tnumber is in 00000000 format, and code is in 000000 format.
Rgonzo1971Commented:
You have to filter on N and O not the calculation
pma111Author Commented:
is there no way to do it so we can filter on "duplicates", i.e. the calculation, as that is all we are interested in. Otherwise we need to go through a huge number of records ..
pma111Author Commented:
still not convinced it works.. as there is one particular combo on number and code that appears 140 times in the data. the way this formula works is the calculation indicates there are over 10 instances of 140 duplicate combinations which is not true..
Rgonzo1971Commented:
then try ( and filter greater then 0

=SUMPRODUCT(--(N$1:N1&O$1:O1=N1&O1))-1

Regards
EE20150930.xlsm
pma111Author Commented:
sorry... still no luck, if I filter on column T for the combination with an apparent 121 matches, if filters the data and returns  a single rown, when it should be returning 121 rows all with the same number and code combination. When I take the "121" filter off column T, and manually filter for the value shown, that combination is only in the data once, not 121 times?
pma111Author Commented:
column T should be representing how many times the combination of number and code (n and o) appear in the entire data. So if there are 200 rows with the same number and code, column T should say 200, and for each of the other 199 rows with the same combination, that should also say 200, so when I filter on 200, it should show me all the rows with that same combination of number and code.
Rgonzo1971Commented:
then try this
EE20150930_1.xlsm
ProfessorJimJamMicrosoft Excel ExpertCommented:
look,

in column P put this formula  starting from second row put =CONCATENATE(N2,O2)

then select the entire column P and go to conditional formatting and then highlight cell rules and then duplicate values.

is that what are u looking for?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pma111Author Commented:
still no working, first one I filtered on said there were 17 rows with the same combination of N and O, so fitlered on 17, expecting 17 rows of data and it returns 1 row! I took the filter off and manually search for that combo and it appears 2 times in the data. I dont know if the format of the data is affecting this, they are in 00000000 and 000000 format, but I think excel thinks they are actually text!
pma111Author Commented:
@ProfessorJimJam, kind of.. but a count of how many duplicates per combination in a column would have been ideal, so I can focus on those with a larger amount of duplicates first.
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.