Announcement

Collapse
No announcement yet.

Lookup Data & Return Same Row But Different Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup Data & Return Same Row But Different Column



    I'll do my best to explain what situation I have and what I need to be done.
    I have an excel book containing a database of citations of articles, with information such as Title, Author, Year, Abstract etc.
    In the database I have multiple tabs of different categories, and there are duplicates across tabs.

    I have used SPSS to aggregate the different categories into one database, merging duplicates and tagging each article with all the different categories it previously appeared in. However the problem is when creating the excel book from SPSS for some reason the string length for each field was limited to 255 characters. So the abstracts in the aggregated excel database are cut short.

    What I want to try and do is copy the corresponding full abstracts from the original database into the cleaned up one.

    I can join all the citations from the different tabs into one big list which will be similar to the aggregated database but still with duplicates and no tags for each article. And since the articles are merged/deleted when aggregated, there are many versions of an article which are in the original database but not in the aggregated one.

    What I was thinking is somehow develop a command which:
    1. Takes the first 75 or so characters of an abstract in the original database
    2. Searches for this 75 character string in the aggregated database and finds the corresponding short abstract
    3. Copies the entire full abstract from the original database,
    4. Replaces the cut short abstract with the corresponding full abstract.


    This is the ideal method but if for some reason this is too difficult then each article also has a unique ID number which could be used instead as so:
    1. Reads the ID number of an article from the original database
    2. Copies the corresponding full abstract
    3. Finds the article with the same ID number in the aggregated database
    4. Replaces the cut short abstract with the corresponding full abstract.
    5. If the ID number is not found in the aggregated database, then skip and go to the next one.

    However since I did some manual merging of references this method would not work 100%

    In the original database. Abstract is in column I and ID number in H.
    In aggregated database, Abstract is in column E and ID number in I
    See attached small sample workbook. 1st tab is a small sample of the original database with full abstracts and second tab is aggregated database sample. The unique ID number is AccNo.

    If anyone could help out it will be greatly appreciated!
    Attached Files

  • #2
    Originally posted by SnowToad View Post
    What I was thinking is somehow develop a command which:
    1. Takes the first 75 or so characters of an abstract in the original database
    2. Searches for this 75 character string in the aggregated database and finds the corresponding short abstract
    3. Copies the entire full abstract from the original database,
    4. Replaces the cut short abstract with the corresponding full abstract.


    This is the ideal method but if for some reason this is too difficult then each article also has a unique ID number which could be used instead as so:
    1. Reads the ID number of an article from the original database
    2. Copies the corresponding full abstract
    3. Finds the article with the same ID number in the aggregated database
    4. Replaces the cut short abstract with the corresponding full abstract.
    5. If the ID number is not found in the aggregated database, then skip and go to the next one.
    I'm not sure why you feel that the first method is the ideal. To me using a unique key identified is ideal, because then you can just a simple lookup formula -

    In Aggregated Database sheet, cell C2:
    =VLOOKUP($I2,'Original Database'!$H$1:$K$10000,2,FALSE)
    Drag / copy this formula down for each line in your in aggregated database.
    Adjust the $H$1:K10000 in the formula as this points to original database and I'm sure your original database might have more than 10000 rows. It can be made dynamic if you wish.
    Unique numbers that arent found will return #N/A filter them out afterwards (you already have filter in place anyway).

    Regards,
    Ger
    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Originally posted by Ger Plante View Post
      I'm not sure why you feel that the first method is the ideal. To me using a unique key identified is ideal, because then you can just a simple lookup formula -

      In Aggregated Database sheet, cell C2:
      =VLOOKUP($I2,'Original Database'!$H$1:$K$10000,2,FALSE)
      Drag / copy this formula down for each line in your in aggregated database.
      Adjust the $H$1:K10000 in the formula as this points to original database and I'm sure your original database might have more than 10000 rows. It can be made dynamic if you wish.
      Unique numbers that arent found will return #N/A filter them out afterwards (you already have filter in place anyway).

      Regards,
      Ger
      Cheers Ger, I'll give that a go

      The reason why using AccNo is not perfect is that I have manually merged some citations so that say if there were a pair of duplicates, one had all accurate info but no abstract, and the other had an abstract, and i copied the abstract from the second one into the first. Then if I use the AccNo of the first citation, it will find the merged citation in the aggregated database and replace that one's abstract with blank (as it was originally).

      Would there be a way to make it ignore the citation in the original database if the abstract field was blank?

      EDIT: I've tried it out now, it worked perfectly and I manually replaced the leftover abstracts which were not found and I think it's all done! Thankyou very much!
      Last edited by SnowToad; May 31st, 2010, 11:14.

      Comment


      • #4


        Thanks for getting back to us SnowToad. Glad I could help.

        Afterwards, if you want to remove the VLOOKUP formula from the cell and just leave the full abstract, select the entire column (Column C) by clicking on the column header, right click on column C, click on paste special and click on values.

        Regards,
        Ger
        Check out our new reputation system. Click on the Like button under the post!
        _______________________________________________

        There are 10 types of people in the world. Those that understand Binary and those that dont.

        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

        The BEST Lookup function of all time

        Dynamic Named Ranges are your bestest friend

        _______________________________________________

        Comment

        Working...
        X