I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!

"However, if one report has a student and the other does not"

Assumes SS numbers to compare are on the same sheet in columns B and H
1. Select column B
2. Choose Format Conditional formatting
3. In "Condition 1" choose "Formula is"
4. Enter this formula =(COUNTIF($H:$H,B1)=0)*(B1<"")
5. Click the "Formats" button and pick a pattern color.
SS numbers in column B not in column H will change color.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare, match, itemize differences with "XL Companion")




"A.R. Hunt"
wrote in message
I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!

I've got a conditional format that changes the font color when there's a
different SSN now, but I still have to go in and manually bump lines down so
that matching SSNs will be side by side. (Sometimes there are items on one
list but not the other throwing off my side by side comparison.) Is there a
way to do a condtional format that would insert a row or cells if there is a
difference?

"Jim Cone" wrote:


"However, if one report has a student and the other does not"

Assumes SS numbers to compare are on the same sheet in columns B and H
1. Select column B
2. Choose Format Conditional formatting
3. In "Condition 1" choose "Formula is"
4. Enter this formula =(COUNTIF($H:$H,B1)=0)*(B1<"")
5. Click the "Formats" button and pick a pattern color.
SS numbers in column B not in column H will change color.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare, match, itemize differences with "XL Companion")




"A.R. Hunt"
wrote in message
I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!

"Is there a way to do a conditional format that would
insert a row or cells if there is a difference?"

No, conditional formatting just formats.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"A.R. Hunt"
wrote in message
I've got a conditional format that changes the font color when there's a
different SSN now, but I still have to go in and manually bump lines down so
that matching SSNs will be side by side. (Sometimes there are items on one
list but not the other throwing off my side by side comparison.) Is there a
way to do a condtional format that would insert a row or cells if there is a
difference?



"Jim Cone" wrote:
"However, if one report has a student and the other does not"

Assumes SS numbers to compare are on the same sheet in columns B and H
1. Select column B
2. Choose Format Conditional formatting
3. In "Condition 1" choose "Formula is"
4. Enter this formula =(COUNTIF($H:$H,B1)=0)*(B1<"")
5. Click the "Formats" button and pick a pattern color.
SS numbers in column B not in column H will change color.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare, match, itemize differences with "XL Companion")




"A.R. Hunt"
wrote in message
I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!

What about a "what if" statement?

"Jim Cone" wrote:

"Is there a way to do a conditional format that would
insert a row or cells if there is a difference?"

No, conditional formatting just formats.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"A.R. Hunt"
wrote in message
I've got a conditional format that changes the font color when there's a
different SSN now, but I still have to go in and manually bump lines down so
that matching SSNs will be side by side. (Sometimes there are items on one
list but not the other throwing off my side by side comparison.) Is there a
way to do a condtional format that would insert a row or cells if there is a
difference?



"Jim Cone" wrote:
"However, if one report has a student and the other does not"

Assumes SS numbers to compare are on the same sheet in columns B and H
1. Select column B
2. Choose Format Conditional formatting
3. In "Condition 1" choose "Formula is"
4. Enter this formula =(COUNTIF($H:$H,B1)=0)*(B1<"")
5. Click the "Formats" button and pick a pattern color.
SS numbers in column B not in column H will change color.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare, match, itemize differences with "XL Companion")




"A.R. Hunt"
wrote in message
I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!

You cannot insert rows using any kind of formula or function.
What you need will require VBA code.
If you go that route you might as well have the code reconcile the differences
instead of just inserting rows.
Any of that goes beyond my "free" limit. However, others here may have a
higher threshold.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"A.R. Hunt"
wrote in message
What about a "what if" statement?


"Jim Cone" wrote:
"Is there a way to do a conditional format that would
insert a row or cells if there is a difference?"

No, conditional formatting just formats.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"A.R. Hunt"
wrote in message
I've got a conditional format that changes the font color when there's a
different SSN now, but I still have to go in and manually bump lines down so
that matching SSNs will be side by side. (Sometimes there are items on one
list but not the other throwing off my side by side comparison.) Is there a
way to do a condtional format that would insert a row or cells if there is a
difference?



"Jim Cone" wrote:
"However, if one report has a student and the other does not"

Assumes SS numbers to compare are on the same sheet in columns B and H
1. Select column B
2. Choose Format Conditional formatting
3. In "Condition 1" choose "Formula is"
4. Enter this formula =(COUNTIF($H:$H,B1)=0)*(B1<"")
5. Click the "Formats" button and pick a pattern color.
SS numbers in column B not in column H will change color.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare, match, itemize differences with "XL Companion")




"A.R. Hunt"
wrote in message
I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!

Oh, well. I thought I'd give it a shot. Thanks for trying.

"Jim Cone" wrote:


You cannot insert rows using any kind of formula or function.
What you need will require VBA code.
If you go that route you might as well have the code reconcile the differences
instead of just inserting rows.
Any of that goes beyond my "free" limit. However, others here may have a
higher threshold.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"A.R. Hunt"
wrote in message
What about a "what if" statement?


"Jim Cone" wrote:
"Is there a way to do a conditional format that would
insert a row or cells if there is a difference?"

No, conditional formatting just formats.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"A.R. Hunt"
wrote in message
I've got a conditional format that changes the font color when there's a
different SSN now, but I still have to go in and manually bump lines down so
that matching SSNs will be side by side. (Sometimes there are items on one
list but not the other throwing off my side by side comparison.) Is there a
way to do a condtional format that would insert a row or cells if there is a
difference?



"Jim Cone" wrote:
"However, if one report has a student and the other does not"

Assumes SS numbers to compare are on the same sheet in columns B and H
1. Select column B
2. Choose Format Conditional formatting
3. In "Condition 1" choose "Formula is"
4. Enter this formula =(COUNTIF($H:$H,B1)=0)*(B1<"")
5. Click the "Formats" button and pick a pattern color.
SS numbers in column B not in column H will change color.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - compare, match, itemize differences with "XL Companion")




"A.R. Hunt"
wrote in message
I have 2 reports (approx 8000 lines) with similar data that I need to match
up and find differences in. I copy and paste them side by side onto one sheet
(1 has 5 columns--the other has 12). I insert columns with formulas to find
the differences between the items that should be common--SSN, Dollar amounts,
and hours. I sort by SSN and term on each side. That should match them up.
However, if one report has a student and the other does not, my formula
pinpoints it by showing a variance in SSN. I then have to insert cells to get
my common students to line up again. (Make any sense?) Anyway, I was
wondering if anyone has any suggestions as to how I can streamline this
process. Thanks in advance!