Results 1 to 7 of 7

Thread: Extracting information

  1. #1

    Lightbulb Extracting information



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi
    I am trying to make my life easier at work using excel, and there is one thing I need help with.
    Here is the case:

    I got 2 spreadsheets, lets call them A & B.
    In spreadsheet A I have thousands of lines with information, where each line has 8 columns.
    The first column of each line has a case number (which I am interested in), followed by different kinds of information.

    I get contacted by a company, giving me spreadsheet B, containing 50 case numbers (which are not sorted in any order)

    I need to find each case number from spreadsheet A, and get all that information into spreadsheet B. (Right now, I copy each line seperately, and it takes a lot of time)

    Is there a way that I can get Excel to give me the 50 case numbers from spreadsheet A, and transfer all the information (all 8 columns) over to spreadsheet B? (is the a formula that makes Excel search a column in spreadsheet A for all the case numbers in spreadsheet B, and if it finds a match, it will copy the information in all 8 columns over to spreadsheet B)

    If you could help me with this, I would save hours of work every week =)

  2. #2
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    93
    Articles
    0
    Excel Version
    2013, 2016, O365
    Formulas can't change or move cell contents.

    Here are two approaches to consider, though

    • Add a new tab to Workbook A
    • A1: the same column heading you have on your data sheet....Case Num (or whatever you have)
    • Under A1, copy/paste the case number list from Workbook B

    Option 1
    • On your data tab,...Select the case number data, including the heading
    ...Data.Advanced (Click OK if you get the "cannot recognize" error)
    ...List Range: (already selected)
    ...Criteria Range: (select the list on the new tab, including the heading)
    ...Click: OK

    Option 2
    • Put a helper formula to the right of your data with a formula finds matches...something like this: =MATCH(A2,Sheet2!$A$1:$A$50,0)
    • Copy that formula down as far as you need
    • Data.Filter
    ...Click the dropdown
    ...UN-check the error at the bottom of the list


    Now only matching case numbers should now be visible.
    • Copy the visible cells from Workbook A to Workbook B

    Is that something you can work with?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by nesheim View Post
    I am trying to make my life easier at work using excel, and there is one thing I need help with.
    Can you attach a simulated example of your files and write the expected results.
    In the meantime, look at VLOOKUP or INDEX/MATCH
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #4

    Question Attached File

    I attached a excel file with an example.
    I just added 2 months of the information from spreadsheet A, and I also pasted spreadsheet B into the same sheet.

    -There will be some norwegian sentences in this, but you do not need to worry about that. The only thing that I am interested in, is matching the case numbers from B and A. When it comes to gather the information, all we need is the case number.

    I marked the case number headline from A with the color red, and the case number headline from B with yellow.
    -Note that spreadsheet B is just a converted PDF to Excel file. (I do not know if that matters in any way)

    As you will see is that the columns from spreadsheet B and A have different kinds of information. I want to add the columns that are already in A, -> next to what is already in B. (But only for the few case numbers that are already in B)


    -If you search in the spreadsheet for case number 3661143, you will find one in row 93, column B. Now I want to copy the information from column C -> L, and put it in spreadsheet B. (you will find 3661143 in spreadsheet B too, row 3+4, column O). Now I want the information at A next to all the information in B.
    Attached Files Attached Files
    Last edited by nesheim; 2015-09-21 at 03:55 PM.

  5. #5
    Ron Coderre, ill try it out, and tell you if it works or not.
    Last edited by nesheim; 2015-09-21 at 03:58 PM.

  6. #6
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Test.Case.Numbers,AB-Version2.xlsxTest.Case.Numbers,AB-Version2.xlsxI used INDEX/MATCH to get the info from the first set of columns into the second set. The first row's formulas are just copied down as many rows as needed.

  7. #7
    Quote Originally Posted by candybg View Post
    Test.Case.Numbers,AB-Version2.xlsxTest.Case.Numbers,AB-Version2.xlsxI used INDEX/MATCH to get the info from the first set of columns into the second set. The first row's formulas are just copied down as many rows as needed.
    Wow, thanks alot, just as I wanted it to be! This saves me so much time!
    It is the first time I am using this forum, thanks for quick replies and good help =)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •