count duplicate records in a group

[Mparekh]

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 i
column B for the group of records in Column A. For e.g. for recor
12345 in column A, 1 in column B gets repeated twice and so on and s
forth or in other words, for record 12345 in column A, group 1,2,3 i
column B gets repeated twice.

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

Thank You.
Parek

 

[Guest]

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,

 

[wjohnson]

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.

 

[Mparekh]

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.

 

[Mparekh]

Ashish, Thanks! I modified your suggested formula a bit and it worked.

Thanks again