Solved

Excel question

Posted on 2011-02-25
12
444 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Marketers need statistics and metrics like everybody else needs oxygen. In this article we explain how to enable marketing campaign statistics for Microsoft Exchange mail.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now