Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel question

Posted on 2011-02-25
12
Medium Priority
?
471 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

618 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