| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Duplicates
Hello,
I have Col.A with name of the cities and Col.B with its state. They contain duplicates. I'm looking for a way that matches col.A and B and look for duplicates. col A and B shoulb be considered as one becase there may be a same city name, but in different US state. I want excel to count the duplicates and put the number of repeating cities next to them in Col.C and highlight all duplicates. ie. A B C Concord CA San Ramon CA Anaheim CA Concord TX San Ramon CA Concord CA San Ramon CA Result. A B C Concord CA 1 San Ramon CA 2 Anaheim CA 0 <==Zero or nothing Concord TX 0 San Ramon CA 0 Concord CA 0 San Ramon CA 0 thx. -- Message posted via http://www.officekb.com |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Duplicates
Hi Saman,
Your sample data does not match your description. For example, the second time that San Ramone CA shows up it is a duplicate and should show 2 off to the right of it, but your sample shows 0. If what you want to do is mark ALL duplicates then this formula will work in column C: =SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-1 The same issue arrises when you want to mark the duplicates - do you mean ALL rows that appear more than once or do you mean all occurances after the first one? To marks ALL rows that contain duplicates you can set up conditional formatting such as Formula Is , =$C10 Select the entire range first with the active cell on row 1 and then choose Format, Conditional Formatting. -- Cheers, Shane Devenshire "saman110 via OfficeKB.com" wrote: Hello, I have Col.A with name of the cities and Col.B with its state. They contain duplicates. I'm looking for a way that matches col.A and B and look for duplicates. col A and B shoulb be considered as one becase there may be a same city name, but in different US state. I want excel to count the duplicates and put the number of repeating cities next to them in Col.C and highlight all duplicates. ie. A B C Concord CA San Ramon CA Anaheim CA Concord TX San Ramon CA Concord CA San Ramon CA Result. A B C Concord CA 1 San Ramon CA 2 Anaheim CA 0 <==Zero or nothing Concord TX 0 San Ramon CA 0 Concord CA 0 San Ramon CA 0 thx. -- Message posted via http://www.officekb.com |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Duplicates
This ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just
Enter) ) returns the values you posted: C1: =MAX(SUM(--(IF(($A$1:$A$7&$B$1:$B$7=A1&B1),MATCH(A1&B1,$A$1:$ A$7&$B$1:$B$7,0),0)=ROW()))-1,0) Copy C1 and paste into C2:C7 Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "saman110 via OfficeKB.com" <u35670@uwe wrote in message news:78d13efce5a84@uwe... Hello, I have Col.A with name of the cities and Col.B with its state. They contain duplicates. I'm looking for a way that matches col.A and B and look for duplicates. col A and B shoulb be considered as one becase there may be a same city name, but in different US state. I want excel to count the duplicates and put the number of repeating cities next to them in Col.C and highlight all duplicates. ie. A B C Concord CA San Ramon CA Anaheim CA Concord TX San Ramon CA Concord CA San Ramon CA Result. A B C Concord CA 1 San Ramon CA 2 Anaheim CA 0 <==Zero or nothing Concord TX 0 San Ramon CA 0 Concord CA 0 San Ramon CA 0 thx. -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Duplicates
Hi,
here is a formula that will return the answers you are showing: =IF(AND(SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-10,SUMPRODUCT(--(A1&B1=$A$1:A1&$B$1:B1))-1=0),SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-1,0) -- Thanks, Shane Devenshire "saman110 via OfficeKB.com" wrote: Hello, I have Col.A with name of the cities and Col.B with its state. They contain duplicates. I'm looking for a way that matches col.A and B and look for duplicates. col A and B shoulb be considered as one becase there may be a same city name, but in different US state. I want excel to count the duplicates and put the number of repeating cities next to them in Col.C and highlight all duplicates. ie. A B C Concord CA San Ramon CA Anaheim CA Concord TX San Ramon CA Concord CA San Ramon CA Result. A B C Concord CA 1 San Ramon CA 2 Anaheim CA 0 <==Zero or nothing Concord TX 0 San Ramon CA 0 Concord CA 0 San Ramon CA 0 thx. -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Duplicates
Here's a slightly longer (by 3 characters), but NON-array version:
C1: =MAX(SUMPRODUCT(--((($A$1:$A$7&$B$1:$B$7=A1&B1)*MATCH(A1&B1,$A$1:$A$ 7&$B$1:$B$7,0))=ROW()))-1,0) Copy that formula down through C7 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... This ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just Enter) ) returns the values you posted: C1: =MAX(SUM(--(IF(($A$1:$A$7&$B$1:$B$7=A1&B1),MATCH(A1&B1,$A$1:$ A$7&$B$1:$B$7,0),0)=ROW()))-1,0) Copy C1 and paste into C2:C7 Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "saman110 via OfficeKB.com" <u35670@uwe wrote in message news:78d13efce5a84@uwe... Hello, I have Col.A with name of the cities and Col.B with its state. They contain duplicates. I'm looking for a way that matches col.A and B and look for duplicates. col A and B shoulb be considered as one becase there may be a same city name, but in different US state. I want excel to count the duplicates and put the number of repeating cities next to them in Col.C and highlight all duplicates. ie. A B C Concord CA San Ramon CA Anaheim CA Concord TX San Ramon CA Concord CA San Ramon CA Result. A B C Concord CA 1 San Ramon CA 2 Anaheim CA 0 <==Zero or nothing Concord TX 0 San Ramon CA 0 Concord CA 0 San Ramon CA 0 thx. -- Message posted via http://www.officekb.com |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Duplicates
All,
Thank you for responding. My result in the ex. returns 0 because I want to know what city has been repeated for how many times. If I get others to show me the repeated dups, I would get confused and won't know which one to delete later. This is Ok if I could get the formula right to do it your way, but when I use your formula all my entries returns 1 even those who has not been repeated. ShaneDevenshire wrote: Hi Saman, Your sample data does not match your description. For example, the second time that San Ramone CA shows up it is a duplicate and should show 2 off to the right of it, but your sample shows 0. If what you want to do is mark ALL duplicates then this formula will work in column C: =SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-1 The same issue arrises when you want to mark the duplicates - do you mean ALL rows that appear more than once or do you mean all occurances after the first one? To marks ALL rows that contain duplicates you can set up conditional formatting such as Formula Is , =$C10 Select the entire range first with the active cell on row 1 and then choose Format, Conditional Formatting. Hello, [quoted text clipped - 28 lines] thx. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200709/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count duplicates once | Excel Discussion (Misc queries) | |||
| Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
| Count excluding Duplicates | Excel Worksheet Functions | |||
| Count Duplicates | Excel Discussion (Misc queries) | |||
| count a group of numbers but do not count duplicates | Excel Worksheet Functions | |||
Linear Mode
