Solved

Excel question

Posted on 2011-02-25
12
452 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
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.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Easy CSR creation in Exchange 2007,2010 and 2013
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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