Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula to search and compare data in different col
Greetings!
I need help writing a formula to search through columns of data in two spreadsheets, compare some values in each of the spreadsheets, and identify any unpaired records. Situation: I have two spreadsheets that deal with the same entities. In the first spreadsheet, I have 4 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Approved Date (date) In the second spreadsheet, I have 5 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Actual Date (date) Original Date (date) What I need to do is the following: For each unique record in the first spreadsheet, search the second spreadsheet to find any records that match the following criteria: 1) PWSID and Facility names are identical; and 2) Either of the following is true: " approved date" in the first spreadsheet is +/- 2 days from "actual date" or "original date" in the second spreadsheet. For each record in the first spreadsheet, if it finds a record in the second spreadsheet that meets the search criteria, I would want an 'OK' to be placed in the first spreadsheet (new column). If the search does not find a record in the second, it should return 'No Record Found'. Any help folks could provide would be greatly appreciated! -- Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula to search and compare data in differentcol
Hi Michael
I think you will need a macro to do that. You say you have two spreadsheets to compare are they in the same workbook. I assume data start in column A on both sheets Post some sample data for test purpose. Regards, Per On 6 Apr., 15:57, Michael wrote: Greetings! I need help writing a formula to search through columns of data in two spreadsheets, compare some values in each of the spreadsheets, and identify any unpaired records. Situation: I have two spreadsheets that deal with the same entities. In the first spreadsheet, I have 4 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Approved Date (date) In the second spreadsheet, I have 5 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Actual Date (date) Original Date (date) What I need to do is the following: For each unique record in the first spreadsheet, search the second spreadsheet to find any records that match the following criteria: 1) PWSID and Facility names are identical; and 2) Either of the following is true: *" approved date" in the first spreadsheet is +/- 2 days from "actual date" or "original date" in the second spreadsheet. For each record in the first spreadsheet, if it finds a record in the second spreadsheet that meets the search criteria, I would want an 'OK' to be placed in the first spreadsheet (new column). *If the search does not find a record in the second, it should return 'No Record Found'. Any help folks could provide would be greatly appreciated! -- Michael |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula to search and compare data in different
Thanks for getting back to me!
Example of data in Spreadsheet 1 (including column headers): PWSID PWS Name Plant Scheduled OH2101412 DEL-CO DEL-CO OLENTANGY PLANT 5/16/2007 Example of Data in Spreadsheet 2 (including column headers): PWSID PWS Name Plant Actual Original OH2101412 DEL-CO DEL-CO OLENTANGY PLANT 5/16/2008 5/12/2008 The data are in different workbooks, but I can copy one data set into a separate tab in the other workbook if it makes it easier. The fields (columns) shown in the Spreadsheet 1 example above are together (columns 2-5 in worksheet 1), but the data in the second worksheet would not be in consecutive columns. Only the PWSID and PWS Name fields in the second worksheet are next to each other (columns 2-3), and the 'Plant', 'Actual', and 'Original' columns occur further to the right in worksheet 2, in non-contiguous columns (not next to each other). What I need to do is to determine for thousands of sets of data like example 1, whether there is corresponding data (in the second workbook) that meets the criteria I spelled out. So for the data listed above, what I would want the macro to do is look at each row of data in the first worksheet (like example 1) and then check each row in the second worksheet to see if it finds (in thousands of rows of data) a row which contains: the same PWSID and PWS Name, and that has a date (either in the 'actual' or 'original' columns) that is within +/- 2 days of the date listed in the first worksheet ('scheduled'). Once again, thank you kindly for your help! -- Michael "Per Jessen" wrote: Hi Michael I think you will need a macro to do that. You say you have two spreadsheets to compare are they in the same workbook. I assume data start in column A on both sheets Post some sample data for test purpose. Regards, Per On 6 Apr., 15:57, Michael wrote: Greetings! I need help writing a formula to search through columns of data in two spreadsheets, compare some values in each of the spreadsheets, and identify any unpaired records. Situation: I have two spreadsheets that deal with the same entities. In the first spreadsheet, I have 4 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Approved Date (date) In the second spreadsheet, I have 5 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Actual Date (date) Original Date (date) What I need to do is the following: For each unique record in the first spreadsheet, search the second spreadsheet to find any records that match the following criteria: 1) PWSID and Facility names are identical; and 2) Either of the following is true: " approved date" in the first spreadsheet is +/- 2 days from "actual date" or "original date" in the second spreadsheet. For each record in the first spreadsheet, if it finds a record in the second spreadsheet that meets the search criteria, I would want an 'OK' to be placed in the first spreadsheet (new column). If the search does not find a record in the second, it should return 'No Record Found'. Any help folks could provide would be greatly appreciated! -- Michael |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula to search and compare data in different
Hi Michael
To keep things simple, copy the data from workbook2 to sheet2 in workbook1. Past the code into a module and change PlantCol, AccDateCol and OrgDateCol, to the desired column numbers in sheet2. Sub MatchAndCheck() Dim shA As Worksheet Dim shB As Worksheet Dim TargetCol As String Dim FirstRow As Long Dim LastRow As Long Application.Screenupdating=False Set shA = Worksheets("Sheet1") Set shB = Worksheets("Sheet2") TargetCol = "B" PlantCol = 4 'Change to suit AccDateCol = 5 ' Change to suit OrgDateCol = 6 ' Change to suit FirstRow = 2 ' Headings in row 1 LastRow = shA.Range(TargetCol & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow PWSID = shA.Range(TargetCol & r).Value Set f = shB.Columns(TargetCol).Find(what:=PWSID, _ After:=shB.Range(TargetCol & 1), lookat:=xlWhole) Debug.Print f.Address If Not f Is Nothing Then If shA.Cells(r, 3).Value = shB.Cells(f.Row, PlantCol).Value Then 'PWSID & Facility Name match AppDate = shA.Cells(r, 5).Value AccDate = shB.Cells(f.Row, AccDateCol).Value OrgDate = shB.Cells(f.Row, OrgDateCol).Value If Abs(AppDate - AccDate) <= 2 Or Abs(AppDate-OrgDate) <= 2 Then shA.Cells(r, 6) = "OK" End If Else Set fFirst = f Do Set f = shB.Columns(TargetCol).FindNext(f) Debug.Print f.Address If Not f Is Nothing Then AppDate = shA.Cells(r, 5).Value AccDate = shB.Cells(f.Row, AccDateCol).Value OrgDate = shB.Cells(f.Row, OrgDateCol).Value If Abs(AppDate - AccDate) <= 2 Or Abs(AppDate- OrgDate) <= 2 Then shA.Cells(r, 6) = "OK" Exit Do End If End If shA.Cells(r, 6) = "No Record Found" Loop Until f.Address = fFirst.Address End If End If Next Application.Screenupdating=True End Sub Hopes this helps --- Per On 7 Apr., 00:01, Michael wrote: Thanks for getting back to me! Example of data in Spreadsheet 1 (including column headers): PWSID * * * * * PWS Name * Plant * * * * * * * * * * * * * * * * * Scheduled OH2101412 * *DEL-CO * * * * DEL-CO OLENTANGY PLANT *5/16/2007 Example of Data in Spreadsheet 2 (including column headers): PWSID * * * * *PWS Name * Plant * * * * * * * * * * * * * * * * *Actual * * * * *Original OH2101412 * *DEL-CO * * *DEL-CO OLENTANGY PLANT * 5/16/2008 * 5/12/2008 The data are in different workbooks, but I can copy one data set into a separate tab in the other workbook if it makes it easier. *The fields (columns) shown in the Spreadsheet 1 example above are together (columns 2-5 in worksheet 1), but the data in the second worksheet would not be in consecutive columns. *Only the PWSID and PWS Name fields in the second worksheet are next to each other (columns 2-3), and the 'Plant', 'Actual', and 'Original' columns occur further to the right in worksheet 2, in non-contiguous columns (not next to each other). What I need to do is to determine for thousands of sets of data like example 1, whether there is corresponding data (in the second workbook) that meets the criteria I spelled out. *So for the data listed above, what I would want the macro to do is look at each row of data in the first worksheet (like example 1) and then check each row in the second worksheet to see if it finds (in thousands of rows of data) a row which contains: *the same PWSID and PWS Name, and that has a date (either in the 'actual' or 'original' columns) that is within +/- 2 days of the date listed in the first worksheet ('scheduled'). Once again, thank you kindly for your help! -- Michael "Per Jessen" wrote: Hi Michael I think you will need a macro to do that. You say you have two spreadsheets to compare are they in the same workbook. I assume data start in column A on both sheets Post some sample data for test purpose. Regards, Per On 6 Apr., 15:57, Michael wrote: Greetings! I need help writing a formula to search through columns of data in two spreadsheets, compare some values in each of the spreadsheets, and identify any unpaired records. Situation: I have two spreadsheets that deal with the same entities. In the first spreadsheet, I have 4 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Approved Date (date) In the second spreadsheet, I have 5 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Actual Date (date) Original Date (date) What I need to do is the following: For each unique record in the first spreadsheet, search the second spreadsheet to find any records that match the following criteria: 1) PWSID and Facility names are identical; and 2) Either of the following is true: *" approved date" in the first spreadsheet is +/- 2 days from "actual date" or "original date" in the second spreadsheet. For each record in the first spreadsheet, if it finds a record in the second spreadsheet that meets the search criteria, I would want an 'OK' to be placed in the first spreadsheet (new column). *If the search does not find a record in the second, it should return 'No Record Found'. Any help folks could provide would be greatly appreciated! -- Michael- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with formula to search and compare data in different
Thanks, much!
I will give it a try tomorrow AM. -- Michael "Per Jessen" wrote: Hi Michael To keep things simple, copy the data from workbook2 to sheet2 in workbook1. Past the code into a module and change PlantCol, AccDateCol and OrgDateCol, to the desired column numbers in sheet2. Sub MatchAndCheck() Dim shA As Worksheet Dim shB As Worksheet Dim TargetCol As String Dim FirstRow As Long Dim LastRow As Long Application.Screenupdating=False Set shA = Worksheets("Sheet1") Set shB = Worksheets("Sheet2") TargetCol = "B" PlantCol = 4 'Change to suit AccDateCol = 5 ' Change to suit OrgDateCol = 6 ' Change to suit FirstRow = 2 ' Headings in row 1 LastRow = shA.Range(TargetCol & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow PWSID = shA.Range(TargetCol & r).Value Set f = shB.Columns(TargetCol).Find(what:=PWSID, _ After:=shB.Range(TargetCol & 1), lookat:=xlWhole) Debug.Print f.Address If Not f Is Nothing Then If shA.Cells(r, 3).Value = shB.Cells(f.Row, PlantCol).Value Then 'PWSID & Facility Name match AppDate = shA.Cells(r, 5).Value AccDate = shB.Cells(f.Row, AccDateCol).Value OrgDate = shB.Cells(f.Row, OrgDateCol).Value If Abs(AppDate - AccDate) <= 2 Or Abs(AppDate-OrgDate) <= 2 Then shA.Cells(r, 6) = "OK" End If Else Set fFirst = f Do Set f = shB.Columns(TargetCol).FindNext(f) Debug.Print f.Address If Not f Is Nothing Then AppDate = shA.Cells(r, 5).Value AccDate = shB.Cells(f.Row, AccDateCol).Value OrgDate = shB.Cells(f.Row, OrgDateCol).Value If Abs(AppDate - AccDate) <= 2 Or Abs(AppDate- OrgDate) <= 2 Then shA.Cells(r, 6) = "OK" Exit Do End If End If shA.Cells(r, 6) = "No Record Found" Loop Until f.Address = fFirst.Address End If End If Next Application.Screenupdating=True End Sub Hopes this helps --- Per On 7 Apr., 00:01, Michael wrote: Thanks for getting back to me! Example of data in Spreadsheet 1 (including column headers): PWSID PWS Name Plant Scheduled OH2101412 DEL-CO DEL-CO OLENTANGY PLANT 5/16/2007 Example of Data in Spreadsheet 2 (including column headers): PWSID PWS Name Plant Actual Original OH2101412 DEL-CO DEL-CO OLENTANGY PLANT 5/16/2008 5/12/2008 The data are in different workbooks, but I can copy one data set into a separate tab in the other workbook if it makes it easier. The fields (columns) shown in the Spreadsheet 1 example above are together (columns 2-5 in worksheet 1), but the data in the second worksheet would not be in consecutive columns. Only the PWSID and PWS Name fields in the second worksheet are next to each other (columns 2-3), and the 'Plant', 'Actual', and 'Original' columns occur further to the right in worksheet 2, in non-contiguous columns (not next to each other). What I need to do is to determine for thousands of sets of data like example 1, whether there is corresponding data (in the second workbook) that meets the criteria I spelled out. So for the data listed above, what I would want the macro to do is look at each row of data in the first worksheet (like example 1) and then check each row in the second worksheet to see if it finds (in thousands of rows of data) a row which contains: the same PWSID and PWS Name, and that has a date (either in the 'actual' or 'original' columns) that is within +/- 2 days of the date listed in the first worksheet ('scheduled'). Once again, thank you kindly for your help! -- Michael "Per Jessen" wrote: Hi Michael I think you will need a macro to do that. You say you have two spreadsheets to compare are they in the same workbook. I assume data start in column A on both sheets Post some sample data for test purpose. Regards, Per On 6 Apr., 15:57, Michael wrote: Greetings! I need help writing a formula to search through columns of data in two spreadsheets, compare some values in each of the spreadsheets, and identify any unpaired records. Situation: I have two spreadsheets that deal with the same entities. In the first spreadsheet, I have 4 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Approved Date (date) In the second spreadsheet, I have 5 columns that make up a unique record: PWSID (alpha num) PWS Name (alpha num) Facility Name (alpha num) Actual Date (date) Original Date (date) What I need to do is the following: For each unique record in the first spreadsheet, search the second spreadsheet to find any records that match the following criteria: 1) PWSID and Facility names are identical; and 2) Either of the following is true: " approved date" in the first spreadsheet is +/- 2 days from "actual date" or "original date" in the second spreadsheet. For each record in the first spreadsheet, if it finds a record in the second spreadsheet that meets the search criteria, I would want an 'OK' to be placed in the first spreadsheet (new column). If the search does not find a record in the second, it should return 'No Record Found'. Any help folks could provide would be greatly appreciated! -- Michael- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search / Compare / Copy Value Up (cpm) | Excel Discussion (Misc queries) | |||
Search Formula to collate BOM Data | Excel Worksheet Functions | |||
Excel 2002: Can Vlookup formula search for data with two reference | Excel Discussion (Misc queries) | |||
Excel Formula or tool to compare two columns of data | Excel Worksheet Functions | |||
What formula should I use to compare duplicate data between worksh | Excel Worksheet Functions |