Match data duplicates

[belpal]

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?

 

[Jacob Skaria]

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

 

[belpal]

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?

 

[Shane Devenshire]

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

 

[belpal]

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?