Need to add a few more lines in the code.

[RAJESH1960]

Hello JohnnyL

With the help of your code, I have successfully created this new app where the range is different. There is no 2A or Tally rows in this app. It has to match the GSTIN of all rows. It works perfectly. In this app, with your edited code, I need to continue the code with a few more lines of code and also edit the message box. After the code is run, I want the code to

• Look for errors in GSTIN verification sheet Column K, and if found I want the rows with errors to be colored Yellow in the Purchases Sheet. Message box to display “GSTIN not matching. Check & Edit.”
• If errors not found then the msg box to display “All GSTIN Numbers Matched.”
• When I run the code, the web site is in full view while running. If possible, avoid or run the web site in the background and hidden from display with one of those lines you use to avoid opening the hidden sheets like Application.DisplayAlerts…
• When the code is making a copy of the Purchases sheet, avoid copying the code button in the copied sheet (GSTIN verification sheet).
• This data doesn’t have green color but as your code runs and works for the green cells, I have colored all the rows as green. I was not able to edit that part. If it is easy to change the green part, please change it otherwise it is easy to color the rows in green and then run the code. As you wish.Test get GSTIN of Purchases Ledgers.xlsm

 

[johnnyL]

Looking at it now. Doing some more cleanup, like I always do to you prior to getting into the changes.

 

[RAJESH1960]

like I always do to you

You mean the code

 

[johnnyL]

Well, yeah, just saying because you always seem to comment how I 'changed a bunch of stuff'.

 

[RAJESH1960]

Well, yeah, just saying because you always seem to comment how I 'changed a bunch of stuff'.

 

[johnnyL]

Swing #1
See if this does what you want:

Option Explicit
'
#If VBA7 Then
' Timer settings for 64Bit Excel
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
            "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
' Timer settings for 32Bit Excel
    Private Declare Function getFrequency Lib "kernel32" Alias _
            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
            "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
'

Sub GetDataFromWeb()                                                                        ' 19 avg secs with IE.
'
    Dim StartTime               As Double
    StartTime = MicroTimer                                                                  ' Start the stopwatch
'
    Dim FormulaCount            As Long
    Dim NA_EndRow               As Long, NA_StartRow            As Long
    Dim LastRow                 As Long, LastRowDownloadedSheet As Long
    Dim cel                     As Range
    Dim CopiedData              As String
    Dim DownloadedSheetName     As String, DownloadedWorkbook   As String
    Dim MainColumn              As String
    Dim RenamedPurchasesSheet   As String
    Dim WB                      As Workbook
    Dim WS                      As Worksheet
'
    Set WS = ThisWorkbook.Sheets("Purchases")                                               ' <--- Set this to the sheet to sort
    RenamedPurchasesSheet = "GSTIN verification"                                            ' <--- Set this to the name of the renamed 'Purchases' sheet
'
    MainColumn = "B"                                                                        ' <--- Set this to the column to be used for last row
    LastRow = WS.Range(MainColumn & WS.Rows.Count).End(xlUp).Row                            ' Get last row used in Column B of 'Purchases' sheet
'
' Step 1
'
    If WS.Range(MainColumn & "2") = vbNullString Then                                       ' If B2 in Purchases sheet is blank then ...
        MsgBox "Enter GSTIN Number in column B."                                            '   Display message to user
        Exit Sub                                                                            '   Exit the code
    End If
'
'--------------------------------------------------------------------------------------
'
' Step 2
'
    Application.CopyObjectsWithCells = False                                                ' Don't copy the Macro button in the following copy
'
    WS.Copy after:=Sheets(Sheets.Count)                                                     ' Copy 'Purchases' sheet to end of workbook
    ActiveSheet.Name = RenamedPurchasesSheet                                                ' Name the copy of the 'Purchases' sheet
'
'--------------------------------------------------------------------------------------
'
' Step 3
'
    With Sheets(RenamedPurchasesSheet)
        .Sort.SortFields.Clear                                                              '   Clear the sort fields
        .Range("A2:J" & LastRow).Sort Key1:=.Range(MainColumn & "2"), _
                Order1:=xlAscending, Header:=xlNo                                           '   Sort the Purchases copied sheet by 'As Per' column
'
        .Range(Columns("K"), Columns(Columns.Count)).ClearFormats                           '   Clear formatting from Columns K to end of sheet
'
        .Range("B2:B" & LastRow).Copy                                                       '   Copy data from renamed sheet to clipboard
    End With
'
'--------------------------------------------------------------------------------------
'
' Step 5
'
'
    CopiedData = CreateObject("htmlfile").parentWindow.clipboardData.GetData("Text")        ' Save contents from clipboard into CopiedData
'
    Dim CheckCount              As Long
    Dim WebProcessedGSTINs      As Long
    Dim Browser                 As Object
    Dim TextWindow              As Object
    Dim WebSite                 As String
'
    Set Browser = CreateObject("InternetExplorer.Application")
'
    WebSite = "https://my.gstzen.in/p/gstin-validator/"                                     ' <--- Set this to the website that you want to go to
'
    With Browser
        .Visible = False                                                                    '   Set IE window status to Invisible
        .Navigate WebSite                                                                   '   Go to the website
'
        Do While .Busy Or .ReadyState <> 4                                                  '   Loop to wait for website to fully load
            DoEvents                                                                        '       Process any pending events
        Loop                                                                                '   Loop back
'
        On Error Resume Next                                                                '   If an error occurs, ignore it and proceed with next line of code
        For CheckCount = 1 To 100                                                           '   Loop to check if TextWindow has appeared
            Set TextWindow = .document.getElementsByClassName("cz-no-double-click")         '       Check to see if TextWindow has appeared
            If Not TextWindow Is Nothing Then Exit For                                      '       If TextWindow has appeared then Exit this loop
        Next                                                                                '   Loop back
        On Error GoTo 0                                                                     '   Return Error handling back to Excel
'
        .document.getElementsByTagName("textarea")(0).innerText = CopiedData                '   Copy CopiedData to text box on website
        .document.querySelector("button[type=submit]").Click                                '   Click the 'Check GSTIN/UIN Numbers' button on website
'
        Do While .Busy Or .ReadyState <> 4                                                  '   Loop to wait for website to fully load
            DoEvents                                                                        '       Process any pending events
        Loop                                                                                '   Loop back
'
        On Error Resume Next                                                                '   If an error occurs, ignore it and proceed with next line of code
        For CheckCount = 1 To 100                                                           '   Loop to check if all GSTIN #s have been processed by website
            WebProcessedGSTINs = .document.getElementsByTagName("table")(0).getElementsByTagName("TR").Length   '       Save # of processed GSTIN #s into WebProcessedGSTINs
            If WebProcessedGSTINs > (Sheets(RenamedPurchasesSheet).Range("B2:B" & _
                    LastRow).Rows.Count) Then Exit For                                      '       If website has processed all of the GSTIN #s then Exit For loop
        Next                                                                                '   Loop back
        On Error GoTo 0
'
        Set WB = Workbooks.Open(.document.querySelector("[class='pull-right btn btn-sm btn-excel']"))   ' Open the download link into workbook
'
        .Quit                                                                               '   Close the browser
    End With
'
    DownloadedWorkbook = ActiveWorkbook.Name                                                ' Save name of downloaded file to DownloadedWorkbook
    ActiveSheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)                  ' Copy the sheet from downloaded file to ThisWorkbook
'
    Workbooks(DownloadedWorkbook).Close SaveChanges:=False                                  ' Close the downloaded file
'
'--------------------------------------------------------------------------------------
'
' Step 6
'
    DownloadedSheetName = ActiveSheet.Name                                                  ' Save the downloaded sheet name to DownloadedSheetName
'
    With Sheets(RenamedPurchasesSheet)
        Sheets(DownloadedSheetName).Range("A1:L1").Copy .Range("K1:V1")                     ' Copy Header row from the downloaded sheet to RenamedPurchasesSheet
'
        LastRowDownloadedSheet = Sheets(DownloadedSheetName).Range("A" & _
                Sheets(DownloadedSheetName).Rows.Count).End(xlUp).Row                       '   Get last row used in the DownloadedSheet
'
        For FormulaCount = 1 To 12                                                          '   Loop to write formulas across range
            .Cells(2, 10 + FormulaCount).Formula = _
                    "=VLOOKUP(B2,'" & DownloadedSheetName & "'!$A$2:$L$" & _
                    LastRowDownloadedSheet & "," & FormulaCount & ",0)"                     '       Write formula to cell
        Next                                                                                '   Loop back
'
        .Range("K2:V2").AutoFill Destination:=.Range("K2:V" & LastRow)                      '   Fill the formulas down the range

        .Range("K2:V" & LastRow).Copy                                                       '   Copy formula range into memory (Clipboard)
        .Range("K2:V" & LastRow).PasteSpecial xlPasteValues                                 '   Paste just the values back to range
'
        Application.CutCopyMode = False                                                     '   Clear clipboard & 'marching ants' around copied range
'
        .Range("L2:L" & LastRow).NumberFormat = "dd-mm-yyyy"                                '   Format Dates in Column L

        .UsedRange.EntireColumn.AutoFit                                                     '   Autofit all of the columns on the destination Sheet
'
        .Range("A1:K" & LastRow).Copy WS.Range("A1")                                        '   Copy columns of data to 'Purchases' sheet
    End With
'
    With WS
        NA_StartRow = .Range("K:K").Find(what:="#N/A", after:=.Range("K" & LastRow)).Row    '       Find the start row of '#N/A' values
        NA_EndRow = .Range("K:K").Find(what:="#N/A", after:=.Range("K1"), _
                searchdirection:=xlPrevious).Row                                            '       Find the end row of '#N/A' values
'
        .Sort.SortFields.Clear                                                              '       Clear the sort fields
        .Range("A2:K" & LastRow).Sort Key1:=.Range("K" & "2"), Order1:=xlAscending, _
                Header:=xlNo                                                                '       Sort the Purchases sheet by column K
        .Range("A" & NA_StartRow & ":K" & NA_EndRow).Interior.Color = 65535
'
        .Sort.SortFields.Clear                                                              '       Clear the sort fields
        .Range("A2:K" & LastRow).Sort Key1:=.Range("A" & "2"), Order1:=xlAscending, _
                Header:=xlNo                                                                '       Sort the Purchases sheet by column A
'
        .Columns("K:K").Delete                                                              '       Delete the no longer needed column K from Purchases sheet
    End With
'
    Debug.Print "Time elapsed = " & (MicroTimer - StartTime) & " seconds."                  ' Display Elapsed Time into Immediate Window (CTRL+G)
    MsgBox "Completed."                                                                     ' Notify user that the script has finished
End Sub


Public Function MicroTimer() As Double                                                      ' Precision depends on the frequency of the CPU in the computer
'
' Code by Charles Williams originally
' Uses Windows API calls to the high resolution timer
' Returns time in seconds
'
    Dim cyTicks1        As Currency
    Static cyFrequency  As Currency
'
    MicroTimer = 0                                                                          ' Initialize MicroTimer to zero
'
    If cyFrequency = 0 Then getFrequency cyFrequency                                        ' Get ticks/second aka frequency
'
    getTickCount cyTicks1                                                                   ' Get # of ticks
'
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency                                 ' Calculate seconds ... seconds = Ticks/Frequency
End Function

I:
Eliminated the copy of the 'Code button'
Removed the code references to 'green'
Removed the browser window from the view.
added the 'Yellow' to the 'Purchases' sheet.

I have left you the homework of adding the message boxes that you mentioned. You should be able to do that by now. If you need help with those, let us know.

 

[RAJESH1960]

Sorry JohnnyL. I was very sleepy and hit the sack. I will check the code and tell you the result. Regarding the msg box I have tried it for ranges only till now. For msg box depending on the color, it is the first time. If you show me with comments as you do, I can practice and learn from that.

 

[RAJESH1960]

I have found a few issues. The code runs perfectly only if there are #N/A in the list of Purchases Sheet. The code needs to be edited at a few places.
1.In the beginning of the code, in the purchases sheet I want the code to select columns A:J and in the themes color I want to use no fill. That way if any color is there, it will clear it before running the code.
2. If the code doesn't find any #N/A in the GSTIN verification sheet it runs into an error. Instead I want it to display a msg box stating “All GSTIN Numbers Matched.”
3. This is a major one. As this is a sample data, there are only 100 rows to check. In the original sheet there may be more than 1000 rows. The web site can check only 500 rows at a time. But not to worry, the 1000 rows have repeated GSTIN numbers in the whole sheet. If you take the unique values only in the GSTIN verification sheet it may reduce the rows to 1/3rd or at least 1/4th and will display the result if the rows are less than 500 rows. In rear cases, If the unique values are more than 500 then I will have to divide the sheet into 2 parts and to Check GSTIN I will have to run the code 2 times with different list.

 

[johnnyL]

Added some more of your changes:

Option Explicit
'
#If VBA7 Then
' Timer settings for 64Bit Excel
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
            "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
' Timer settings for 32Bit Excel
    Private Declare Function getFrequency Lib "kernel32" Alias _
            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
            "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
'

Sub GetDataFromWeb()                                                                        ' 19 avg secs with IE.
'
    Dim StartTime               As Double
    StartTime = MicroTimer                                                                  ' Start the stopwatch
'
    Dim FormulaCount            As Long
    Dim NA_EndRow               As Long, NA_StartRow            As Long
    Dim LastRow                 As Long, LastRowDownloadedSheet As Long
    Dim cel                     As Range
    Dim CopiedData              As String
    Dim DownloadedSheetName     As String, DownloadedWorkbook   As String
    Dim MainColumn              As String
    Dim RenamedPurchasesSheet   As String
    Dim WB                      As Workbook
    Dim WS                      As Worksheet
'
    Set WS = ThisWorkbook.Sheets("Purchases")                                               ' <--- Set this to the sheet to sort
    RenamedPurchasesSheet = "GSTIN verification"                                            ' <--- Set this to the name of the renamed 'Purchases' sheet
'
    MainColumn = "B"                                                                        ' <--- Set this to the column to be used for last row
    LastRow = WS.Range(MainColumn & WS.Rows.Count).End(xlUp).Row                            ' Get last row used in Column B of 'Purchases' sheet
'
' Step 1
'
    If WS.Range(MainColumn & "2") = vbNullString Then                                       ' If B2 in Purchases sheet is blank then ...
        MsgBox "Enter GSTIN Number in column B."                                            '   Display message to user
        Exit Sub                                                                            '   Exit the code
    End If
'
'--------------------------------------------------------------------------------------
'
' Step 2
'
    With WS
        .UsedRange.Interior.Pattern = xlNone                                                '   Clear the green filled cell in the used range of 'Purchases' sheet
        .Range(Columns("K"), .Columns(Columns.Count)).ClearFormats                          '   Clear formatting from Columns K to end of 'Purchases' sheet
    End With
'
    Application.CopyObjectsWithCells = False                                                ' Don't copy the Macro button in the following copy
'
    WS.Copy after:=Sheets(Sheets.Count)                                                     ' Copy 'Purchases' sheet to end of workbook
    ActiveSheet.Name = RenamedPurchasesSheet                                                ' Name the copy of the 'Purchases' sheet
'
'--------------------------------------------------------------------------------------
'
' Step 3
'
    With Sheets(RenamedPurchasesSheet)
        .Sort.SortFields.Clear                                                              '   Clear the sort fields
        .Range("A2:J" & LastRow).Sort Key1:=.Range(MainColumn & "2"), _
                Order1:=xlAscending, Header:=xlNo                                           '   Sort the Purchases copied sheet by 'As Per' column
'
''        .Range(Columns("K"), Columns(Columns.Count)).ClearFormats                           '   Clear formatting from Columns K to end of sheet
'
        .Range("B2:B" & LastRow).Copy                                                       '   Copy data from renamed sheet to clipboard
    End With
'
'--------------------------------------------------------------------------------------
'
' Step 5
'
    CopiedData = CreateObject("htmlfile").parentWindow.clipboardData.GetData("Text")        ' Save contents from clipboard into CopiedData
'
    Dim CheckCount              As Long
    Dim WebProcessedGSTINs      As Long
    Dim Browser                 As Object
    Dim TextWindow              As Object
    Dim WebSite                 As String
'
    Set Browser = CreateObject("InternetExplorer.Application")
'
    WebSite = "https://my.gstzen.in/p/gstin-validator/"                                     ' <--- Set this to the website that you want to go to
'
    With Browser
        .Visible = False                                                                    '   Set IE window status to Invisible
        .Navigate WebSite                                                                   '   Go to the website
'
        Do While .Busy Or .ReadyState <> 4                                                  '   Loop to wait for website to fully load
            DoEvents                                                                        '       Process any pending events
        Loop                                                                                '   Loop back
'
        On Error Resume Next                                                                '   If an error occurs, ignore it and proceed with next line of code
        For CheckCount = 1 To 100                                                           '   Loop to check if TextWindow has appeared
            Set TextWindow = .document.getElementsByClassName("cz-no-double-click")         '       Check to see if TextWindow has appeared
            If Not TextWindow Is Nothing Then Exit For                                      '       If TextWindow has appeared then Exit this loop
        Next                                                                                '   Loop back
        On Error GoTo 0                                                                     '   Return Error handling back to Excel
'
        .document.getElementsByTagName("textarea")(0).innerText = CopiedData                '   Copy CopiedData to text box on website
        .document.querySelector("button[type=submit]").Click                                '   Click the 'Check GSTIN/UIN Numbers' button on website
'
        Do While .Busy Or .ReadyState <> 4                                                  '   Loop to wait for website to fully load
            DoEvents                                                                        '       Process any pending events
        Loop                                                                                '   Loop back
'
        On Error Resume Next                                                                '   If an error occurs, ignore it and proceed with next line of code
        For CheckCount = 1 To 100                                                           '   Loop to check if all GSTIN #s have been processed by website
            WebProcessedGSTINs = .document.getElementsByTagName("table")(0).getElementsByTagName("TR").Length   '       Save # of processed GSTIN #s into WebProcessedGSTINs
            If WebProcessedGSTINs > (Sheets(RenamedPurchasesSheet).Range("B2:B" & _
                    LastRow).Rows.Count) Then Exit For                                      '       If website has processed all of the GSTIN #s then Exit For loop
        Next                                                                                '   Loop back
        On Error GoTo 0
'
        Set WB = Workbooks.Open(.document.querySelector("[class='pull-right btn btn-sm btn-excel']"))   ' Open the download link into workbook
'
        .Quit                                                                               '   Close the browser
    End With
'
    DownloadedWorkbook = ActiveWorkbook.Name                                                ' Save name of downloaded file to DownloadedWorkbook
    ActiveSheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)                  ' Copy the sheet from downloaded file to ThisWorkbook
'
    Workbooks(DownloadedWorkbook).Close SaveChanges:=False                                  ' Close the downloaded file
'
'--------------------------------------------------------------------------------------
'
' Step 6
'
    DownloadedSheetName = ActiveSheet.Name                                                  ' Save the downloaded sheet name to DownloadedSheetName
'
    With Sheets(RenamedPurchasesSheet)
        Sheets(DownloadedSheetName).Range("A1:L1").Copy .Range("K1:V1")                     ' Copy Header row from the downloaded sheet to RenamedPurchasesSheet
'
        LastRowDownloadedSheet = Sheets(DownloadedSheetName).Range("A" & _
                Sheets(DownloadedSheetName).Rows.Count).End(xlUp).Row                       '   Get last row used in the DownloadedSheet
'
        For FormulaCount = 1 To 12                                                          '   Loop to write formulas across range
            .Cells(2, 10 + FormulaCount).Formula = _
                    "=VLOOKUP(B2,'" & DownloadedSheetName & "'!$A$2:$L$" & _
                    LastRowDownloadedSheet & "," & FormulaCount & ",0)"                     '       Write formula to cell
        Next                                                                                '   Loop back
'
        .Range("K2:V2").AutoFill Destination:=.Range("K2:V" & LastRow)                      '   Fill the formulas down the range

        .Range("K2:V" & LastRow).Copy                                                       '   Copy formula range into memory (Clipboard)
        .Range("K2:V" & LastRow).PasteSpecial xlPasteValues                                 '   Paste just the values back to range
'
        Application.CutCopyMode = False                                                     '   Clear clipboard & 'marching ants' around copied range
'
        .Range("L2:L" & LastRow).NumberFormat = "dd-mm-yyyy"                                '   Format Dates in Column L

        .UsedRange.EntireColumn.AutoFit                                                     '   Autofit all of the columns on the destination Sheet
    End With
'
    Dim rng         As Range
    Dim rngFound    As Range
'
    Set rng = Sheets(RenamedPurchasesSheet).Range("K2:K" & LastRow)
'
    Set rngFound = rng.Find("#N/A")
'
    If rngFound Is Nothing Then
        MsgBox "All GSTIN Numbers Matched."
        GoTo WrapUp
    Else
        Sheets(RenamedPurchasesSheet).Range("A1:K" & LastRow).Copy WS.Range("A1")           '   Copy columns of data to 'Purchases' sheet
'
        With WS
            NA_StartRow = .Range("K:K").Find(what:="#N/A", after:=.Range("K" & LastRow)).Row    '       Find the start row of '#N/A' values
            NA_EndRow = .Range("K:K").Find(what:="#N/A", after:=.Range("K1"), _
                    searchdirection:=xlPrevious).Row                                        '       Find the end row of '#N/A' values
'
            .Sort.SortFields.Clear                                                          '       Clear the sort fields
            .Range("A2:K" & LastRow).Sort Key1:=.Range("K" & "2"), Order1:=xlAscending, _
                    Header:=xlNo                                                            '       Sort the Purchases sheet by column K
            .Range("A" & NA_StartRow & ":K" & NA_EndRow).Interior.Color = 65535
'
            .Sort.SortFields.Clear                                                          '       Clear the sort fields
            .Range("A2:K" & LastRow).Sort Key1:=.Range("A" & "2"), Order1:=xlAscending, _
                    Header:=xlNo                                                            '       Sort the Purchases sheet by column A
'
            .Columns("K:K").Delete                                                          '       Delete the no longer needed column K from Purchases sheet
        End With
'
        MsgBox "GSTIN not matching. Check & Edit."
    End If
'
WrapUp:
    Debug.Print "Time elapsed = " & (MicroTimer - StartTime) & " seconds."                  ' Display Elapsed Time into Immediate Window (CTRL+G)
    MsgBox "Completed."                                                                     ' Notify user that the script has finished
End Sub


Public Function MicroTimer() As Double                                                      ' Precision depends on the frequency of the CPU in the computer
'
' Code by Charles Williams originally
' Uses Windows API calls to the high resolution timer
' Returns time in seconds
'
    Dim cyTicks1        As Currency
    Static cyFrequency  As Currency
'
    MicroTimer = 0                                                                          ' Initialize MicroTimer to zero
'
    If cyFrequency = 0 Then getFrequency cyFrequency                                        ' Get ticks/second aka frequency
'
    getTickCount cyTicks1                                                                   ' Get # of ticks
'
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency                                 ' Calculate seconds ... seconds = Ticks/Frequency
End Function

 

[johnnyL]

3. This is a major one. As this is a sample data, there are only 100 rows to check. In the original sheet there may be more than 1000 rows. The web site can check only 500 rows at a time. But not to worry, the 1000 rows have repeated GSTIN numbers in the whole sheet. If you take the unique values only in the GSTIN verification sheet it may reduce the rows to 1/3rd or at least 1/4th and will display the result if the rows are less than 500 rows. In rear cases, If the unique values are more than 500 then I will have to divide the sheet into 2 parts and to Check GSTIN I will have to run the code 2 times with different list.

In regards to #3, I am thinking that if you alter the 'GSTIN verification sheet' in the way that you are requesting, it will affect your desire to color yellow the mismatches on the 'Purchases' sheet. I will have to think about it a bit.

Edit:
Ok, thought about it, the only way I see your yellow coloring working is to make the unique values on the 'Purchases' sheet, not the 'GSTIN Verification' sheet.