Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing distinct data
Hi,
I was wondering if there was somebody who could help me with what I hope is a quick problem. Basically, I have two worksheets. In one worksheet, I have a column full of unique id numbers. And related to that specific id number, I have rows of information with info like street address and name. In another worksheet, I also have a column full of unique id numbers. Some of these id numbers match up with the id numbers that are in the 1st worksheet. But there's many extraneous records that are irrelevant. In this worksheet, I have rows of information with info on City and State. So basically, I was wondering, is there any way I can match up the records with id numbers that match up on worksheet 1 & 2 and discard the records on worksheet 2 that don't have a corresponding record in worksheet 1? aka, I'd like to match up the street address, name, city and state for onto one worksheet. There's just too many records for me to do this by hand. I'm sorry if this was confusing, I'd be more than happy to explain further. Any and all help would be GREATLY appreciated. Thanks! Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing distinct data
Hi
you can use VLOOKUP to bring the City and State information from Sheet 2 to Sheet 1 assume on sheet2 your IDs are in column A, City & State in columns B & C - starting at row 2 and going to row 1000 assume on sheet1 your IDs are in column A and you want City in D and State in E on sheet1 the formula in D2 would be =VLOOKUP(A2,Sheet2!$A$2:$C$1000,2,0) on sheet1 the formula in E2 would be =VLOOKUP(A2,Sheet2!$A$2:$C$1000,3,0) you could then select these two cells and fill down (select both cells and then move your mouse over the bottom right hand corner of E2 and when you see a + double click) the rest of the ID numbers on Sheet 1 to bring the data across. You could then, if you want, select columns D & E of sheet 1 and use copy / edit, paste special - values to change the formula into the result and then sheet2 could be deleted. hope this helps Cheers JulieD "junkit132000" wrote in message ... Hi, I was wondering if there was somebody who could help me with what I hope is a quick problem. Basically, I have two worksheets. In one worksheet, I have a column full of unique id numbers. And related to that specific id number, I have rows of information with info like street address and name. In another worksheet, I also have a column full of unique id numbers. Some of these id numbers match up with the id numbers that are in the 1st worksheet. But there's many extraneous records that are irrelevant. In this worksheet, I have rows of information with info on City and State. So basically, I was wondering, is there any way I can match up the records with id numbers that match up on worksheet 1 & 2 and discard the records on worksheet 2 that don't have a corresponding record in worksheet 1? aka, I'd like to match up the street address, name, city and state for onto one worksheet. There's just too many records for me to do this by hand. I'm sorry if this was confusing, I'd be more than happy to explain further. Any and all help would be GREATLY appreciated. Thanks! Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing distinct data
1. Open the workbook; if you have made any changes, save it.
2. My assumptions: your first sheet is called Sheet1, the second is called Sheet2 (the one from which data is discarded). 3. Both sheets contain data from Row 1 down AND row 1 contains field names, one of the common names is ID. Now try this code: Sub xx() cnn = "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=" & ThisWorkbook.FullName Sql = "SELECT * FROM [SHEET2$] WHERE ID IN(SELECT ID FROM [SHEET1$])" Set adors = CreateObject("ADODB.RecordSet") adors.Open Sql, cnn If Not adors.EOF Then ActiveWorkbook.Worksheets.Add ActiveSheet.Range("A2").CopyFromRecordset adors End If For i = 0 To adors.Fields.Count - 1 ActiveSheet.Cells(1, i + 1).Value = adors.Fields(i).Name Next adors.Close Set adors=NOTHING End Sub Copy the code to the ThisWorkbook module & run it. IT will add a new sheet into which it will add the data that you want to keep. If this does what you want or it can be adapted to do so, you can delete Sheet2 or make the CopyFromRecordSet method apply to Sheet2. "junkit132000" wrote: Hi, I was wondering if there was somebody who could help me with what I hope is a quick problem. Basically, I have two worksheets. In one worksheet, I have a column full of unique id numbers. And related to that specific id number, I have rows of information with info like street address and name. In another worksheet, I also have a column full of unique id numbers. Some of these id numbers match up with the id numbers that are in the 1st worksheet. But there's many extraneous records that are irrelevant. In this worksheet, I have rows of information with info on City and State. So basically, I was wondering, is there any way I can match up the records with id numbers that match up on worksheet 1 & 2 and discard the records on worksheet 2 that don't have a corresponding record in worksheet 1? aka, I'd like to match up the street address, name, city and state for onto one worksheet. There's just too many records for me to do this by hand. I'm sorry if this was confusing, I'd be more than happy to explain further. Any and all help would be GREATLY appreciated. Thanks! Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing distinct data
AA2e72E, A few points:
1) Your code uses ADO to query an open workbook. This is a bad idea due to an ADO bug: BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) http://support.microsoft.com/default...;en-us;Q319998 2) The Microsoft OLE DB provider for Jet 4.0 is the provider of choice for ADO enthusiast in this group, so your connection string could be re-written as: Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Tempo\db.xls; Extended Properties=Excel 8.0 Perhaps you were using the odbc driver and respective provider in an attempt to circumvent the memory leak bug? This does not work because the bug is in ADO and is not limited to particular drivers and providers. I've tested with the OLE DB providers for Jet 4.0, Jet 3.51 and odbc and all caused the memory leak to occur. The only realistic work around is to use a closed copy of the workbook and, contrary to Method 1 in the above MS article, the copy should not be made using SELECT..INTO because this in itself involves querying the open workbook. 3) The usual approach is to JOIN the sheets/tables. Here's is a suggested alternative using a JOIN: SELECT T2.* FROM [Sheet2$] AS T2 INNER JOIN [Sheet1$] AS T1 ON T2.ID = T1.ID; FWIW your subquery construct should be equivalent in terms of performance; in fact, my experience of Jet suggests yours may even run a little faster than mine. I post mine merely because it is the more common construct. 4) The data/header row does not need to start in row 1 when using [Sheet1$] as the table name. Jet will determine the table confines using the UsedRange (but at a much lower level than VBA does). Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
distinct count summarize Data for pivoit table | Excel Discussion (Misc queries) | |||
comparing two columns of data and return unique data in another co | Excel Discussion (Misc queries) | |||
Extracting distinct data | Excel Discussion (Misc queries) | |||
comparing lists of data to remove duplicate data | Excel Discussion (Misc queries) | |||
Distinct Data In Combo Box | Excel Programming |