comparison similar rows in Excel
 

Hello,

How (with Excel) I could compare rows with equal contents ? For example:



row A: 1 1 1
row B: 1 0 0
row C: 1 1 1
row D: 1 0 0
row E: 0 0 0


attention: every value in row is in seperate column !



I'd like to create formula which for above exampel indicates: row A is equal
to row C, row B = row D. Row D in the above exampel hasn't similiar row.
In my work I have combination a few dozen rows and I must search out rows
with equal contents in columns. However purpose of my work is analogous to
above exampel. My work differ from the exampel number of the columns (in my
5, above - 3).

I'd like further develop above model, e.g.: to search for rows with
different of given column, in above exampel it will be:

row D: 1 0 0
row E: 0 0 0



Best regards

comparison similar rows in Excel
 

row A ???? - do u mean column A ? like this :

A..B..C..D..E
1..1..1..1..0
1..0..1..0..0
1..0..1..0..0

comparison similar rows in Excel
 

row A ???? - do u mean column A ? like this :

A..B..C..D..E
1..1..1..1..0
1..0..1..0..0
1..0..1..0..0


Yes, every letter in seperate column, every value in seperate row. In above
example (for clear worksheet - on the assumption that letter A is in field
A1) - location of value in column A: 1 in field A2, 1 - in field A3, 1 -
A4; for letter B (B1): 1 in field B2, 0 in field B3, 0 in field B4 ......
and so on

comparison similar rows in Excel
 

mareks1234 wrote:

row A ???? - do u mean column A ? like this :

A..B..C..D..E
1..1..1..1..0
1..0..1..0..0
1..0..1..0..0



Yes, every letter in seperate column, every value in seperate row. In above
example (for clear worksheet - on the assumption that letter A is in field
A1) - location of value in column A: 1 in field A2, 1 - in field A3, 1 -
A4; for letter B (B1): 1 in field B2, 0 in field B3, 0 in field B4 ......
and so on



Do the row have to remain in the same order or are you allowed to "sort"
them?

comparison similar rows in Excel
 

row A ???? - do u mean column A ? like this :

A..B..C..D..E
1..1..1..1..0
1..0..1..0..0
1..0..1..0..0



Yes, every letter in seperate column, every value in seperate row. In
above example (for clear worksheet - on the assumption that letter A is
in field A1) - location of value in column A: 1 in field A2, 1 - in
field A3, 1 - A4; for letter B (B1): 1 in field B2, 0 in field B3, 0 in
field B4 ...... and so on

Do the row have to remain in the same order or are you allowed to "sort"
them?


It is allowed to sort the row. Only column couldn't be mixed. Order is
important for the result. So:
row A: 1 1 1
row B: 1 0 0
row C: 1 1 1
row D: 1 0 0
row E: 0 0 0

could be sorted for example like this:

row A: 1 1 1
row C: 1 1 1
row B: 1 0 0
row D: 1 0 0
row E: 0 0 0

The row represent the item and the column - feature of this item
regards

comparison similar rows in Excel
 

mareks1234 wrote:

row A ???? - do u mean column A ? like this :

A..B..C..D..E
1..1..1..1..0
1..0..1..0..0
1..0..1..0..0



Yes, every letter in seperate column, every value in seperate row. In
above example (for clear worksheet - on the assumption that letter A is
in field A1) - location of value in column A: 1 in field A2, 1 - in
field A3, 1 - A4; for letter B (B1): 1 in field B2, 0 in field B3, 0 in
field B4 ...... and so on

Do the row have to remain in the same order or are you allowed to "sort"
them?



It is allowed to sort the row. Only column couldn't be mixed. Order is
important for the result. So:
row A: 1 1 1
row B: 1 0 0
row C: 1 1 1
row D: 1 0 0
row E: 0 0 0

could be sorted for example like this:

row A: 1 1 1
row C: 1 1 1
row B: 1 0 0
row D: 1 0 0
row E: 0 0 0

The row represent the item and the column - feature of this item
regards




In that case the "like" rows are adjcent. you may then see them easily.
If you want you may use a helper cell to make them stand out more, by
flagging duplicates.

comparison similar rows in Excel
 

In that case the "like" rows are adjcent. you may then see them easily.
If you want you may use a helper cell to make them stand out more, by
flagging duplicates.

What about my second question ?
"I'd like further develop above model, e.g.: to search for rows with
different of given column, in above exampel it will be:

row D: 1 0 0
row E: 0 0 0"

comparison similar rows in Excel
 

mareks1234 wrote:

In that case the "like" rows are adjcent. you may then see them easily.
If you want you may use a helper cell to make them stand out more, by
flagging duplicates.


What about my second question ?
"I'd like further develop above model, e.g.: to search for rows with
different of given column, in above exampel it will be:

row D: 1 0 0
row E: 0 0 0"



That is only same for two column entries and the third is immaterial?

comparison similar rows in Excel
 

What about my second question ?
"I'd like further develop above model, e.g.: to search for rows with
different of given column, in above exampel it will be:

row D: 1 0 0
row E: 0 0 0"



That is only same for two column entries and the third is immaterial?


I don't understand ?? I'd like find the rows with only one different
concrete attribute (for 3 attribute)

comparison similar rows in Excel
 

mareks1234 wrote:

What about my second question ?
"I'd like further develop above model, e.g.: to search for rows with
different of given column, in above exampel it will be:

row D: 1 0 0
row E: 0 0 0"



That is only same for two column entries and the third is immaterial?



I don't understand ?? I'd like find the rows with only one different
concrete attribute (for 3 attribute)



You have 3 columns/attribute. If two column must be the same then the 3
column is changing but sorting on the unchanging columns will group the
sets that are the same and the changed attribute will be randomly mixed
thru the set.