Duplicates by groups
 

I am trying to identify duplicates between two columns. Column A has a
number of customers. Column B has their purchases. I want to identify any
duplicate records in column B for each group of customers in column A. Any
ideas?

See example below. In this example, I would like to highlight/identify Eggs
for Joe (as Eggs are the duplicate in Column B); Cereal and Bread for Susan;
Pete is fine (no duplicates).

I would then need to create a report that would give me Joe and only Eggs
(once) and Apples; Susan Bread (once) and Cereal (once); etc.

Column A Column B
Joe Eggs
Joe Eggs
Joe Apples
Susan Cereal
Susan Bread
Susan Cereal
Susan Bread
Pete Milk
Pete Eggs

Duplicates by groups
 

In C1 use =SUMPRODUCT((A1:$A$99=A1)*(B1:$B$99=B1))-1
Copy down the column
Select all the C entries; copy and use Edit | Paste Special Values (to make
the formulas into values)
Sort all the data by column C
Copy and Past the entries with C values greater than 0


OR learn to use a Pivot Table

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"WalterAndersen" wrote in message
...
I am trying to identify duplicates between two columns. Column A has a
number of customers. Column B has their purchases. I want to identify
any
duplicate records in column B for each group of customers in column A.
Any
ideas?

See example below. In this example, I would like to highlight/identify
Eggs
for Joe (as Eggs are the duplicate in Column B); Cereal and Bread for
Susan;
Pete is fine (no duplicates).

I would then need to create a report that would give me Joe and only Eggs
(once) and Apples; Susan Bread (once) and Cereal (once); etc.

Column A Column B
Joe Eggs
Joe Eggs
Joe Apples
Susan Cereal
Susan Bread
Susan Cereal
Susan Bread
Pete Milk
Pete Eggs

Duplicates by groups
 

Thanks for the info. One more question...

I only want one sales figure as well. Will that formula work (SUMPRODUCT)?

Example
Column A Column B Column C
Joe eggs $5
Joe eggs $5
Joe bread $2
Joe eggs $5
Joe cereal $3
Joe bread $2
Joe eggs $5

I would want to only see the following:
Column A Column B Column C
Joe egss $5
Joe bread $2
Joe cereal $3

"Bernard Liengme" wrote:

In C1 use =SUMPRODUCT((A1:$A$99=A1)*(B1:$B$99=B1))-1
Copy down the column
Select all the C entries; copy and use Edit | Paste Special Values (to make
the formulas into values)
Sort all the data by column C
Copy and Past the entries with C values greater than 0


OR learn to use a Pivot Table

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"WalterAndersen" wrote in message
...
I am trying to identify duplicates between two columns. Column A has a
number of customers. Column B has their purchases. I want to identify
any
duplicate records in column B for each group of customers in column A.
Any
ideas?

See example below. In this example, I would like to highlight/identify
Eggs
for Joe (as Eggs are the duplicate in Column B); Cereal and Bread for
Susan;
Pete is fine (no duplicates).

I would then need to create a report that would give me Joe and only Eggs
(once) and Apples; Susan Bread (once) and Cereal (once); etc.

Column A Column B
Joe Eggs
Joe Eggs
Joe Apples
Susan Cereal
Susan Bread
Susan Cereal
Susan Bread
Pete Milk
Pete Eggs


.

Duplicates by groups
 

Hello Walter,

Select a sufficiently long area with 3 columns and array-enter:
=Pfreq(A1:A999,B1:B999,C1:C999)

Pfreq you can find he
http://sulprobil.com/html/pfreq.html

Regards.
Bernd

Duplicates by groups
 

Bernd - thanks for the info.

I am getting an error when I run the fomula. Any ideas? The error is
"#NAME?".

"Bernd P" wrote:

Hello Walter,

Select a sufficiently long area with 3 columns and array-enter:
=Pfreq(A1:A999,B1:B999,C1:C999)

Pfreq you can find he
http://sulprobil.com/html/pfreq.html

Regards.
Bernd
.

Duplicates by groups
 

Did you try withOUT going to the site and getting the function?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WalterAndersen" wrote in message
...
Bernd - thanks for the info.

I am getting an error when I run the fomula. Any ideas? The error is
"#NAME?".

"Bernd P" wrote:

Hello Walter,

Select a sufficiently long area with 3 columns and array-enter:
=Pfreq(A1:A999,B1:B999,C1:C999)

Pfreq you can find he
http://sulprobil.com/html/pfreq.html

Regards.
Bernd
.

Duplicates by groups
 

Hello,

Press ALT + F11, insert a new macro module, copy the macro text from
http://sulprobil.com/html/pfreq.html into the new module and go back
to your worksheet. Should work...

Regards,
Bernd