+ Reply to Thread
Results 1 to 5 of 5

Excel 2010 Conditional formatting across mulitpe colums

  1. #1
    Registered User
    Join Date
    10-21-2010
    Location
    Victoria,Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Excel 2010 Conditional formatting across mulitpe colums

    Hi Guys,

    I have a sheet that has 6 colums that get there data from list, i would like to be able to see where there are two rows with exactly the same data doesnt matter what order.

    The colums are

    Device Port Device Port Device Port Device Port

    Each Device colum gets is list from the same list and ports come from a different list.

    What i would like to be able to achieve is a list of devices with where they are attched two however i dont want to duplicate data

    so it may look look

    devicea 22 deviceb 21 devicec 23 decived 24
    deviced 24 devicec 23 deviceb 21 decivea 22

    would highlight the second one only
    currently using
    =AND(COUNTIF($A$2:$A$26,$A2)>=2,COUNTIF($A2:$A$26,$A2)<>COUNTIF($A$2:$A$26,$A2),COUNTIF($B$2:$B$26,$B2)>=2,COUNTIF($B2:$B$26,$B2)<>COUNTIF($B$2:$B$26,$B2),COUNTIF($D$2:$D$26,$D2)>=2,COUNTIF($D2:$D$26,$D2)<>COUNTIF($D$2:$D$26,$D2),COUNTIF($E$2:$E$26,$E2)>=2,COUNTIF($E2:$E$26,$E2)<>COUNTIF($E$2:$E$26,$E2))


    Anyhelp on thi swould be good

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Excel 2010 Conditional formatting across mulitpe colums

    Please post a spreadsheet with some sample data. It is not clear where your columns are. You mention 6 columns, but the data you posted is either four our eight columns, depending how they are split.

  3. #3
    Registered User
    Join Date
    10-21-2010
    Location
    Victoria,Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel 2010 Conditional formatting across mulitpe colums

    My mistake it is 8 colums.

    I am managing assisting with a datacentre and the idea is to have every device that has patching documented, however i dont want to have a separate sheet for every single device and dont want to duplicate the patching recoards.

    Colom a c e g will reference a device or patch panel b d e h will reference the port.

    or if you have any other ideas on how best to manage the patching records would be really good. As much as i have treid to get a named software for this it does not fit the budget so i am trying to get this set up in excel Visio or even Access. Any help would be good.

    Thanks
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-21-2010
    Location
    Victoria,Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel 2010 Conditional formatting across mulitpe colums

    I have attached another spreadsheet with what i am trying to complete. Over time i will filter the results or search on devices etc however if there is a better way to complete this please let me know.

    I have over 150 devices with lots of patcing and would like the best idea for running this.


    Chris
    Attached Files Attached Files

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Excel 2010 Conditional formatting across mulitpe colums

    OK,

    so you basically need to compare 8 cells in one row with 8 cells in all the other rows and flag duplicates, no matter what the order of the cells in one row.

    For a single formula, this is huge. With a few helper columns it may be doable.

    First, I think we need to create individual IDs for each Device and Port combinations. I've set something up in Sheet2, using the row number of each device in a formula involving MOD() and ^. The multiplier numbers have been chosen randomly, but I tried to find numbers that do not result in duplicates for the 250 rows that I applied the formula to.

    Back in Sheet1, there are some helper columns to help you grasp the concept. For each Device/Port combo, we look up and multiply the unique numbers from Sheet2. This should give us a unique number for each device/port combo.

    Now in Sheet1 we multiply add the (up to four) unique numbers. If any two rows have the same sum result, it means the same device/port combo was used in these rows (assuming that the products are really unique).

    Now you can use a simple Countif() to count the occurrences of the current number up to the current row. You can use this in a Conditional Format, or let the conditional format refer to the Countif column.

    The other problem with your data set is that the Vlookup sometimes returns N/A because the data for the port is a mix of number and text. You need to make sure that this is consistently entered as text. For example in Row 6, there is a port 08, which is not found in the dropdown list. If it were the text "8" it would be found.

    see attached for a demo of what I've described above.

    cheers
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1