+ Reply to Thread
Results 1 to 5 of 5

count duplicate records in a group

  1. #1
    Registered User
    Join Date
    10-04-2004
    Posts
    13

    count duplicate records in a group

    Here's the set of data I am struggling with:

    Column A Column B
    12345 1
    12345 2
    12345 3
    12345 1
    12345 2
    12345 3
    12346 1
    12346 2
    12346 1
    12346 2

    Am trying to write a formula that would count duplicate records in column B for the group of records in Column A. For e.g. for record 12345 in column A, 1 in column B gets repeated twice and so on and so forth or in other words, for record 12345 in column A, group 1,2,3 in column B gets repeated twice.

    Is there a formula that would help me derive that? Pleasseee help, have been struggling with this since last two days and finally need to pick your excel brains on this.

    Thank You.
    Parekh
    Last edited by Mparekh; 12-20-2005 at 10:36 PM.

  2. #2
    Ashish Mathur
    Guest

    RE: count duplicate records in a group

    Hi,

    You may try the following array formula (Ctrl+Shift+Enter). the data is in
    the format below (in range A1:B7)

    12345 1
    12345 2
    12345 3
    12345 1
    12345 2
    12345 3
    12345 1

    In A9 enter, 12345 and in B9 enter 1. in C9, enter the following array
    formula COUNT(IF(($A$1:$A$7=A9)*($B$1:$B$7=B9),B1:B7))

    You may chage the value in B9 to 2 or 3.

    Regards,



    "Mparekh" wrote:

    >
    > Here's the set of data I am struggling with:
    >
    > Column A Column B
    > 12345 1
    > 12345 2
    > 12345 3
    > 12345 1
    > 12345 2
    > 12345 3
    > 12346 1
    > 12346 2
    > 12346 1
    > 12346 2
    >
    > Am trying to write a formula that would count duplicate records in
    > column B for the group of records in Column A. For e.g. for record
    > 12345 in column A, 1 in column B gets repeated twice and so on and so
    > forth or in other words, for record 12345 in column A, group 1,2,3 in
    > column B gets repeated twice.
    >
    > Is there a formula that would help me derive that? Pleasseee help, have
    > been struggling with this since last two days and finally need to pick
    > your excel brains on this.
    >
    > Thank You.
    > Parekh
    >
    >
    > --
    > Mparekh
    > ------------------------------------------------------------------------
    > Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
    > View this thread: http://www.excelforum.com/showthread...hreadid=495089
    >
    >


  3. #3
    Forum Contributor
    Join Date
    12-14-2005
    Posts
    176

    Count

    If you want something that looks like the following:

    Count of Part B Part B
    Part A 1 2 3 (blank) Grand Total
    12345 2 2 2 6
    12346 2 2 4
    (blank)
    Grand Total 4 4 2 10

    I put Part A at the top of Column A, Part B at the top of Column.
    You can do this very easily in a PIVOT table.
    I put part A as the row info. Part B as the column and data field.
    Try it a couple of different ways to experiment with how you want to see the info.

  4. #4
    Registered User
    Join Date
    10-04-2004
    Posts
    13
    Ashish, Thank you for the formula. The problem I am facing is, Column A has various groups of data against which Column B has sub groups of data, as I had listed earlier. So it would not be possible for me to enter 12345 in A9 and 1 in B9. That was just an example to be more explicit on what I am trying to do. I would need an array formula which would look at data in Column A then recurring data sub groups in Column B and count those sub groups in Column B and display the result in Column C.

    Pivot tables would not help me much at this point as I need to use the data in Column C for further calculations in the spreadsheet.

    Thank you in advance.

  5. #5
    Registered User
    Join Date
    10-04-2004
    Posts
    13
    Ashish, Thanks! I modified your suggested formula a bit and it worked.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1