[tycelchu]

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!

[etaf]

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

[tycelchu]

Thanks very much! That (or a slight tweak on it) worked a treat!

[etaf]

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]

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]

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]

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]

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.