help needed! excel formulas above my head!

[daveallston]

Hello,
Hoping someone can help me out, would be much appreciated. Here is
what I am trying to do:

I have several spreadsheets, each with 46,000 rows of data. I need to
analyze it in a couple of different ways. I am very strong with Excel,
but some of these formulas I need are blowing me away a bit. Hopefully
an expert on here can help me out! Would appreciate it very much!

1. Is there a way (possibly in MS Access, if not Excel?) to compare
two individual tables or sheets, and have a resulting table showing a
list of every row of data where a change occured in one or more cells?
My example is I have a table of data from Jan 1, and one from Apr 1,
and I need to find where changes have been made between the two dates,
in any one of the 50+ columns. I have tried using the "find unmatched
query wizard" in Access, but that will only find me the lines of data
where there is no match between sheet A and B for the "matching field"
I select. I want to have a query or search run that returns every line
where information was added or modified, between sheet A and B.

And then further to this, and this might be making it too complicated,
I don't know... is there a way to get a count of the number of changes
by "groupings" (i.e. if there are 400 rows for city A, 500 rows for
city B, 300 rows for city C, and 500 rows for city D, and there were a
total of 75 rows where data was changed between the Jan 1 and Apr 1
sheets, can I run a count to see how many of the 75 rows were city A
vs city B vs city C vs City D?

2. Counting blank cells: I need to be able to make a chart in Tab A
showing City A, B, C, and D, counting the total number of blank cells
in Col E, Col F, and Col G (all three columns counted separately) in
Tab B, for each of City A, B, C, and D (Tab B is a huge database list
of unsorted data, with rows of data for each City). What is the
formula for this?

Thank you very much to anyone who can help me with this. Much, much
appreciated. I will monitor this group all day for any replies, so if
you need more information, or have any quesitons, please let me know.

Cheers,
Dave

 

[Bernie Deitrick]

I have an add-in that will compare two databases based on a key value, and create a third sheet
showing the changes. Email me privately - take out the spaces and change the dot to . - and I will
send it to you, if you want it.

Once you create the third sheet, you can easily use that as a source for a pivot table to count the
changes.

For your question number 2, use a formula like this (the data doesn't need to be sorted):

=SUMPRODUCT(($B$1:$B$40000="City A")*(E$1:E$40000=""))

Or, with the city names starting in H2 down to H5
=SUMPRODUCT(($B$1:$B$40000=$H2)*(E$1:E$40000=""))

Then drage down and to the right to get the counts for E, F, and G

HTH,
Bernie
MS Excel MVP

 

[ryguy7272]

Can you run this code and see if it works for you?

Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
' CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
End Sub



Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %")
& "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub


As always, make a backup of your file in case you get unintended results...

Regards,
Ryan---