Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Column A has the product ID. There are 102 other columns with other data. Column A will be unique unless some data was changed. In that case, there will be 2 rows with 99% of the columns are identical, except for the change.

Is there any way to indicate which columns are different within the same product ID?

In the example below, for ID: 2, column C is the differentiation. For ID: 5, column E is.

spreadsheet screenshot

share|improve this question
    
Do you mean a way to indicate which ROWS are different? The question isn't terribly phrased, but it's difficult to make sense out of your situation. –  Wutnaut Jul 1 at 19:18
    
I know which rows are duplicates since the ID is the same. We need which COLUMNS are different. Will add an image above to clarify. –  Joe Z Jul 1 at 19:26
    
I know how to do this for 2 columns, not sure how to make it work for 102 columns... Anyways I do it by highlighting the 2 columns then in the top ribbon choose Conditional formatting -> Highlight cell rules -> More rules... -> Format only unique or duplicate values (choose unique from drop-down). I hope this information is enough to put you on the correct path because I'm quickly reaching my excel depth. –  Wutnaut Jul 1 at 20:17
    
@Wutnaut, doesn't seem to be within the scope of the ID. Also, there are 13171 rows. –  Joe Z Jul 1 at 20:37
    
Well, if it's possible, it's through conditional formatting. 102 columns with 13k rows and you want to show which are unique... good luck. –  Wutnaut Jul 1 at 20:43

2 Answers 2

So this depends on how you want to get your output, but what I would do in your situation, if there's not a reason that you can't, is to write a simple macro in VBA that scans across all columns and either returns the location of the different column, creates a link to that cell, or highlights the differing values.

The macro could flow like this:

  1. Get value in the current cell
  2. Check cells above and below until a different value appears. This gives you your range of rows
  3. For each column after the first, compare those rows
  4. If there's a difference, highlight those cells (for example. You could also return the column letter or number, e.g. D = 4)
share|improve this answer
    
Ideally, I'd get the output as another column (103rd), which will list the column headers that contain differences. So, column F would list [null, C, C, null, null, E, E] –  Joe Z Jul 1 at 21:22
    
Okay. So it would be reasonably simple to write a macro for this. I'll give you this as food for thought, but if you decide to take this route and have questions, let me know. –  Aliden Jul 1 at 21:41
    
What we did was, import this into SQL, and run GROUP BY on ID, then CONCAT'd the unique values within each group, using a specific character. Any place that has that char, means a merge of differing data. Definitely not the Exel way, but for me, the quickest route to the solution. Would be interesting to see this done in VBA, though. –  Joe Z Jul 2 at 13:40

Click in cell B2.  Create a conditional format with the formula

=OR(AND($A1=$A2,B1<>B2), AND($A2=$A3,B2<>B3))

like this:

                                formatting rule

and apply it to your entire data area:

    Rules Manager (shows “Applies To”)

Here’s the result:

                                the worksheet

share|improve this answer
    
Is this practical for the size of the spreadsheet (102 cols, 13k rows)? –  Joe Z Jul 1 at 21:20
    
I guess it would be. I’d be shocked if 102 columns was a problem; thousands of rows – maybe. But look at what the formula is doing – it’s just looking at the cell above the current cell, the cell below, and the corresponding three cells (same row ±1) in column A. Six cells, four comparisons, and three logical (Boolean) operations – there’s no reason for it to choke. –  Scott Jul 1 at 21:28
    
in one instance (a particular ID), there are 175 duplicate rows, so each check would need to check all other 174 rows –  Joe Z Jul 1 at 21:30
    
I still don’t see a problem. At each cell, it’s six cells, four comparisons, and three logical operations. –  Scott Jul 1 at 21:32
1  
Try it already ... sheesh ... –  Mike Honey Jul 2 at 0:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.