2 Database tables comparission in Excel

2 Database tables comparission in Excel - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: 2 Database tables comparission in Excel (/Thread-2-Database-tables-comparission-in-Excel)

2 Database tables comparission in Excel - prasadsbrk - 08-13-2009

HI,

I've the QA Data in Shee1 & Prod Data in Sheet 2 of a workbook. Prod has 9 records & QA data has 6 records (missing 2nd, 5th & 8th records). I need to compare both the sheets with corsponding records and highlight the data mismatches as well as records missing. Can any one help me on this.

RE: 2 Database tables comparission in Excel - venkatbatchu - 08-13-2009

Hi,
Try with the below code u would get expected result....
----------------------

Code:
Set objExcel = CreateObject(”Excel.Application”)

objExcel.Visible = True

Set Wk1= objExcel.Workbooks.Open("File path1")

Set Wk2= objExcel.Workbooks.Open("Filepath2")

Set Ws1= Wk1.Worksheets(1)

Set Ws2= Wk2.Worksheets(1)

   For Each cell In Ws1.UsedRange

       If cell.Value <> Ws2.Range(cell.Address).Value Then

           cell.Interior.ColorIndex = 3′Highlights in red color if any changes in cells

       Else

           cell.Interior.ColorIndex = 0

       End If

   Next

set objExcel=nothing

------------------------

RE: 2 Database tables comparission in Excel - prasadsbrk - 08-14-2009

Hi,

The same code i'm using & it is comaring Sheet1 Row1 with Sheet2 Row1, Sheet1 Row 2 with Sheet2 Row2, .. so on.
But for me Data is in random rows as below

Code:
Sheet1 Row1 = Sheet2 Row1

Sheet1 Row2 = Sheet2 Row2

Sheet1 Row3 = <<No data here>>

Sheet1 Row4 = <<No data here>>

Sheet1 Row5 = Sheet2 Row3

Sheet1 Row6 = Sheet2 Row4

<<No Data here>> = Sheet2 Row5

.............
..........
like so on
But common field is there to (Column A) to know, so using this i need to compare. as below

- If <<NO DATA>> highlight the entire row in one color (e.g. Sheet1 in Yellow & Sheet2 in Green)
- If DATA Found (with ref. to Column A) and mismatches in other columns highlight in one color (e.g Red)

Can any one help me on this.

Thanks