2

Data from Spreadsheet A has multiple columns but I need to compare only:

Column A - order #
Column C - count
Column D - date

Spreadsheet B also has many columns, but I need to compare:

Column A - order #
Column B - count
Column C - date

I need to highlight column A in the row on both spreadsheets when the data across the row is identical for just those 3 columns. In this example, row 6 and 14 have different dates and 7 and 15 have different quantities, so they would not highlight.

click for example

8
  • Good for you? Come back when you have a question and have made an attempt to solve the question.
    – gtwebb
    Jun 13, 2016 at 22:56
  • Your description doesn't seem to match the screenshot (I assume the block starting in row 11 is supposed to represent spreadsheet B?): Col E vs. col C. What part of this is causing you problems? How to do conditional formatting? How to match across sheets? How to handle different sequencing?
    – fixer1234
    Jun 14, 2016 at 4:26
  • Yes row 11 is B in this example, but I miss labeled the columns.
    – G56709
    Jun 14, 2016 at 4:33
  • I need to compare A4,C4 & D4 from sheet 'A' to find where they are the same across A,B & C from sheet 'B'. Where they are the same, I would like to highlight column A from the row on both sheets. These two spreadsheets contain 90% identical data just in different columns. I'm trying to find where they differ. I tried to use a conditional format to highlight, but cannot get the formula right. If I have to, I can add one into the other by adding columns and separate after the calculation.
    – G56709
    Jun 14, 2016 at 4:39
  • You've described what you want to accomplish, but not what is the specific issue that's causing you problems. Generally, questions aren't intended as a coding service. People will help you solve a specific problem when you're stuck trying to do it yourself, but they will expect to see the formula you've tried, for example, to understand exactly what issue or concept is hanging you up.
    – fixer1234
    Jun 14, 2016 at 5:15

3 Answers 3

0

This method relies on some helper columns in your first spreadsheet, or in a separate sheet if you prefer.

MATCH() will return the row number a given value in an array.

INDEX() looks up this value in another specified array

So in row 1 of the the helper column (let's say column X), you want =MATCH(A1,SheetName!A:A,0) This will return the row in the other sheet containing the ordernumber in A1.

Then in the next 2 columns, you want =INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1). This will return the count and the date from the other sheet in that row.

Then, in the last column you want =And(Y1=C1,Z1=D1). This will return TRUE if everything is the same.

Then use conditional formatting to make your highlights if you so wish.

This can all be condensed into a single formula and used as a conditional formatting condition on if you like, also, vlookup would work just as well here as index match. I've given you it all separated out as I think it's easier to follow what's going on and why it works, but I recommend you have a go at condensing it, as practice.

I hope this has helped.

Note, this won't work if the Order number is duplicated in the lookup list, as it only looks up the first occurrence in the table.

0

You can do it, with conditional formatting, using the "Use a formula to determine which cells to format". And the short version, you need to use the following formula on your first table which located on the Sheet1 and the other on Sheet2 both starting from A1:

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

This formula assumes the following:

  • both tables are in two separate sheets, called Sheet1 and Sheet2
  • Sheet1 contains your first table, where A, C, and D columns are compared with your second table on Sheet2 A, B and C columns.
  • on sheet one as conditional formatting area, you select $A:$A or $A2:$AXX (where XX a high enough number to contain all the values) - this only matters to have the colors if the above equation is true

When it won't work as 'expected':

  • If you have multiple same order numbers in the 2nd table, only the 1st match will be fount and compared
  • By applying to the Sheet2 table it won't give back correct coloring, some referencing values needs to be changed

Used functions:

  • AND() - only returns true if all parameters true
  • MATCH() - finds a value in a selected range (column in our case)
  • INDEX() - returns a cell reference from a matrix/range, by saying explicitly the row and column numbers
  • ROW() - without any parameter returns the current row number

Now let's see some components:

  • INDEX(Sheet1!$A:$A;ROW();1) this returns the current compared cell reference when the conditional formatting running
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A) here we looking for a cell on the second sheet which matching with our current cell if there is a match, the returned value is higher than 0, otherwise warning/error given.
  • INDEX(Sheet1!$A:$D;ROW();3) - we looking here for the first table's (Sheet1) 3rd column (alias C column) value.
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2) - this fetching the 2nd column from the Sheet2 table. We already had the row, where the order ID is placed, that equation is in the middle of this function, as determinating the row.
  • the 3rd AND() parameter is the same as the previous two points, just changing the column numbers.

Note:

  • for finding the correct row, I only using columns: Sheet1!$A:$A and Sheet2!$A:$A
  • when I need to get another column, I need to extend the column to a matrix/table which the last column is at least the last column which I will refer, in case of sheet1 the 4th, alias D column Sheet1!$A:$D

By knowing the above, you need to be able to modify the equation to work in the Sheet2 table too.

-1

This one is nice and easily done with conditional formatting as follows.

Setup

For the example formula below, the data is in cells Sheet1!A1:C3 and Sheet2!A1:C3.

Conditional formatting formula

Highlight the cell range on Sheet2, add conditional formatting by formula and enter this fomrula: =AND(Sheet1!$A1=$A1,Sheet1!$B1=$B1,Sheet1!$C1=$C1) ...and choose a colour to highlight your results with.

It's important to lock the columns and not the rows (i.e. have the dollar signs before the letters but not numbers).

Regarding the references used in the conditional formatting formula, the cell references should be the upper-most cells of the corresponding columns of data. So, if both columns of order numbers start in A1, leave it as A1 per the above. But if on the first tab the first order number to consider is in row 4 but on the second tab it's in row 7, modify the formula to AND(Sheet1!$A4=A7...), etc.

Lemme know if anything there needs clarification!

You must log in to answer this question.

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