UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Compare 2 Worksheets, Office 2010    
 
   
wornout
post Jun 29 2020, 09:38 PM
Post#1



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


I have been trying for 2 days to do this and all the code I have used is based on a unique ID.
I have a worksheet "Import" columns "B2 to M" & lastrow
I have worksheet "Tasks" columns "C3 to N" & lastrow
I want to check Tasks to see if any of Import rows are there if not put import rows in to Tasks on the next available row.
So all cells in the row have to match as there is no unique Identifier .
Go to the top of the page
 
ADezii
post Jun 30 2020, 05:54 AM
Post#2



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


Can you provide some Sample Data so as to better visualize the problem?
Go to the top of the page
 
wornout
post Jun 30 2020, 01:40 PM
Post#3



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


Yes see attached
So you have an import sheet that has been imported from outlook(Really wanted to import from google calander but could not find anything on how) I have included the module for the import macro called FindAppts the rest are me trying to check match they dont work because the are comparing the same row number.
I want to check if any of the rows in Imports matchs any of the rows in Tasks So I can copy over the ones that dont match from Imports to tasks


Attached File(s)
Attached File  Importmatch.zip ( 47.17K )Number of downloads: 4
 
Go to the top of the page
 
boobigbam@gmail....
post Jun 30 2020, 03:06 PM
Post#4



Posts: 15
Joined: 24-June 20



This is only dummy code to give an idea of a very basic and "low level" way to do the comparison:-)

Basically you run through all Rows in IMPORTS and puts the values from each cell into a single string, without any spaces or anything.
For each Row you then run through each row in TASKS and do the same thing and then compare the 2 strings and do whats needed.

This is only an idea how to compare. How to traverse the sheets row by row is beyond my knowledge:-)

CODE
For Each Row In Imports - seperat coding needed here
ImportStr = getString(CurrentRowNumber)

'Then for each row in TASKS - again seperat coding needed here
TaskStr = getString(CurrentRowNumber)
If ImportStr not like TaskStr then
'Do stuff here to copy row
End If
'Next row from TASKS

'And then repeat with the next Row in IMPORTS

'How the function might look. Just take into account which sheet is active and you will probably need 2 functions as the Columns arent the same
Function getString(RN As Integer) As String
Dim str As String
str = Range("A" & RN) & Range("B" & RN) 'and so on until last column
getString = str
End Function

This post has been edited by boobigbam@gmail.com: Jun 30 2020, 03:09 PM
Go to the top of the page
 
ADezii
post Jul 1 2020, 07:49 AM
Post#5



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


What, exactly, constitutes a Row Match, and if not a Row Match, what Columns do you want copied from Imports to Tasks?
Go to the top of the page
 
ADezii
post Jul 1 2020, 10:49 AM
Post#6



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Reference the Images for Imports (Yellow) and Tasks (Green).
  2. For each Row in the Imports Worksheet, the Code combines the Cells Values into a Comma-Delimited String. It then passes the String to a Function which does the same for the Tasks Worksheet and compares each String.
  3. If the Strings from Imports and Tasks Rows are equal, they are printed to the Immediate Window.
  4. Base Code (Imports):
    CODE
    Dim shtImports As Excel.Worksheet           'Reference to Imports Worksheet
    Dim lngLastRowImports As Long               'Last Row with Data in Imports Worksheet
    Dim lngCtr As Long                          'Rows in Imports
    Dim lngCtr2 As Long                         'Columns in Imports
    Dim strImports As String                    'String representing Row Data

    Set shtImports = ActiveWorkbook.Worksheets("Imports")

    lngLastRowImports = shtImports.Cells.SpecialCells(xlCellTypeLastCell).Row       'Last Row Imports

    For lngCtr = 2 To lngLastRowImports     'Each Row in Imports (2 to Last Row)
      For lngCtr2 = 2 To 13                 'Each Column in Imports (B to M)
        strImports = strImports & shtImports.Cells(lngCtr, lngCtr2) & ","
      Next
      fAnalyzeRow (Left$(strImports, Len(strImports) - 1))    'Pass the String to compare against Tasks Rows
      strImports = ""       'RRESET
    Next
  5. Code Definition for fAnalyzeRow():
    CODE
    Public Function fAnalyzeRow(strRow As String)
    Dim shtTasks As Excel.Worksheet             'Reference to Tasks Worksheet
    Dim lngLastRowTasks As Long                 'Last Row with Data in Tasks Worksheet
    Dim lngCtr As Long                          'Rows in Tasks
    Dim lngCtr2 As Long                         'Columns in Tasks
    Dim strTasks As String                      'String representing Row Data

    Set shtTasks = ActiveWorkbook.Worksheets("Tasks")

    lngLastRowTasks = shtTasks.Cells.SpecialCells(xlCellTypeLastCell).Row           'Last Row Tasks

    For lngCtr = 3 To lngLastRowTasks       'Each Row in Tasks (2 to Last Row)
      For lngCtr2 = 3 To 14                 'Each Column in Imports (B to M)
        strTasks = strTasks & shtTasks.Cells(lngCtr, lngCtr2) & ","
      Next
      strTasks = Left$(strTasks, Len(strTasks) - 1)
        If Trim(strTasks) = Trim(strRow) Then
          Debug.Print strRow & " ==> " & strTasks
        End If
      strTasks = ""     'RESET
    Next
    End Function
  6. OUTPUT:
    CODE
    112,113,114,115,116,117,118,119,120,121,122,123 ==> 112,113,114,115,116,117,118,119,120,121,122,123
    124,125,126,127,128,129,130,131,132,133,134,135 ==> 124,125,126,127,128,129,130,131,132,133,134,135
    23,24,25,26,27,28,29,30,31,32,33,34 ==> 23,24,25,26,27,28,29,30,31,32,33,34
  7. Hope this helps.


Attached File(s)
Attached File  Imports.JPG ( 68.36K )Number of downloads: 2
Attached File  Tasks.JPG ( 66.81K )Number of downloads: 1
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 07:11 AM