Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match data duplicates
I have two columns of data and there are dublicate entries which I need to
group together: Data: A1 B1 Dog Cat Elephant Lion Fish Shark Cat Dog Shark Fish Lion Elephant I group the entries so they appear as follows: Dog Cat Cat Dog Fish Shark Shark Fish Does this makes sense? Can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match data duplicates
In cell C1 enter the below formula. Please note that this is an array
formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" (in one line) =IF(A1="","",IF(ISNA(MATCH(1,($A$1:$A$100=B1)*($B$ 1:$B$100=A1),0)),"","Duplicate")) The formula will return 'Duplicate' if there is a duplicate entry. Copy down as required and once done sort column C to get your list..Try and feedback If this post helps click Yes --------------- Jacob Skaria "belpal" wrote: I have two columns of data and there are dublicate entries which I need to group together: Data: A1 B1 Dog Cat Elephant Lion Fish Shark Cat Dog Shark Fish Lion Elephant I group the entries so they appear as follows: Dog Cat Cat Dog Fish Shark Shark Fish Does this makes sense? Can anyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match data duplicates
Hi there
Thanks so much! This is great in identifying the duplicates, but the problem I have is then sorting the duplicates so that they are beneath one another, i.e. Dog Cat Cat Dog Any suggestions? "Jacob Skaria" wrote: In cell C1 enter the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" (in one line) =IF(A1="","",IF(ISNA(MATCH(1,($A$1:$A$100=B1)*($B$ 1:$B$100=A1),0)),"","Duplicate")) The formula will return 'Duplicate' if there is a duplicate entry. Copy down as required and once done sort column C to get your list..Try and feedback If this post helps click Yes --------------- Jacob Skaria "belpal" wrote: I have two columns of data and there are dublicate entries which I need to group together: Data: A1 B1 Dog Cat Elephant Lion Fish Shark Cat Dog Shark Fish Lion Elephant I group the entries so they appear as follows: Dog Cat Cat Dog Fish Shark Shark Fish Does this makes sense? Can anyone help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match data duplicates
Hi,
What do you mean by group together? In other words it looks like you are grouping Cat/Dog together, although I don't understand? Do you really want to group them on different lines and are Cat/Dog a group and if so how do you define a group? If you simply want to know if there are duplicates in the two columns: Highlight column A's data and use conditional formatting: To conditionally format your cell(s): In 2003: 1. Select the cells you want to format, in this case column A 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(B$1:B$9=A1) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =OR(B$1:B$9=A1) 5. Click the Format button and choose a format. 6. Click OK twice You can repeat the process on column B -- If this helps, please click the Yes button. Cheers, Shane Devenshire "belpal" wrote: I have two columns of data and there are dublicate entries which I need to group together: Data: A1 B1 Dog Cat Elephant Lion Fish Shark Cat Dog Shark Fish Lion Elephant I group the entries so they appear as follows: Dog Cat Cat Dog Fish Shark Shark Fish Does this makes sense? Can anyone help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match data duplicates
Hi there
I do need to group the two sets of data together. Another example is people going together as a couple to a party. They each respond to the invitation saying who their respective partners are. A) I am now able to check duplicates and that everyone has responded and detailed who their partner is. B) I then need to put the couples and group them together/I need to be able to match this corresponding data set together: Initial set of data: Bonny Clyde Elvis Pricilla Elizabeth Phillip Romeo Juliet Pricilla Elvis Clyde Bonny Juliet Romeo Philip Elizabeth Matched set of data duplicates sorted so that they appear after one another on my spreadsheet: Bonny Clyde Clyde Bonny Elvis Pricilla Pricilla Elvis Romeo Juliet Juliet Romeo Elizabeth Philip Philip Elizabeth Does this make sense? "Shane Devenshire" wrote: Hi, What do you mean by group together? In other words it looks like you are grouping Cat/Dog together, although I don't understand? Do you really want to group them on different lines and are Cat/Dog a group and if so how do you define a group? If you simply want to know if there are duplicates in the two columns: Highlight column A's data and use conditional formatting: To conditionally format your cell(s): In 2003: 1. Select the cells you want to format, in this case column A 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(B$1:B$9=A1) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =OR(B$1:B$9=A1) 5. Click the Format button and choose a format. 6. Click OK twice You can repeat the process on column B -- If this helps, please click the Yes button. Cheers, Shane Devenshire "belpal" wrote: I have two columns of data and there are dublicate entries which I need to group together: Data: A1 B1 Dog Cat Elephant Lion Fish Shark Cat Dog Shark Fish Lion Elephant I group the entries so they appear as follows: Dog Cat Cat Dog Fish Shark Shark Fish Does this makes sense? Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index,match - how to avoid same lookup when duplicates present | Excel Worksheet Functions | |||
Cross match data in Col A v/s Col B and display match in Col 3 | Excel Discussion (Misc queries) | |||
Using the MATCH formula and list the duplicates | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
Offset & Match Formula Shows Duplicates | Excel Worksheet Functions |