Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data (conditionally) between two spreadsheets
Hi, I'm hoping someone can help me with the following scenario.
Spreadsheet A has the following (always in Column A) NCNTPASH1AP136 CUSNPRSJ1AP11 NJANPRSJ1AP03 JANBVSJ9798RR023 Spreadsheet B has the following (not always in the same columns but always located next to each other. So, they could be column A and B one week or column B and C the next.) NCNTPASH1AP136 Ordered NJANPRSJ1AP11 In Inventory NJANPRSJ1AP03 In Inventory JANPRSJ9798RR001 Deployed Spreadsheet A's column is not titled, it's simply a list of server names. Spreadsheet B has column names (a 9 Mb dump from a Remedy database) and they are always ("CI Name" and "Status"). I'm interested in comparing server names in Spreadsheet A to those that are in Spreadsheet B but only if they are marked "Deployed." Does that make sense? I need to know which server names are common to both spreadsheets and which servers are unique to each. Can someone help me solve this both by using Excel's built-in functions as well as with a VB script? I ask this because I genuinely want to understand and learn this for myself. Thank you so much for any time and consideration you can afford. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data (conditionally) between two spreadsheets
Is there a way of determining which is the first column in sheet B? Is there
a header in the column or row a\the will determine where the data is located. the task isn't too hard once we know the start location. Can we use a inputbox to determine the 1st cell? "yord" wrote: Hi, I'm hoping someone can help me with the following scenario. Spreadsheet A has the following (always in Column A) NCNTPASH1AP136 CUSNPRSJ1AP11 NJANPRSJ1AP03 JANBVSJ9798RR023 Spreadsheet B has the following (not always in the same columns but always located next to each other. So, they could be column A and B one week or column B and C the next.) NCNTPASH1AP136 Ordered NJANPRSJ1AP11 In Inventory NJANPRSJ1AP03 In Inventory JANPRSJ9798RR001 Deployed Spreadsheet A's column is not titled, it's simply a list of server names. Spreadsheet B has column names (a 9 Mb dump from a Remedy database) and they are always ("CI Name" and "Status"). I'm interested in comparing server names in Spreadsheet A to those that are in Spreadsheet B but only if they are marked "Deployed." Does that make sense? I need to know which server names are common to both spreadsheets and which servers are unique to each. Can someone help me solve this both by using Excel's built-in functions as well as with a VB script? I ask this because I genuinely want to understand and learn this for myself. Thank you so much for any time and consideration you can afford. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data (conditionally) between two spreadsheets
On Sep 22, 2:38*pm, Joel wrote:
Is there a way of determining which is the first column in sheet B? *Is there a header in the column or row a\the will determine where the data is located. *the task isn't too hard once we know the start location. *Can we use a inputbox to determine the 1st cell? "yord" wrote: Hi, I'm hoping someone can help me with the following scenario. Spreadsheet A has the following (always in Column A) NCNTPASH1AP136 CUSNPRSJ1AP11 NJANPRSJ1AP03 JANBVSJ9798RR023 Spreadsheet B has the following (not always in the same columns but always located next to each other. So, they could be column A and B one week or column B and C the next.) NCNTPASH1AP136 * * Ordered NJANPRSJ1AP11 * * *In Inventory NJANPRSJ1AP03 * * *In Inventory JANPRSJ9798RR001 * Deployed Spreadsheet A's column is not titled, it's simply a list of server names. Spreadsheet B has column names (a 9 Mb dump from a Remedy database) and they are always ("CI Name" and "Status"). I'm interested in comparing server names in Spreadsheet A to those that are in Spreadsheet B but only if they are marked "Deployed." Does that make sense? I need to know which server names are common to both spreadsheets and which servers are unique to each. Can someone help me solve this both by using Excel's built-in functions as well as with a VB script? I ask this because I genuinely want to understand and learn this for myself. Thank you so much for any time and consideration you can afford. Spreadsheet (workbook) A does not have a column header, thus the starting cell will always be A1. Spreadsheet (a separate workbook) B always has a header but currently holds within columns B and C (B2 and C2 respectively). The column locations may change but it's not a big deal since I can always modify the spreadsheet manually to accommodate the script or vice versa. Therefore, it may not be important for the script to know about the header names contained in Spreadsheet B ("CI Name" and "Status"). I simply mentioned them in the hopes that a script could search any two columns based on the header name rather than column location. Does that make sense? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data (conditionally) between two spreadsheets
Try this code. It will allow you to select the two tables to compare. the
first is the one with the status and the 2nd is the one with the ID's to compare. create a worksheet called Summary where the results will be placed. the Deployed ID's will appear in column A. Column B will contain the ID's from the compare table. where there is a match the two IDs will appear on the same row. when a match doesn't occur there will not be an ID in both columns. Sub CompareData() Set SummarySht = Sheets("Summary") With SummarySht SummarySht.Cells.ClearContents 'add headers .Range("A1") = "Deployed CI Names" .Range("A1") = "CI Names to Compare" End With Set StatusRange = Application.InputBox( _ prompt:="Select Column where Status Table starts", _ Type:=8) Set StatusSht = StatusRange.Parent statusCol = StatusRange.Column Set CompareTable = Application.InputBox( _ prompt:="Select Column where Compare Table is located", _ Type:=8) Set CompareSht = CompareTable.Parent CompareCol = CompareTable.Column With StatusSht 'add header row so autofilter works properly .Rows(1).Insert Shift:=xlDown .Cells(1, statusCol + 1) = "status" LastRow = .Cells(Rows.Count, statusCol).End(xlUp).Row 'filter the deployed data .Columns(statusCol + 1).AutoFilter .Columns(statusCol + 1).AutoFilter _ Field:=1, _ Criteria1:="Deployed" Set CINameRange = _ .Range(.Cells(2, statusCol), .Cells(LastRow, statusCol)) CINameRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=SummarySht.Range("A2") 'remove autofilter .Cells.AutoFilter End With With SummarySht LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 End With With CompareTable 'compare each item in table with deployed items RowCount = 2 Do While .Cells(RowCount, CompareCol) < "" CIName = .Cells(RowCount, CompareCol) 'search for CIName in deployed list With SummarySht Set c = .Columns("A").Find(what:=CIName, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then .Range("B" & NewRow) = CIName NewRow = NewRow + 1 Else c.Offset(0, 1) = CIName End If End With RowCount = RowCount + 1 Loop End With End Sub "yord" wrote: On Sep 22, 2:38 pm, Joel wrote: Is there a way of determining which is the first column in sheet B? Is there a header in the column or row a\the will determine where the data is located. the task isn't too hard once we know the start location. Can we use a inputbox to determine the 1st cell? "yord" wrote: Hi, I'm hoping someone can help me with the following scenario. Spreadsheet A has the following (always in Column A) NCNTPASH1AP136 CUSNPRSJ1AP11 NJANPRSJ1AP03 JANBVSJ9798RR023 Spreadsheet B has the following (not always in the same columns but always located next to each other. So, they could be column A and B one week or column B and C the next.) NCNTPASH1AP136 Ordered NJANPRSJ1AP11 In Inventory NJANPRSJ1AP03 In Inventory JANPRSJ9798RR001 Deployed Spreadsheet A's column is not titled, it's simply a list of server names. Spreadsheet B has column names (a 9 Mb dump from a Remedy database) and they are always ("CI Name" and "Status"). I'm interested in comparing server names in Spreadsheet A to those that are in Spreadsheet B but only if they are marked "Deployed." Does that make sense? I need to know which server names are common to both spreadsheets and which servers are unique to each. Can someone help me solve this both by using Excel's built-in functions as well as with a VB script? I ask this because I genuinely want to understand and learn this for myself. Thank you so much for any time and consideration you can afford. Spreadsheet (workbook) A does not have a column header, thus the starting cell will always be A1. Spreadsheet (a separate workbook) B always has a header but currently holds within columns B and C (B2 and C2 respectively). The column locations may change but it's not a big deal since I can always modify the spreadsheet manually to accommodate the script or vice versa. Therefore, it may not be important for the script to know about the header names contained in Spreadsheet B ("CI Name" and "Status"). I simply mentioned them in the hopes that a script could search any two columns based on the header name rather than column location. Does that make sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing data in multiple spreadsheets with the first being the m | Excel Worksheet Functions | |||
Consolidating data from many spreadsheets and comparing results over time | Excel Programming | |||
Comparing two spreadsheets | Excel Worksheet Functions | |||
Comparing data from different spreadsheets???? | Excel Discussion (Misc queries) | |||
Comparing data and updating spreadsheets | Excel Discussion (Misc queries) |