Data Clean-up - Additional Techniques
Margaret Hogarth February 2nd, 2010
Here are the techniques that came up during the Data Clean-up discussion:
v-lookup:
“Use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.”
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
How to identify duplicates in Excel 2007 without blowing them away:
Use the subtotal function
or
Use conditional formatting: Select cells [or choose cells with certain conditions] > Home > Styles > Conditional Formatting > Manage Rules > New Rule > [type in condition(s) > Apply Rule
Diane Carroll has two macros:
1) Title: WSU Wizard using EXCEL Authors: Ellis, Cindy; Carroll, Diane
This Wizard (and there really is a wizard) will clean up Innovative data of initial articles and ending periods. It will also separate ISSN into four columns and remove the extraneous data. It can also add up costs from order records with multiple posting with some editing.
https://research.wsulibs.wsu.edu:8443/dspace/handle/2376/2246
2) Title: Procedures for creating a Serials Decision Database. Author: Diane Carroll
Nick Peterson’s Merge macro. Will match on any identifier you select and move data from one worksheet to another. PDF explains how to use it and the EXCEL spreadsheet contains the Macro. I LIVE AND DIE BY THIS ONE.
https://research.wsulibs.wsu.edu:8443/dspace/handle/2376/2277
This record has procedures on how to download Web of Science authored and cited records and how to get order record information from Innovative for a defined period of time (uses character base to do this). I loaded this information to make available all procedures I use to create a serials decision database. It is not perfect.
It was pointed out that one can select all XML text (control+a) and paste (control v) into Excel without problems. It formats beautifully.
Margaret Hogarth margaret.hogarth@ucr.edu
- Uncategorized
- Comments(0)
