Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed! excel formulas above my head!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed! excel formulas above my head!
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help needed! excel formulas above my head!
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--- -- RyGuy "Bernie Deitrick" wrote: 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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed for excel formulas!!! | Excel Worksheet Functions | |||
Printing Excel Documents from Head to Toe | Excel Discussion (Misc queries) | |||
Mailmerge problem from Excel to Word placies data 1st row in head | Excel Discussion (Misc queries) | |||
insert image in head excel file? | Excel Discussion (Misc queries) | |||
Databasing in Excel...I'm in way over my head | Excel Programming |