Extracting information

N

nesheim

New member
Joined
Sep 21, 2015
Messages
6
Reaction score
0
Points
0
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 =)
 
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?
 
nesheim said:
I am trying to make my life easier at work using excel, and there is one thing I need help with.
Click to expand...
Can you attach a simulated example of your files and write the expected results.
In the meantime, look at VLOOKUP or INDEX/MATCH
 
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.
 

Attachments

  • Test.Case.Numbers,AB.xlsx
    119.6 KB · Views: 19
Last edited:
Ron Coderre, ill try it out, and tell you if it works or not.
 
Last edited:
You must log in or register to reply here.
Back
Top