Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Duplicates in Groups | Excel Worksheet Functions | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
Excel - Sorting groups in groups due to subtotaling | Excel Worksheet Functions | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) |