Results 1 to 6 of 6

Thread: Comparing data in rows in multiple columns?

  1. #1

    Question Comparing data in rows in multiple columns?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    A B C D E F G H I J K L M N O P Q R S T
    1 Elem # Title Status Medium Size/Format Element Type Version AR/Audio Config Standard Language Epis # For Epis # Episode Title Remarks Item # Item Barcode Cont Barcode Site Vault Recommendation
    2 317629 101 DALMATIANS (1996) IN VIDEO ELECTRONIC FILE (VIDEO) (BD BACKUP-AUTHORING ARCHIVE) (THEATRICAL) (16X9 - 1.33) 1080/23.98PSF VARIOUS (SEE REMARKS) NULL NULL NULL 1E+09 E0365100 E0365100
    3 310529 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND ELECTRONIC FILE (SOUND) (PRINTMASTER) (THEATRICAL) (5.1) 23.976 FPS BULGARIAN NULL NULL NULL 1E+09 E0323815 E0323815
    4 310529 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND ELECTRONIC FILE (SOUND) (PRINTMASTER) (THEATRICAL) (2.0) 23.976 FPS BULGARIAN NULL NULL NULL 1E+09 E0323812 E0323812
    5 260785 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND DVD-R (DIALOG STEMS) (INTERNATIONAL) (5.1/2.0) N/A BULGARIAN NULL NULL NULL 11721080 11721080 11721080
    6 260784 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND DVD-R (PRINTMASTER) (INTERNATIONAL) (5.1) N/A BULGARIAN NULL NULL NULL 11721079 11721079 11721079
    7 260785 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND DVD-R (PRINTMASTER) (INTERNATIONAL) (2.0) N/A BULGARIAN NULL NULL NULL 11721081 11721081 11721081
    8 252290 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND ELECTRONIC FILE (SOUND) (DIALOG STEMS) (INTERNATIONAL) (5.1/2.0) N/A BULGARIAN NULL NULL NULL 7198261 E0126209 E0126209
    9 252290 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN SOUND ELECTRONIC FILE (SOUND) (PRINTMASTER) (INTERNATIONAL) (5.1/2.0) N/A BULGARIAN NULL NULL NULL 7198260 E0126208 E0126208
    10 313756 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN VIDEO ELECTRONIC FILE (VIDEO) (BD BACKUP-AUTHORING ARCHIVE) (THEATRICAL) (16X9 - 1.33) 1080/23.98PSF MULTI-LANG (SEE REMARKS) NULL NULL NULL 1E+09 E0339140 E0339140
    11 313757 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN VIDEO ELECTRONIC FILE (VIDEO) (BD BACKUP-REPLICATION) (THEATRICAL) (16X9 - 1.33) 1080/23.98PSF MULTI-LANG (SEE REMARKS) NULL NULL NULL 1E+09 E0339146 E0339146
    12 254874 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN VIDEO DAT (DVD BACKUP-AUTHORING ARCHIVE) (THEATRICAL) (16X9 - 1.66) PAL MULTI-LANG (SEE REMARKS) NULL NULL NULL 7544700 07544700 07544700
    13 2548748 101 DALMATIANS II: PATCH'S LONDON ADVENTURE IN VIDEO DVD-R (DVD BACKUP-REPLICATION) (THEATRICAL) (16X9 - 1.66) PAL MULTI-LANG (SEE REMARKS) NULL NULL NULL 7544701 07544701 07544701 I
    Iím trying to work on a formula that would do the following :

    If title column B in a row is the same
    If 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.

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by inso View Post
    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.
    You are unclear. You should have set up an attachment and an example of how looks the expected result in a few examples.
    This is my understanding of your problem (see attach)
    You need a Conditional Formatting for color rows
    Code:
     = "Destroy"=$T2
    or
    = NOT(ISERROR(SEARCH("Destroy";$T2)))
    Here missing formula for multiple conditions (unclear request).
    Given that, the formula can not count the cells with color that created with a Conditional Formatting, I think you're going to have to solve using VBA.
    Attached Files Attached Files
    Last edited by navic; 2015-08-19 at 10:12 PM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3

  4. #4
    Thank you so very much for your help.
    And thank you for pointing out I have not included and attachment with similar entries in the sample data. Apologies as was unclear in my criteria as well. 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 blue 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.
    Attached Files Attached Files

  5. #5
    Hi,

    Try the following formula in T2:

    =IF(COUNTIFS($B$1:B2,B2,$L$1:L2,L2,$D$12,D2,$F$1:F2,F2,$G$1:G2,G2,$I$1:I2,I2,$H$1:H2,H2)>1,IF(E2="DAT","Destroy","Duplicate"),"")

    Drag this downwards.
    Attached Files Attached Files

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by inso View Post
    Iíve attached an example of how it should look like once applied.
    I will try to modify the formula created by @cbatrody
    It might help you
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •