Multiple column check on 2 worksheets

A

arwhuy

New member
Joined
Jul 24, 2017
Messages
6
Reaction score
0
Points
0
Hello all, am new to this forum and this is my first post, so apologies if i have missed any rule/requirement of the forum.

I have been trying to work on a requirement to check multiple column on two different sheets of a workbook, and report only differences (if any) on a different sheet. I haven't been successful fo far, so any help or idea how to go about it would be very helpful.

I have tried to explain the requirement below.

Two sheets named "F12" and "P12", both are differerent in formats. There is a common unique reference in both sheets, i need to compare both sheets, look that unique reference, and compare about 8 different columns, and if the values match then ignore, but if not then report them in a different sheet, one below the other. Another point to mention is, the unique references could be repeated more than once, in which case it has to sum the value in the column before comparing it with the relevant column in the other sheet. Also the unique reference may be missing in one of the sheets (just available in one sheet), in that instance that will have to be reported as a difference as well.

Sheet NameF12 P12
Unique RefA Q
ColumnCD=AJ
ColumnCE=AM
ColumnCF=AO
ColumnDF=Y
ColumnDH=Z
ColumnCS=AR
ColumnCT=AX
ColumnCU=AZ

I have attached a sample file with some test/examples oh how the data will appear and how they need to be reported in the "Result" sheet. Additional details available in the "Details" sheet.

I hope this is achievable, please let me know if this is not clear or if you need further details. Any help/guidance would be really appreciated.
 

Attachments

  • Testdata2.xlsb
    102.6 KB · Views: 16
Some questions for clarification first:
arwhuy said:
Two sheets named "F12" and "P12", both are differerent in formats.
Click to expand...
One of the sheets' names seems to have morphed to FPS, but it's fairly obvious which one. Are the corresponding column headers in the 2 sheets actually the same (it woud help)?



arwhuy said:
the unique references could be repeated more than once, in which case it has to sum the value in the column before comparing it with the relevant column in the other sheet
Click to expand...
can there be repeats on both sheets?


They will be pretty convoluted formulae to do this - is a macro acceptable?


arwhuy said:
am new to this forum and this is my first post, so apologies if i have missed any rule/requirement of the forum.
Click to expand...
Yes…
cross posted without links:https://www.mrexcel.com/forum/excel...iple-column-2-worksheets-report-mismatch.html

arwhuy/KumarG, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request. Similar requirements at MrExcel.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
Last edited:
Hellop45cal, thanks for your response. Yes the sheet names are P12 and F12 (incorrectly named as FPS in the attachment). No the column headers in the two sheets are not the same.

I should have mentioned earlier that the Repeats will just be in F12, not in P12.

Yes i did post it in other forum, but did not mention as i did not know of this requirement until pointed out by yourself in the post above. Will keep in mind going forward.
 
In the attached, try clicking the button in the vicinity of cell H30 of the Results sheet.
The results appear starting at cell K31 instead of K21 for comparison.
 

Attachments

  • ExcelGuru8101Testdata2.xlsb
    112.9 KB · Views: 14
Don't forget to tell the people at MrExcel about this crosspost!
 
Hello p45cal, thanks you very much for your help. This is really great.

When I tested this in actual data, i had the following observation, any thoughts or help would be really appreciated.

1, It picked up few records as mismatch, though the values were the same. These are specifically for records that had 2 lines in F12 sheet. I have added few examples of these records in the sample sheet, please refer cells P40 to P44 in the "Result sheet".

2, It took just about a second for the test data, but when i ran it in the actual data (which is about 5k lines in both the sheets - F12 and P12) it took about 6 to 7 minutes, is this normal and expected for this size of data comparison.

Again many thanks for you help.
 

Attachments

  • ExcelGuru8101Testdata2.xlsb
    114.2 KB · Views: 14
re: 1.:
Detective work showed that it thinks there's a miniscule difference of 2.8421709430404E-14 ; there shouldn't be - Excel's floating point arithmentic I think, so change both instances of:
If colm.Cells(1) <> colm.Cells(2). Then
to:
If Round(colm.Cells(1).Value, 10) <> Round(colm.Cells(2).Value, 10) Then

regarding 2.
Yes, I think I can speed it up but I need to experiment with bigger data. Can you attach here, or if you don't want it in the public domain, send it me via Private Message here (if it doesn't let you attach, supply link to it on a file sharing site)?
 
P45cal - Thanks again for swiftly looking into this, it now works as expected.

I tried in the actual/live data, and it gave the expected result, the only thing is the time taken to execute. It tool around 15 mins this time, not sure if this was due to my system performance or other factors like many excel sheets open.

I will try to create a larger sample file to replicate the size and volume of data in actual file, and share it shortly for you to have a look. Many thanks again for your expertise and help so far.
 
Try:
Code: 
Sub blah()
On Error GoTo GetOut
Application.ScreenUpdating = False
F12ColmLetters = Array("A", "CD", "CE", "CF", "DF", "DH", "CS", "CT", "CU")
P12ColmLetters = Array("Q", "AJ", "AM", "AO", "Y", "Z", "AR", "AX", "AZ")
F12ColmNumbers = F12ColmLetters
P12ColmNumbers = P12ColmLetters

i = 0
For Each clm In F12ColmLetters
  F12ColmNumbers(i) = Range(clm & 1).Column
  i = i + 1
Next clm
i = 0
For Each clm In P12ColmLetters
  P12ColmNumbers(i) = Range(clm & 1).Column
  i = i + 1
Next clm
With Sheets("F12")
  Set F12 = .Range("A1").CurrentRegion.Resize(, Application.Max(F12ColmNumbers))
  Set F12 = Intersect(F12, F12.Offset(1))
  NmbrFrmt = F12.Range(F12ColmLetters(1) & 1).NumberFormat  'take a note of the format of a random dta cell.
  F12Vals = F12.Value
End With
With Sheets("P12")
  Set P12 = .Range("A1").CurrentRegion.Resize(, Application.Max(P12ColmNumbers))
  Set P12 = Intersect(P12, P12.Offset(1))
  P12Vals = P12.Value
End With
ReDim Allvals(1 To UBound(F12Vals) + UBound(P12Vals), 1 To 10)
i = 1
For j = 1 To UBound(F12Vals)
  For k = 1 To 9
    Allvals(i, k) = F12Vals(j, F12ColmNumbers(k - 1))
    Allvals(i, 10) = "F12"
  Next k
  i = i + 1
Next j
For j = 1 To UBound(P12Vals)
  For k = 1 To 9
    Allvals(i, k) = P12Vals(j, P12ColmNumbers(k - 1))
    Allvals(i, 10) = "P12"
  Next k
  i = i + 1
Next j
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
With NewSht
  .Cells(2, 1).Resize(UBound(Allvals), UBound(Allvals, 2)) = Allvals
  .Range("A1").FormulaR1C1 = "Hdr1"
  .Range("A1").AutoFill Destination:=Range("A1:J1"), Type:=xlFillDefault
  Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSht.Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=NewSht.Range("L1"))

  With pt
    .ManualUpdate = True
    .ColumnGrand = False
    .RowGrand = False
    .TableStyle2 = ""
    With .PivotFields("Hdr1")
      .Orientation = xlRowField
      .Position = 1
      .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
      .LayoutBlankLine = True
    End With
    With .PivotFields("Hdr10")
      .Orientation = xlRowField
      .Position = 2
    End With
    .RowAxisLayout xlTabularRow
    For m = 2 To 9
      With .AddDataField(.PivotFields("Hdr" & m), "Sum of Hdr" & m, xlSum)
        .NumberFormat = "#,##0.00"
      End With
    Next m
    .PivotFields("Hdr10").ShowAllItems = True
    '.PivotFields("Hdr1").RepeatLabels = True
    .ManualUpdate = False
    Set PF = .PivotFields("Hdr1")
    Set PIs = PF.PivotItems
    Dim PitCollection As New Collection
    For Each pit In PIs
      PitVisible = False
      'Pit.LabelRange.Select
      'pit.DataRange.Select
      For Each colm In pit.DataRange.Columns
        'If colm.Cells(1) <> colm.Cells(2) Then
        If Round(colm.Cells(1).Value, 10) <> Round(colm.Cells(2).Value, 10) Then
          PitVisible = True
        End If
      Next colm
      If Not PitVisible Then PitCollection.Add pit
    Next pit
    .ManualUpdate = True
    For Each pit In PitCollection
      pit.Visible = False
    Next pit
    .ManualUpdate = False
    For Each pit In PF.VisibleItems
      For Each colm In pit.DataRange.Columns
        'If colm.Cells(1) <> colm.Cells(2) Then
        If Round(colm.Cells(1).Value, 10) <> Round(colm.Cells(2).Value, 10) Then
          colm.Interior.Color = vbYellow
        End If
      Next colm
    Next pit
    Set RngToCopy = Intersect(.DataBodyRange.EntireRow, .TableRange2.EntireColumn)
    RngToCopy.Copy Sheets("Result").Range("K31")
    Sheets("Result").Range("K31").Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Borders.LineStyle = xlNone
  End With  'PT
  Application.DisplayAlerts = False: .Delete: Application.DisplayAlerts = True
End With  'NewSht
GetOut:
Sheets("Result").Activate
Application.ScreenUpdating = True
End Sub
 
Hello p45cal, your latest code works like a charm, took only few seconds on the full size data to give the expected results. You are a star! Thank you so much for all your help, really appreciate it.
 
.NumberFormat = "#,##0.00"
should really be:
.NumberFormat = NmbrFrmt
in case you change the format of the numbers on your source sheets.
 
You must log in or register to reply here.
Back
Top