Re: Duplicates by groups
- From: "Bernard Liengme" <bliengme@xxxxxxx>
- Date: Sat, 31 Oct 2009 12:54:25 -0300
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/assistance/2002/articles/xlconPT101.aspx
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"WalterAndersen" <WalterAndersen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BFFB70B8-2C63-4574-BE37-4FB3351C69D8@xxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Duplicates by groups
- From: WalterAndersen
- Re: Duplicates by groups
- References:
- Duplicates by groups
- From: WalterAndersen
- Duplicates by groups
- Prev by Date: Re: Excel 2002 Why VLOOKUP(Extract,B$4:C$8,2,FALSE) ?
- Next by Date: Re: Finding the combo box
- Previous by thread: Duplicates by groups
- Next by thread: Re: Duplicates by groups
- Index(es):
Relevant Pages
|
