Solved

Excel question

Posted on 2011-02-25
12
459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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 500 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

733 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