Adding 3 columns of criteria simultaneously

[Michael Joseph Cosgriff]

I have used a RANDOM NUMBERS web page to make a collection of 6 columns from
A1 to F50. Now I want to scan them to see if I have repeated any rows. Can
someone help with a formula

Mike Cosgriff

 

[Sheeloo]

One way is to concatenate the values in Column G like this
=A1&B1&C1&D1&E1&F1

then use this in Col H
=Countif(G1:$G100,G1)
(change 100 to the last row number) and copy down.

Cells with value > 1 will show duplicate cells.

 

[OssieMac]

Hi Mike,

You could use a helper column and concatenate the data then use conditional
format to highlight identical lines.

Following assumes data in A1:F50 as per your post.
In cell G1 insert the following formula.
=A1&B1&C1&D1&E1&F1
Copy the formula to the bottom of the data.
Select the full range in column G
Select conditional format and select formula for the rule type.
Insert the following formula for the rule.
=COUNTIF($G$1:$G$50,G1)>1
(Note that $G$1:$G$50 must be absolute with $ signs and G1 is relative.)
Set the format required like yellow fill.

Then matching lines will be shown up with yellow fill. Note that if you have
multiple matching rows then all will be yellow and you will have to sort out
the matching ones but in 50 samples 6 wide the probability of that is low.

 

[OssieMac]

Hi Sheeloo,

At least the rows in the range need to be absolute to enable the formula to
be copied down.

=COUNTIF(G$1:G$100,G1)