Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in data from another database
As a Nursing data analyst, I must combine information from multiple sources.
Each source contains different elements I need to import. Right now I have several large groups of data that go back to 1999. So each database has about 2000 entries. I have imported all the data to excel in worksheets. The good news is that each group of data is indexed with a patient medical record number in the first row. The bad news is that about 5% of the entries don't have a corresponding medical record number both worksheets. All I want to do is to add in the data items that have a medical record number on both sheets. Entries indexed with the medical record that don't exist in both worksheets can either be skipped or even deleted. Is there a way to do this either native to excel or some code that someone has already written? I am fairly new to Excel and Visual Basic. Can someone point me in the right direction here? Thanks -- WillRRn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in data from another database
Assume the medical record number is in column A of each of Sheet1 and sheet2
Assume the first row of each sheet has column headers/labels. in sheet1, insert a new column B (select Column B, do Edit=Insert) Give it a header of something like MATCH in B2 put in a formula =if(Countif(Sheet2!$A:$A,$A2)0,"Keep","Delete") then drag fill down the column select cell A1 Do Data=filter=Autofilter in the column B dropdown, select Delete Now only rows to be deleted are visible. In the gray labels to the left, select the first visible row below row1 hold down the Shift key, scroll down to the last visible row and again click in the gray label. This will select all rows from the first visible to the last visible. Do Edit=Delete this will only delete the visible rows. Now do Data=Filter=Autofilter to remove the autofilter. Select Column B and do Edit=Delete Repeate this for the second sheet where the formula would be =if(Countif(Sheet1!$A:$A,$A2)0,"Keep","Delete") -- Regards, Tom Ogilvy "WillRRn" wrote in message ... As a Nursing data analyst, I must combine information from multiple sources. Each source contains different elements I need to import. Right now I have several large groups of data that go back to 1999. So each database has about 2000 entries. I have imported all the data to excel in worksheets. The good news is that each group of data is indexed with a patient medical record number in the first row. The bad news is that about 5% of the entries don't have a corresponding medical record number both worksheets. All I want to do is to add in the data items that have a medical record number on both sheets. Entries indexed with the medical record that don't exist in both worksheets can either be skipped or even deleted. Is there a way to do this either native to excel or some code that someone has already written? I am fairly new to Excel and Visual Basic. Can someone point me in the right direction here? Thanks -- WillRRn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in data from another database
Very Cool!
You just saved me about 10 hours of work! "Tom Ogilvy" wrote: Assume the medical record number is in column A of each of Sheet1 and sheet2 Assume the first row of each sheet has column headers/labels. in sheet1, insert a new column B (select Column B, do Edit=Insert) Give it a header of something like MATCH in B2 put in a formula =if(Countif(Sheet2!$A:$A,$A2)0,"Keep","Delete") then drag fill down the column select cell A1 Do Data=filter=Autofilter in the column B dropdown, select Delete Now only rows to be deleted are visible. In the gray labels to the left, select the first visible row below row1 hold down the Shift key, scroll down to the last visible row and again click in the gray label. This will select all rows from the first visible to the last visible. Do Edit=Delete this will only delete the visible rows. Now do Data=Filter=Autofilter to remove the autofilter. Select Column B and do Edit=Delete Repeate this for the second sheet where the formula would be =if(Countif(Sheet1!$A:$A,$A2)0,"Keep","Delete") -- Regards, Tom Ogilvy "WillRRn" wrote in message ... As a Nursing data analyst, I must combine information from multiple sources. Each source contains different elements I need to import. Right now I have several large groups of data that go back to 1999. So each database has about 2000 entries. I have imported all the data to excel in worksheets. The good news is that each group of data is indexed with a patient medical record number in the first row. The bad news is that about 5% of the entries don't have a corresponding medical record number both worksheets. All I want to do is to add in the data items that have a medical record number on both sheets. Entries indexed with the medical record that don't exist in both worksheets can either be skipped or even deleted. Is there a way to do this either native to excel or some code that someone has already written? I am fairly new to Excel and Visual Basic. Can someone point me in the right direction here? Thanks -- WillRRn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in data from another database
I said:
(select Column B, do Edit=Insert) I should have said (select Column B, do Insert=Column) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Assume the medical record number is in column A of each of Sheet1 and sheet2 Assume the first row of each sheet has column headers/labels. in sheet1, insert a new column B (select Column B, do Edit=Insert) Give it a header of something like MATCH in B2 put in a formula =if(Countif(Sheet2!$A:$A,$A2)0,"Keep","Delete") then drag fill down the column select cell A1 Do Data=filter=Autofilter in the column B dropdown, select Delete Now only rows to be deleted are visible. In the gray labels to the left, select the first visible row below row1 hold down the Shift key, scroll down to the last visible row and again click in the gray label. This will select all rows from the first visible to the last visible. Do Edit=Delete this will only delete the visible rows. Now do Data=Filter=Autofilter to remove the autofilter. Select Column B and do Edit=Delete Repeate this for the second sheet where the formula would be =if(Countif(Sheet1!$A:$A,$A2)0,"Keep","Delete") -- Regards, Tom Ogilvy "WillRRn" wrote in message ... As a Nursing data analyst, I must combine information from multiple sources. Each source contains different elements I need to import. Right now I have several large groups of data that go back to 1999. So each database has about 2000 entries. I have imported all the data to excel in worksheets. The good news is that each group of data is indexed with a patient medical record number in the first row. The bad news is that about 5% of the entries don't have a corresponding medical record number both worksheets. All I want to do is to add in the data items that have a medical record number on both sheets. Entries indexed with the medical record that don't exist in both worksheets can either be skipped or even deleted. Is there a way to do this either native to excel or some code that someone has already written? I am fairly new to Excel and Visual Basic. Can someone point me in the right direction here? Thanks -- WillRRn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in data from another database
Glad it worked out for you.
-- Regards, Tom Ogilvy "WillRRn" wrote in message ... Very Cool! You just saved me about 10 hours of work! "Tom Ogilvy" wrote: Assume the medical record number is in column A of each of Sheet1 and sheet2 Assume the first row of each sheet has column headers/labels. in sheet1, insert a new column B (select Column B, do Edit=Insert) Give it a header of something like MATCH in B2 put in a formula =if(Countif(Sheet2!$A:$A,$A2)0,"Keep","Delete") then drag fill down the column select cell A1 Do Data=filter=Autofilter in the column B dropdown, select Delete Now only rows to be deleted are visible. In the gray labels to the left, select the first visible row below row1 hold down the Shift key, scroll down to the last visible row and again click in the gray label. This will select all rows from the first visible to the last visible. Do Edit=Delete this will only delete the visible rows. Now do Data=Filter=Autofilter to remove the autofilter. Select Column B and do Edit=Delete Repeate this for the second sheet where the formula would be =if(Countif(Sheet1!$A:$A,$A2)0,"Keep","Delete") -- Regards, Tom Ogilvy "WillRRn" wrote in message ... As a Nursing data analyst, I must combine information from multiple sources. Each source contains different elements I need to import. Right now I have several large groups of data that go back to 1999. So each database has about 2000 entries. I have imported all the data to excel in worksheets. The good news is that each group of data is indexed with a patient medical record number in the first row. The bad news is that about 5% of the entries don't have a corresponding medical record number both worksheets. All I want to do is to add in the data items that have a medical record number on both sheets. Entries indexed with the medical record that don't exist in both worksheets can either be skipped or even deleted. Is there a way to do this either native to excel or some code that someone has already written? I am fairly new to Excel and Visual Basic. Can someone point me in the right direction here? Thanks -- WillRRn |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding in data from another database
WooHoo! worked like a charm . . . : )
"Tom Ogilvy" wrote: I said: (select Column B, do Edit=Insert) I should have said (select Column B, do Insert=Column) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Assume the medical record number is in column A of each of Sheet1 and sheet2 Assume the first row of each sheet has column headers/labels. in sheet1, insert a new column B (select Column B, do Edit=Insert) Give it a header of something like MATCH in B2 put in a formula =if(Countif(Sheet2!$A:$A,$A2)0,"Keep","Delete") then drag fill down the column select cell A1 Do Data=filter=Autofilter in the column B dropdown, select Delete Now only rows to be deleted are visible. In the gray labels to the left, select the first visible row below row1 hold down the Shift key, scroll down to the last visible row and again click in the gray label. This will select all rows from the first visible to the last visible. Do Edit=Delete this will only delete the visible rows. Now do Data=Filter=Autofilter to remove the autofilter. Select Column B and do Edit=Delete Repeate this for the second sheet where the formula would be =if(Countif(Sheet1!$A:$A,$A2)0,"Keep","Delete") -- Regards, Tom Ogilvy "WillRRn" wrote in message ... As a Nursing data analyst, I must combine information from multiple sources. Each source contains different elements I need to import. Right now I have several large groups of data that go back to 1999. So each database has about 2000 entries. I have imported all the data to excel in worksheets. The good news is that each group of data is indexed with a patient medical record number in the first row. The bad news is that about 5% of the entries don't have a corresponding medical record number both worksheets. All I want to do is to add in the data items that have a medical record number on both sheets. Entries indexed with the medical record that don't exist in both worksheets can either be skipped or even deleted. Is there a way to do this either native to excel or some code that someone has already written? I am fairly new to Excel and Visual Basic. Can someone point me in the right direction here? Thanks -- WillRRn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data to another table in database based on condition | Excel Worksheet Functions | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Adding database data to a combo box... | Excel Discussion (Misc queries) | |||
Adding data from Excel sheet to Access database | Excel Programming | |||
Adding New Records To Excel Database.. | Excel Programming |