Comparing data in rows in multiple columns?

**inso** · 08-19-2015, 10:58 AM

Hi all,
I am working on a project where I’m trying to identify similar assets that can be destroyed or digitised. I am basically trying to compare the date in rows within the same column and then do the same for multiple columns. If all match then highlight the rows.

Please see below a sample example of my data(please see attached)

I’m trying to work on a formula that would do the following :

It title column B in a row is the same
Is Ep# I column L n a row is same
If medium column D in a row is same
If element type column f in a row is same
If version column G in a row is same
If standard column I in a row is same
If AR/ audio Config column H is same
Then colour rows yellow

Or if all cells in rows in the following columns B, L, D, F, G, I, H match than colour yellow.

And then
If colour of row is yellow and Size format column E contains text “DAT”, place text “Destroy” in Recommendation Column T.

Any help or ideas would be very much appreciated. Thank you in advance.

**Bernie Deitrick** · 08-19-2015, 01:04 PM

You need helper cells.

In V2, enter the formula

= B2& L2& D2& F2& G2& I2& H2

Then in T2, enter

=IF(COUNTIF($V$1:V2,V2)>1,IF(NOT(ISERROR(FIND("DAT",E2))),"Destroy",""),"")

And copy both down to match your data. Note that your example file did not have any rows that met your criteria for "Destroy"....

Then Select cells A2:T??? and use Conditional formatting with the formula option, using the formula

=$V2 = "Destroy"

and choose the yellow fill.

**FlameRetired** · 08-19-2015, 11:50 PM

@ Bernie Deitrick

Good one. Shouldn't the >1 be >=1 ? I'm assuming it's a 'typo'.

**inso** · 08-20-2015, 05:26 AM

Thank you so very much for your help.
And thank you for pointing out I have not included two similar entries. Apologies as was unclear in my criteria. What I need to do is compare the data within the rolls in columns B, L, D, F, G, I, H and mark them as duplicate instead of Destroy. So the two rows highlighted in blues should be the ones that come up as duplicate. And only then as a second rule if Colum E contains certain text such as DAT it is Marked as Destroy. Hope this makes sense. I’ve attached an example of how it should look like once applied.
Thank you again for all your help., it is very much appreciated.

**inso** · 08-20-2015, 05:41 AM

Attachment below.

**Bernie Deitrick** · 08-20-2015, 07:36 AM

No, COUNTIF will always return 1 if you include the criteria cell in the range, and duplicates are when the result from COUNTIF is >1 (2, 3, 4, etc.)

**Bernie Deitrick** · 08-20-2015, 07:38 AM

Maybe use this in T2, instead:

=IF(COUNTIF(V:V,V2)>1,IF(NOT(ISERROR(FIND("DAT",E2))),"Destroy","Duplicate"),"")

and use this for the CF

=OR($V2 = "Destroy", $V2 = "Duplicate")

**inso** · 08-20-2015, 11:24 AM

Many thanks Bernie Deitrick! You are an absolute saviour!
Just one final question if I want to say add DVD-R to the Destroy (using multiple criteria)can I input it in the same formula something like: =IF(COUNTIF(V:V,V2)>1,IF(AND(NOT(ISERROR(FIND("DAT",E2))),"Destroy","Duplicate"),(NOT(ISERROR(FIND("DVD-R",E2))),"Destroy","Duplicate"),"")
My syntax or my logic is defiantly wrong here so your input is truly appreciated as always.

**Bernie Deitrick** · 08-20-2015, 11:34 AM

Here you go:

=IF(COUNTIF(V:V,V2)>1,IF(NOT(ISERROR(FIND("DAT",E2))),"Destroy",IF(NOT(ISERROR(FIND("DVD-R",E2))),"Destroy","Duplicate")),"")

Just be careful when you actually destroy things - do them one at a time, because this will flag both of the duplicates as "Destroy", but getting rid of one will unflag the other.

**inso** · 08-20-2015, 12:05 PM

Brilliant you are absolutely amazing! Thank you again.

**inso** · 08-20-2015, 12:18 PM

Apologies but one final addition as I spotted I've omitted this, column A Elem # has to be different values for the entire formula to work. For example if element 252290 is in two rows than the rows cannot be duplicates, if however its two different ones like 254875 and 254874 and all other columns are the same then these can be marked as duplicates(please see attached). A bit of reverse logic so hope I've explained it ok . Apologies if I am confusing.

**Bernie Deitrick** · 08-20-2015, 12:22 PM

You could also use this, which may be easier to expand in the future by copying the bolded section and pasting it just after the bolded part and changing the criteria string

=IF(COUNTIF(V:V,V2)>1,IF(OR(NOT(ISERROR(FIND("DAT",E2))),NOT(ISERROR(FIND("DVD-R",E2)))),"Destroy","Duplicate"),"")

=IF(COUNTIF(V:V,V2)>1,IF(OR(NOT(ISERROR(FIND("DAT",E2))),NOT(ISERROR(FIND("DVD-R",E2))),NOT(ISERROR(FIND("Other",E2)))),"Destroy","Duplicate"),"")

**Bernie Deitrick** · 08-20-2015, 12:36 PM

Should row 6 be flagged, since it is a duplicate of both rows 8 and 9? And should 8 and 9 be flagged because of their match with row 6 (but since A8 and A9 match, maybe they should not)? Note that if either 8 or 9 were removed, the other would be flagged as a duplicate because of row 6, as would 6....

**FlameRetired** · 08-20-2015, 02:10 PM

Originally Posted by Bernie Deitrick

No, COUNTIF will always return 1 if you include the criteria cell in the range, and duplicates are when the result from COUNTIF is >1 (2, 3, 4, etc.)

I am assuming that is directed at my post. Seems I misinterpreted what the final target was. DAT doesn't seem to be a target.

**inso** · 08-25-2015, 11:31 AM

Cheers so much, you are absolutely correct row 6 should be flagged up as well. Many thanks you have truly helped me a lot!

Comparing data in rows in multiple columns?

LinkBack

Thread Tools

Rate This Thread

Display

Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Re: Comparing data in rows in multiple columns?

Thread Information

Users Browsing this Thread

Similar Threads

Comparing Data on Multiple Sheets and Columns

Comparing two columns-multiple rows

Align rows comparing multiple columns

Comparing two columns data and inserting rows

Comparing data across multiple columns

Align Rows Comparing Multiple Columns

Comparing data in multiple columns

Bookmarks

Bookmarks

Posting Permissions