1. ## Extracting information

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. 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)
...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?

3. Originally Posted by nesheim
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

4. ## 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.

5. Ron Coderre, ill try it out, and tell you if it works or not.

6. 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. Originally Posted by candybg
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 =)