Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Comparing/Matching data across in Excel worksheets & more


(!)

tycelchu's Avatar
tycelchu tycelchu is offline
Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2005
Experience: beginning to be intermediate
10-Apr-2012, 05:48 AM #1
Solved: Comparing/Matching data across in Excel worksheets & more
Hi,

Hopefully one of you delightful people can help me with a small problem I'm having pulling together data from multiple reports... I have manually combined 4 separate reports into 1 workbook with 4 worksheets (1 for each report). Each row represents a separate job.

The 1st worksheet is an export from our own Access database, the 3 others come from external reports.

Whilst each report/worksheet contains different sets of data, 1 column in each worksheet contains ‘CSR’ or ‘Site ID’ numbers. These numbers can be used to match the jobs - however a) not every worksheet has every number b) some numbers may appear more than once on the same worksheet (these are essentially returns to the same job).

The 1st worksheet also has a column with OPID numbers (Column A), these are unique numbers that we have paired to a CSR or Site ID number.

I want to compare the numbers in the CSR column of the 1st worksheet (Column B of Sheet 1) with the CSR columns in the other worksheets and where there is a match, copy/add the corresponding OPID number to a blank column in each worksheet.

What would be the best way to do this?

Thanks in advance for any help and advice offered!

Last edited by tycelchu; 10-Apr-2012 at 08:22 AM..
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,552 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
10-Apr-2012, 11:11 AM #2
Quote:
The 1st worksheet also has a column with OPID numbers (Column A), these are unique numbers that we have paired to a CSR or Site ID number.
so on the 1st worksheet only appear once

if so you could use vlookup to do this

so on a new column in one of the other worksheets say Column D

do this

=VLOOKUP( Cell to compare on the worksheet, Range on the sheet1 worksheets with $ in , column to copy data from , false)

then copy the formula down the page and onto the other sheets

assume worksheet 1 all the Site ID numbers are in column B starting at B2 to B500

then in worksheet 2 the csr is in column A starting at A2

then the formula say in worksheet 2 column D
=VLOOKUP(A2,Sheet1!$B$2:$B$500,1,FALSE)

if it finds the number it will return the number in the column - if not than #n/a
__________________
Wayne
Please let us know what the final solution was to any problem posted
tycelchu's Avatar
tycelchu tycelchu is offline
Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2005
Experience: beginning to be intermediate
13-Apr-2012, 04:13 AM #3
Thanks very much! That (or a slight tweak on it) worked a treat!
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,552 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
13-Apr-2012, 05:08 AM #4
your welcome thanks for letting us know
You can mark your own threads solved using the button at the top of the page of the thread in the upper left corner.
tycelchu's Avatar
tycelchu tycelchu is offline
Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2005
Experience: beginning to be intermediate
13-Apr-2012, 05:51 AM #5
I do have a follow on query if it's ok to ask here?

I now have all of the reports in one workbook and have been able to remove any jobs from the seperate worksheets without an OPID number thanks to the VLOOKUP.

My boss has now asked if it is possible compile the data from each of the worksheets into one. The problem with that is that the five worksheets have different amounts of rows/jobs on (1436 on the main worksheet, 84, 331, 124 & 786 on the other four) so a simple copy/paste isn't going to work.

Essentially what I need to do is cross compare the jobs up via their OPID number, and (by way of example) if an OPID number in Worksheet 2 matches one in Worksheet 1 - copy the entire row (A-O) from Worksheet 2 into empty columns (DD-DR) at the end of the corresponding row in Worksheet 1.

Hopefully this makes sense, please ask if not.

Thanks again in advance for any assistance.

Ty
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,552 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
13-Apr-2012, 06:00 AM #6
you can stilll use vlookup for that

so in DD
enter the vlookup formula
in worksheet 1

=VLOOKUP(A2,Sheet2!$A$2:$O$500,1,FALSE)

then in DE
=VLOOKUP(A2,Sheet2!$A$2:$O$500,2,FALSE)

then in DF
=VLOOKUP(A2,Sheet2!$A$2:$O$500,3,FALSE)

Notice the third element (after the , increases by 1 ) so this looks up the value in A2 and then looks down sheet2 column A until it finds a match
Then the 1,2,3 etc
1 returns the matching line in the 1st column
2 returns the matching line in the 2nd column
3 returns the matching line in the 3rd column
etc to column O = 15

with a slight tweak should work
tycelchu's Avatar
tycelchu tycelchu is offline
Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2005
Experience: beginning to be intermediate
13-Apr-2012, 07:02 AM #7
ACE! Thanks again! I was sure I'd be able to use the VLOOKUP again somehow but couldn't figure out the 'third element' as you put it.

You're a star!
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,552 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
13-Apr-2012, 09:00 AM #8
your welcome thanks for letting us know
You can mark your own threads solved using the button at the top of the page of the thread in the upper left corner.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
excel

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑