Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for new entries in data
Hi everyone. Given a block of data in Sheet1 column A. Can I have
vba cross reference that data against data in Sheet2 column A and make a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on Sheet1? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for new entries in data
Here's one way. This could be done without all of the variables, but
it just makes modifications a little easier. Keep in mind, this does not remove anything from sheet3 before placing in the new data. It simply identifies the first available cells in column A and places the data there. It also ony does column A, not the entire row. That is a simple change if that is what you want though. Sub newEntry() Dim searchRange As Range, r As Range Dim searchSht As Worksheet Dim valSheet As Worksheet Dim placementSheet As Worksheet Dim valRange As Range Set searchSht = Sheets("Sheet1") Set valSheet = Sheets("Sheet2") Set placementSheet = Sheets("Sheet3") Set valRange = valSheet.Range _ (valSheet.Cells(2, 1), _ valSheet.Cells(valSheet.Rows.Count, 1) _ .End(xlUp)) Set searchRange = searchSht.Range _ (searchSht.Cells(2, 1), _ searchSht.Cells(searchSht.Rows.Count, 1) _ .End(xlUp)) For Each r In valRange If WorksheetFunction.CountIf(searchRange, _ r.Text) = 0 Then placementSheet.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Value = r.Text End If Next r Set searchSht = Nothing Set valSheet = Nothing Set placementSheet = Nothing Set valRange = Nothing Set searchRange = Nothing End Sub Steve wrote: Hi everyone. Given a block of data in Sheet1 column A. Can I have vba cross reference that data against data in Sheet2 column A and make a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on Sheet1? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for new entries in data
Hi JW. Thanks so much! Worked great!! But it brought back SO many
entries!! So, based on that, can we only populate the list with entries where the value in Column AA < zero? So I guess conceptully the code would first test the value based on cell AA, and if zero, then test to see if it exists in the other list? No sense in me bringing back entries with no value. Thank you!! One other thing I need to do is take the created list and reduce it to uinique values, which I guess can be easily done after we get the original list On Oct 10, 2:16 pm, JW wrote: Here's one way. This could be done without all of the variables, but it just makes modifications a little easier. Keep in mind, this does not remove anything from sheet3 before placing in the new data. It simply identifies the first available cells in column A and places the data there. It also ony does column A, not the entire row. That is a simple change if that is what you want though. Sub newEntry() Dim searchRange As Range, r As Range Dim searchSht As Worksheet Dim valSheet As Worksheet Dim placementSheet As Worksheet Dim valRange As Range Set searchSht = Sheets("Sheet1") Set valSheet = Sheets("Sheet2") Set placementSheet = Sheets("Sheet3") Set valRange = valSheet.Range _ (valSheet.Cells(2, 1), _ valSheet.Cells(valSheet.Rows.Count, 1) _ .End(xlUp)) Set searchRange = searchSht.Range _ (searchSht.Cells(2, 1), _ searchSht.Cells(searchSht.Rows.Count, 1) _ .End(xlUp)) For Each r In valRange If WorksheetFunction.CountIf(searchRange, _ r.Text) = 0 Then placementSheet.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Value = r.Text End If Next r Set searchSht = Nothing Set valSheet = Nothing Set placementSheet = Nothing Set valRange = Nothing Set searchRange = Nothing End Sub Steve wrote: Hi everyone. Given a block of data in Sheet1 column A. Can I have vba cross reference that data against data in Sheet2 column A and make a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on Sheet1? Thanks!- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for new entries in data
On Oct 10, 6:16 pm, Steve wrote:
Hi JW. Thanks so much! Worked great!! But it brought back SO many entries!! So, based on that, can we only populate the list with entries where the value in Column AA < zero? So I guess conceptully the code would first test the value based on cell AA, and if zero, then test to see if it exists in the other list? No sense in me bringing back entries with no value. Thank you!! One other thing I need to do is take the created list and reduce it to uinique values, which I guess can be easily done after we get the original list On Oct 10, 2:16 pm, JW wrote: Here's one way. This could be done without all of the variables, but it just makes modifications a little easier. Keep in mind, this does not remove anything from sheet3 before placing in the new data. It simply identifies the first available cells in column A and places the data there. It also ony does column A, not the entire row. That is a simple change if that is what you want though. Sub newEntry() Dim searchRange As Range, r As Range Dim searchSht As Worksheet Dim valSheet As Worksheet Dim placementSheet As Worksheet Dim valRange As Range Set searchSht = Sheets("Sheet1") Set valSheet = Sheets("Sheet2") Set placementSheet = Sheets("Sheet3") Set valRange = valSheet.Range _ (valSheet.Cells(2, 1), _ valSheet.Cells(valSheet.Rows.Count, 1) _ .End(xlUp)) Set searchRange = searchSht.Range _ (searchSht.Cells(2, 1), _ searchSht.Cells(searchSht.Rows.Count, 1) _ .End(xlUp)) For Each r In valRange If WorksheetFunction.CountIf(searchRange, _ r.Text) = 0 Then placementSheet.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Value = r.Text End If Next r Set searchSht = Nothing Set valSheet = Nothing Set placementSheet = Nothing Set valRange = Nothing Set searchRange = Nothing End Sub Steve wrote: Hi everyone. Given a block of data in Sheet1 column A. Can I have vba cross reference that data against data in Sheet2 column A and make a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on Sheet1? Thanks!- Hide quoted text - - Show quoted text - Do you want to clear the existing values in Sheet3 first? Or do you just want to append the values the existing data already in Sheet3? Getting the unique values shouldn't be too difficult using a Collection. Also, you only want to process the data is column AA is anything other than 0 or if AA is greater than 0? Just want to get all of my ducks in a row before the rewrite. Regards -Jeff- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for new entries in data
Hi Jeff. Thanks for the response. To asnwer your questions:
-clearing Sheet3 would be ideal -Only if AA zero. Thanks for your help!! On Oct 10, 7:54 pm, JW wrote: On Oct 10, 6:16 pm, Steve wrote: Hi JW. Thanks so much! Worked great!! But it brought back SO many entries!! So, based on that, can we only populate the list with entries where the value in Column AA < zero? So I guess conceptully the code would first test the value based on cell AA, and if zero, then test to see if it exists in the other list? No sense in me bringing back entries with no value. Thank you!! One other thing I need to do is take the created list and reduce it to uinique values, which I guess can be easily done after we get the original list On Oct 10, 2:16 pm, JW wrote: Here's one way. This could be done without all of the variables, but it just makes modifications a little easier. Keep in mind, this does not remove anything from sheet3 before placing in the new data. It simply identifies the first available cells in column A and places the data there. It also ony does column A, not the entire row. That is a simple change if that is what you want though. Sub newEntry() Dim searchRange As Range, r As Range Dim searchSht As Worksheet Dim valSheet As Worksheet Dim placementSheet As Worksheet Dim valRange As Range Set searchSht = Sheets("Sheet1") Set valSheet = Sheets("Sheet2") Set placementSheet = Sheets("Sheet3") Set valRange = valSheet.Range _ (valSheet.Cells(2, 1), _ valSheet.Cells(valSheet.Rows.Count, 1) _ .End(xlUp)) Set searchRange = searchSht.Range _ (searchSht.Cells(2, 1), _ searchSht.Cells(searchSht.Rows.Count, 1) _ .End(xlUp)) For Each r In valRange If WorksheetFunction.CountIf(searchRange, _ r.Text) = 0 Then placementSheet.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Value = r.Text End If Next r Set searchSht = Nothing Set valSheet = Nothing Set placementSheet = Nothing Set valRange = Nothing Set searchRange = Nothing End Sub Steve wrote: Hi everyone. Given a block of data in Sheet1 column A. Can I have vba cross reference that data against data in Sheet2 column A and make a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on Sheet1? Thanks!- Hide quoted text - - Show quoted text - Do you want to clear the existing values in Sheet3 first? Or do you just want to append the values the existing data already in Sheet3? Getting the unique values shouldn't be too difficult using a Collection. Also, you only want to process the data is column AA is anything other than 0 or if AA is greater than 0? Just want to get all of my ducks in a row before the rewrite. Regards -Jeff-- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for new entries in data
Hi Steve. Give this a shot.
Sub newEntry() Dim searchRange As Range, r As Range Dim searchSht As Worksheet Dim valSheet As Worksheet Dim placementSheet As Worksheet Dim valRange As Range Dim uVal As Collection, u As Variant Set searchSht = Sheets("Sheet1") Set valSheet = Sheets("Sheet2") Set placementSheet = Sheets("Sheet3") Set valRange = valSheet.Range _ (valSheet.Cells(2, 1), _ valSheet.Cells(valSheet.Rows.Count, 1) _ .End(xlUp)) Set searchRange = searchSht.Range _ (searchSht.Cells(2, 1), _ searchSht.Cells(searchSht.Rows.Count, 1) _ .End(xlUp)) Set uVal = New Collection For Each r In valRange If valSheet.Cells(r.Row, 27) 0 And _ WorksheetFunction.CountIf(searchRange, _ r.Text) = 0 Then On Error Resume Next uVal.Add r.Text, CStr(r.Text) On Error GoTo 0 End If Next r With placementSheet .Cells.ClearContents .Cells(1, 1).Value = "Unique New" For Each u In uVal .Cells(.Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Value = u Next u End With Set searchSht = Nothing Set valSheet = Nothing Set placementSheet = Nothing Set valRange = Nothing Set searchRange = Nothing Set uVal = Nothing End Sub Steve wrote: Hi Jeff. Thanks for the response. To asnwer your questions: -clearing Sheet3 would be ideal -Only if AA zero. Thanks for your help!! On Oct 10, 7:54 pm, JW wrote: On Oct 10, 6:16 pm, Steve wrote: Hi JW. Thanks so much! Worked great!! But it brought back SO many entries!! So, based on that, can we only populate the list with entries where the value in Column AA < zero? So I guess conceptully the code would first test the value based on cell AA, and if zero, then test to see if it exists in the other list? No sense in me bringing back entries with no value. Thank you!! One other thing I need to do is take the created list and reduce it to uinique values, which I guess can be easily done after we get the original list On Oct 10, 2:16 pm, JW wrote: Here's one way. This could be done without all of the variables, but it just makes modifications a little easier. Keep in mind, this does not remove anything from sheet3 before placing in the new data. It simply identifies the first available cells in column A and places the data there. It also ony does column A, not the entire row. That is a simple change if that is what you want though. Sub newEntry() Dim searchRange As Range, r As Range Dim searchSht As Worksheet Dim valSheet As Worksheet Dim placementSheet As Worksheet Dim valRange As Range Set searchSht = Sheets("Sheet1") Set valSheet = Sheets("Sheet2") Set placementSheet = Sheets("Sheet3") Set valRange = valSheet.Range _ (valSheet.Cells(2, 1), _ valSheet.Cells(valSheet.Rows.Count, 1) _ .End(xlUp)) Set searchRange = searchSht.Range _ (searchSht.Cells(2, 1), _ searchSht.Cells(searchSht.Rows.Count, 1) _ .End(xlUp)) For Each r In valRange If WorksheetFunction.CountIf(searchRange, _ r.Text) = 0 Then placementSheet.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Value = r.Text End If Next r Set searchSht = Nothing Set valSheet = Nothing Set placementSheet = Nothing Set valRange = Nothing Set searchRange = Nothing End Sub Steve wrote: Hi everyone. Given a block of data in Sheet1 column A. Can I have vba cross reference that data against data in Sheet2 column A and make a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on Sheet1? Thanks!- Hide quoted text - - Show quoted text - Do you want to clear the existing values in Sheet3 first? Or do you just want to append the values the existing data already in Sheet3? Getting the unique values shouldn't be too difficult using a Collection. Also, you only want to process the data is column AA is anything other than 0 or if AA is greater than 0? Just want to get all of my ducks in a row before the rewrite. Regards -Jeff-- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate data entries | Excel Worksheet Functions | |||
Data Validation...I need to add more entries!! | Excel Discussion (Misc queries) | |||
Combine Multiple Entries with differing amounts of entries | Excel Worksheet Functions | |||
changing data entries | Excel Discussion (Misc queries) | |||
Using Data Validation - how do I allow other entries | New Users to Excel |