Excel – How to combine data from two sheets and conditionally format rows based on sheet of origin

conditional formattingmicrosoft excelworksheet-function

I have 3 sheets in Excel "Open", "New", "Combine"

    "Open" Sheet (Old list)

    Ticket ID  |  Priority |  Department  |  Status 
    36009      |     1     |   Finance    |  01-Open
    34781      |     2     |   Finance    |  02-Vendor
    35776      |     1     |   Finance    |  01-Open


    "New" Sheet (New List)
    Ticket ID  |  Priority |  Department  |  Status 
    34781      |     3     |   Finance    |  01-Vendor
    35776      |     5     |   Finance    |  10-Closed
    35607      |     2     |   Finance    |  01-Open    


    "Combine" Sheet (End Result)
    Ticket ID  |  Priority |  Department  |  Status 
    36009      |     1     |   Finance    |  01-Open
    34781      |     3     |   Finance    |  01-Vendor
    35776      |     5     |   Finance    |  10-Closed
    35607      |     2     |   Finance    |  01-Open

So what I try to do is combine the Open sheet and New sheet to the combine sheet, the New sheet has priority above the open sheet. We will get 3 different results :

  1. Duplicate results that are tickets that both exists in Open and New. With the duplicate I only get the results from "New" (mark this as yellow background)

  2. Old results that are Tickets that are only in Open not in "New" Sheet (mark this as red background)

  3. New results are tickets that are only in New not in Open (mark this as green background)

So I like to copy the whole row for every check on the Ticket ID's

For now as example I look for Duplicates and New values (From New sheet to Open sheet values) with this function 

=IF(ISERROR(VLOOKUP(New!B2;Open!$B$2:$B$998;1;FALSE));IF(New!B2=0;"Empty";"NEW");"Duplicate")

How can I easily make the Combine sheet like how I want it (like automatically fill it up)?

Best Answer

As others have said, I don't think this is possible with excel functions. Even if it is, it will be slow in doing so. Running a VBA macro will be much faster. I have written a sample to help you along your way. It should more or less do what you are looking for:

Public Sub do_all_the_things()

Dim i As Integer
Dim j As Integer
Dim color As String
i = 1

 Do Until i = Range("'Open'!A1").End(xlDown).Row + 1
  j = 1
  color = "green"

  Do Until j = Range("'New'!A1").End(xlToRight).Column + 1
   If Range("'New'!A" & j).Value = Range("'Open'!A" & j).Value Then
    color = "yellow"
   Exit Do
   End If

   If color = "green" Then
    Do Until j = Range("'Combined'!A1").End(xlToRight).Column + 1
     If Range("'Combined'!A" & j).Value = Range("'Open'!A" & j).Value Then
      color = "red"
      Exit Do
     End If

     j = j + 1
   Loop

   End If

'at this point we know what color the row is
  row_on = Range("'Combined'!A1").End(xlDown).Row + 1

  Range("'Combined'!" & row_on & ":" & row_on).Value = Range("'New'!" & i & ":" & i).Value

  If color = "red" Then
   Range("'Combined'!" & row_on & ":" & row_on).Interior = 255 'red
  End If

  If color = "yellow" Then
   Range("'Combined'!" & row_on & ":" & row_on).Interior = 65535 'yellow
  End If

  If color = "red" Then
   Range("'Combined'!" & row_on & ":" & row_on).Interior.ThemeColor = xlThemeColorAccent6 'green
  End If

  i = i + 1
 Loop




End Sub
Related Question