comparing 2 columns

[Jeff]

I have 2 Excel sheets. Each has about 350 rows and each contain a column
containing lecture titles.

I need to compare the corresponding lecture titles columns (one in each
XL file) to see if a cell is missing in one or the other.

How could I do that?

I am thinking of sorting bnoth files by their lecture titles column.
Then copy the lecture titles column from one file and paste it alongside
the corresponding one to see if there are any missing titles.

Is there an easier automatic XL way?

Thanks.

Jeff

 

[Jim Cone]

You can use the CountIf function.
Data in B2:B350 on both sheets
Enter the formula and fill down on both sheets - use the appropriate sheet reference.

=COUNTIF(B2,Sheet2!$B$2:$B$350) 'sheet 1 formula references sheet 2
=COUNTIF(B2,Sheet1!$B$2:$B$350) 'sheet 2 formula references sheet 1

Note the absolute references "$" for the data range.
The formulas return 0 for a missing entry.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
("Extras for Excel" is worth a look)



<[email protected]>
wrote in message
news:[email protected]...

 

[Zaidy036]

I have 2 Excel sheets. Each has about 350 rows and each contain a column
containing lecture titles.

I need to compare the corresponding lecture titles columns (one in each
XL file) to see if a cell is missing in one or the other.

How could I do that?

I am thinking of sorting bnoth files by their lecture titles column.
Then copy the lecture titles column from one file and paste it alongside
the corresponding one to see if there are any missing titles.

Is there an easier automatic XL way?

Thanks.

Jeff

Use conditional formating on one column.

Use formula: if A Not Eq B then text is Bold Red

 

[Jeff]

Use conditional formating on one column.

Use formula: if A Not Eq B then text is Bold Red

Sounds good. Can you please explain? Where do I put the formula? In a
3rd column? Assuming the columns I want to compare are A and B, do I put
that formula in column C?

 

[Jeff]

You can use the CountIf function.
Data in B2:B350 on both sheets
Enter the formula and fill down on both sheets - use the appropriate sheet reference.

=COUNTIF(B2,Sheet2!$B$2:$B$350) 'sheet 1 formula references sheet 2
=COUNTIF(B2,Sheet1!$B$2:$B$350) 'sheet 2 formula references sheet 1

Note the absolute references "$" for the data range.
The formulas return 0 for a missing entry.

Sounds very good but I am a newbie, can you explain the actual steps?

Where do I enter these formulas?

Thank you.

Jeff

 

[Jim Cone]

The formulas, as presented, work only in a single workbook on two sheets named Sheet1 and Sheet2.
The formulas will work in separate workbooks, if the workbook (file) name is inserted into each
formula.
The first formula goes on Sheet1 in an empty column starting on the same row as the first row of
your data.
The second formula goes on Sheet2...

You must adjust the cell references to agree with the actual location of your data.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(independent review of add-in with 30 ways to sort)



<[email protected]>
wrote in message

 

[Zaidy036]

Sounds good. Can you please explain? Where do I put the formula? In a
3rd column? Assuming the columns I want to compare are A and B, do I put
that formula in column C?

You should not have to copy the column to the second sheet.
1. go to ASAP-Utilities and download the application. It is free for non-
commercial use.
2. Install ASAP.
3. On each worksheet if need indicator on BOTH sheets.
a. highlight the lecture titles column (Assumed A)
b. select ASAP-Text-Remove leading, trailing, and excess spaces.

this is to insure that entries on both sheets are the same.

c. select the top (first) lecture titles data cell (Assume A2)
d. find "Format Cells" and select "Conditional"
e. enter formula:
On Sheet1: value <> sheet2!A2 (substitute Name of sheet)
On Sheet2: value <> sheet1!A2
f. enter format of choice to indicate different
f. Select copy
g. Select "Paste Special"
h. select "Formats"
i. highlight A2 to the bottom and Enter
j. if order may get changed by edit then sort the column on both sheets.

Now the entry in a cell will be your chosen format if different than entry in
other sheet. A missing row on one sheet would turn all following entries to
chosen format.

On new versions of Excel there is a lot of room for data = why have two sheets
in the first place?

 

[Jeff]

The formulas, as presented, work only in a single workbook on two sheets named Sheet1 and Sheet2.
The formulas will work in separate workbooks, if the workbook (file) name is inserted into each
formula.
The first formula goes on Sheet1 in an empty column starting on the same row as the first row of
your data.
The second formula goes on Sheet2...

You must adjust the cell references to agree with the actual location of your data.

Thank you very much!

 

[Jeff]

You should not have to copy the column to the second sheet.
1. go to ASAP-Utilities and download the application. It is free for non-
commercial use.
2. Install ASAP.
3. On each worksheet if need indicator on BOTH sheets.
a. highlight the lecture titles column (Assumed A)
b. select ASAP-Text-Remove leading, trailing, and excess spaces.
c. select the top (first) lecture titles data cell (Assume A2)
d. find "Format Cells" and select "Conditional"
e. enter formula:
On Sheet1: value<> sheet2!A2 (substitute Name of sheet)
On Sheet2: value<> sheet1!A2
f. enter format of choice to indicate different
f. Select copy
g. Select "Paste Special"
h. select "Formats"
i. highlight A2 to the bottom and Enter
j. if order may get changed by edit then sort the column on both sheets.

Now the entry in a cell will be your chosen format if different than entry in
other sheet. A missing row on one sheet would turn all following entries to
chosen format.

On new versions of Excel there is a lot of room for data = why have two sheets
in the first place?

Thank you very much!