Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '14 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 '14 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 '14 at 20:17
@Wutnaut, doesn't seem to be within the scope of the ID. Also, there are 13171 rows. – Joe Z Jul 1 '14 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 '14 at 20:43

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 '14 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 '14 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 '14 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 '14 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 '14 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 '14 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 '14 at 21:32
Try it already ... sheesh ... – Mike Honey Jul 2 '14 at 0:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .