?
Solved

Excel question

Posted on 2011-02-25
12
Medium Priority
?
473 Views
Last Modified: 2012-05-11
I need some Excel assistance please!  Here is the issue.  I have a spreadsheet with two tabs.  In the first tab I have serial number and warranty information columns.  In the second tab I have the serial number and the server name.  Is there a way I can match up the server names and the warranty information columns in a new tab using the serial number as the matching identifier?
0
Comment
Question by:Chris Patterson
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 5

Expert Comment

by:jarrod_williams
ID: 34979489

•Create a new worksheet as a destination for your consolidated data. You can start a new workbook by clicking the "New" icon at the top left. Or, add a new worksheet to your current workbook by clicking the "Insert" tab on the toolbar and selecting "Worksheet." In the new worksheet, click inside the cell to which you would like to transfer your consolidated data. Be sure to leave enough room below and to the right of the destination cell for the transferred data.

•3
Select the data you wish to consolidate. Click on the "Data" tab on the tool bar. From the choices given, select "Consolidate." The Consolidation Tool dialog box will open. The first option you have is to choose the function. Since you are consolidating data, you will be performing a "Sum" function, which is already selected. You may leave that as it is. In the Reference field, type out the range of the first source area. If you are selecting data from different worksheets in the same workbook, include only sheet and cell references. If your data is from different workbooks, include a book reference as well. You can also select the area by clicking and dragging over the source area with your mouse. Excel will fill in the proper range description for you. When the source area range is entered in the Reference field, click the "Add" button to the right. Notice your range has been added to the "All references" box below. Repeat this step until all data sources have been added.

•4
Consolidate the data. By clicking "Create links to source data," your master worksheet will automatically update whenever changes are made to the original sources. If your source data has labels in either the top row or left column, select those options accordingly by checking the appropriate boxes under "Use labels in." When finished, click "OK." Your master worksheet will be created.



taken from:  http://www.ehow.com/how_5872744_join-together-using-ms-excel.html
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34979540
Are the serial numbers listed in the same order in both worksheets or are they different? If they are the same you can just copy sheet2 and call it sheet3 (right-click on sheet tab at the bottom of the sheet and use "Move or Copy" option) then use a lookup formula to get the warranty information, i.e. in C2 of new sheet

=INDEX(sheet1!B:B,MATCH(A2,Sheet1!$A:$A,0))

where A2 is the serial number and sheet 1 contains serial numbers in column A and warranty information in column B and rightwards

copy formula down and across

regards, barry
0
 
LVL 7

Author Comment

by:Chris Patterson
ID: 34979602
The serial numbers are in different orders across the two tabs.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Chris Patterson
ID: 34979631
jarrod_williams : I followed the instructions listed above and get and get "Reference is not valid" error.
0
 
LVL 5

Expert Comment

by:jarrod_williams
ID: 34979680
hmmm. interesting.. I tried that solution on my PC and it worked.. Tried copy pasting then?
0
 
LVL 7

Author Comment

by:Chris Patterson
ID: 34979735
Let me explain some more here.  In sheet1 the "A" column contains the serial number and the "F" column contains the warranty date.  In sheet 2 the "B" column contains the server name and the "H" column contains the serial number.
0
 
LVL 4

Expert Comment

by:kelle1
ID: 34979817
Is this a one time thing?  You can sort the information in tabs 1 and 2 by serial number, then Copy and paste into tab 3 and they should match up, unless one is missing a serial number that the other has.
0
 
LVL 7

Author Comment

by:Chris Patterson
ID: 34979841
This is a one time thing, however one list of serial numbers is much longer than the other, i.e. it has every device in the company.  The other list of serial numbers is just the servers.
0
 
LVL 7

Author Comment

by:Chris Patterson
ID: 34979993
Ok, so if I just paste the four columns into a new sheet, is there a way to match up columns A and B?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 34980153
You could just get the server data into sheet1 with a formula, e.g. in a spare column in row 2 use this formula copied down

=IF(ISNA(MATCH(A2,Sheet2!H:H,0)),"",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!H:H,0)))

That will give you the server ID or a blank if there isn't a match

You can copy all data to another sheet if required

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34980240
As this is a "one-off" you can do the above to get evertthing in one sheet then copy the column with the new formula, use Edit > Psate Special > Values to convert the formulas to hardcoded values then copy the whole sheet to a new one, delete the new column from sheet 1 and then delete all unwanted columns from new sheet, Voila! job done.....:)

regards, barry
0
 
LVL 7

Author Closing Comment

by:Chris Patterson
ID: 34980270
Thank you so much Barry!  The formula worked perfectly. First round is on me :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question