Duplicates in Excel 2007

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

Is it possible to find duplicate values in two different workbooks. I know I
can use conditional formatting in a single spreadsheet. I'm using Excel 2007.

Thanks
 
Hi,

Here is an example of applying conditional formatting between workbooks:

1. In the one workbook name the range containing the serial numbers, here I
named it Data. (highlight the range and choose Insert, Name, Define, type
Data into the Names in workbook box and click OK).
2. Move to the other workbook and in an empty cell type = and then click on
any cell in the other workbook to build a formula like
'[10-21-2008.xls]Sheet2'!$A5
3. Edit this formula so it reads
'[10-21-2008.xls]Sheet2'!Data
Press Enter and the formula will change to
='10-21-2008.xls'!Data
4. Highlight the formula on the Formula Bar and copy it, press Enter once to
escape the Formula Bar
5. Choose the command Insert, Name, Define and enter a name like FSE1 in the
Names in workbook box (if you are using 2007 make it four letter followed by
a number to be on the safe side)
6. Click in the Refers to box, clear the existing entry and choose Paste.
This should paste the formula from step 3 into the Refers to box. Click OK.
7. Select the range with your serial numbers say A1:A100 and choose Format,
Conditional Formatting, Formula is from the first drop down, and in the
second box enter =OR(A1=FSE1) pick Format and choose what you want. Click
OK twice.
 
Hi Shane

Many thanks for your reply, I'll give it a go and let you know.

Shane Devenshire said:
Hi,

Here is an example of applying conditional formatting between workbooks:

1. In the one workbook name the range containing the serial numbers, here I
named it Data. (highlight the range and choose Insert, Name, Define, type
Data into the Names in workbook box and click OK).
2. Move to the other workbook and in an empty cell type = and then click on
any cell in the other workbook to build a formula like
'[10-21-2008.xls]Sheet2'!$A5
3. Edit this formula so it reads
'[10-21-2008.xls]Sheet2'!Data
Press Enter and the formula will change to
='10-21-2008.xls'!Data
4. Highlight the formula on the Formula Bar and copy it, press Enter once to
escape the Formula Bar
5. Choose the command Insert, Name, Define and enter a name like FSE1 in the
Names in workbook box (if you are using 2007 make it four letter followed by
a number to be on the safe side)
6. Click in the Refers to box, clear the existing entry and choose Paste.
This should paste the formula from step 3 into the Refers to box. Click OK.
7. Select the range with your serial numbers say A1:A100 and choose Format,
Conditional Formatting, Formula is from the first drop down, and in the
second box enter =OR(A1=FSE1) pick Format and choose what you want. Click
OK twice.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Nigel said:
Is it possible to find duplicate values in two different workbooks. I know I
can use conditional formatting in a single spreadsheet. I'm using Excel 2007.

Thanks
Click to expand...
Click to expand...
 
Hi Shane

Finally got time to try it, and many thanks, works just as I wanted.

Thank you

Shane Devenshire said:
Hi,

Here is an example of applying conditional formatting between workbooks:

1. In the one workbook name the range containing the serial numbers, here I
named it Data. (highlight the range and choose Insert, Name, Define, type
Data into the Names in workbook box and click OK).
2. Move to the other workbook and in an empty cell type = and then click on
any cell in the other workbook to build a formula like
'[10-21-2008.xls]Sheet2'!$A5
3. Edit this formula so it reads
'[10-21-2008.xls]Sheet2'!Data
Press Enter and the formula will change to
='10-21-2008.xls'!Data
4. Highlight the formula on the Formula Bar and copy it, press Enter once to
escape the Formula Bar
5. Choose the command Insert, Name, Define and enter a name like FSE1 in the
Names in workbook box (if you are using 2007 make it four letter followed by
a number to be on the safe side)
6. Click in the Refers to box, clear the existing entry and choose Paste.
This should paste the formula from step 3 into the Refers to box. Click OK.
7. Select the range with your serial numbers say A1:A100 and choose Format,
Conditional Formatting, Formula is from the first drop down, and in the
second box enter =OR(A1=FSE1) pick Format and choose what you want. Click
OK twice.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Nigel said:
Is it possible to find duplicate values in two different workbooks. I know I
can use conditional formatting in a single spreadsheet. I'm using Excel 2007.

Thanks
Click to expand...
Click to expand...
 
Hi,

When you close the source workbook, the colors would go away

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Shane Devenshire said:
Hi,

Here is an example of applying conditional formatting between workbooks:

1. In the one workbook name the range containing the serial numbers, here
I
named it Data. (highlight the range and choose Insert, Name, Define, type
Data into the Names in workbook box and click OK).
2. Move to the other workbook and in an empty cell type = and then click
on
any cell in the other workbook to build a formula like
'[10-21-2008.xls]Sheet2'!$A5
3. Edit this formula so it reads
'[10-21-2008.xls]Sheet2'!Data
Press Enter and the formula will change to
='10-21-2008.xls'!Data
4. Highlight the formula on the Formula Bar and copy it, press Enter once
to
escape the Formula Bar
5. Choose the command Insert, Name, Define and enter a name like FSE1 in
the
Names in workbook box (if you are using 2007 make it four letter followed
by
a number to be on the safe side)
6. Click in the Refers to box, clear the existing entry and choose Paste.
This should paste the formula from step 3 into the Refers to box. Click
OK.
7. Select the range with your serial numbers say A1:A100 and choose
Format,
Conditional Formatting, Formula is from the first drop down, and in the
second box enter =OR(A1=FSE1) pick Format and choose what you want.
Click
OK twice.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Nigel said:
Is it possible to find duplicate values in two different workbooks. I
know I
can use conditional formatting in a single spreadsheet. I'm using Excel
2007.

Thanks
Click to expand...
Click to expand...
 
Back
Top