I'm working with Excel 2003 and created a workbook (1 worksheet) to compare 3
columns of data. Each column varies in number of rows, but could have as
many as 800 lines. Two problems (although minor, as I have a work-around to
resolve), but I am wondering if there is a better way to do it.

Columns C, D & E have the data I need to compare. In Column A there is a
VLookup formula that compares C & D to find a match. Then in Column B, I did
another VLookup formula that looks for a match for the results of Column A &
E. It works, but possibly there is a better solution.

Second problem, each day we are copying a new set of data into the next
blank column (F), then deleting Column C, so that we always have only 3
columns. Of course when Column C is deleted, the formula goes to REF#. I
did a work around by writing a small macro that re-inputs the formulas into
Columns A & B. Is there any way to retain the formula?

Thanks for any help.
--
Linda

First problem.

Why do you use VLOOKUP to compare two cells, why not just use

=C2=D2

Second problem

Why delete column C, just copy over it.

--

HTH

Bob

"L.Mathe" wrote in message
...
I'm working with Excel 2003 and created a workbook (1 worksheet) to
compare 3
columns of data. Each column varies in number of rows, but could have as
many as 800 lines. Two problems (although minor, as I have a work-around
to
resolve), but I am wondering if there is a better way to do it.

Columns C, D & E have the data I need to compare. In Column A there is a
VLookup formula that compares C & D to find a match. Then in Column B, I
did
another VLookup formula that looks for a match for the results of Column A
&
E. It works, but possibly there is a better solution.

Second problem, each day we are copying a new set of data into the next
blank column (F), then deleting Column C, so that we always have only 3
columns. Of course when Column C is deleted, the formula goes to REF#. I
did a work around by writing a small macro that re-inputs the formulas
into
Columns A & B. Is there any way to retain the formula?

Thanks for any help.
--
Linda

The data could be anywhere in the other 2 columns (or may not). So I need to
find the data that matches (exists) in all 3 columns.

Second problem: What we have to do is sequentially pull in the previous
day's work for comparison. IE: Col C has the work from Mar. 1, Col. D is
Mar. 2, and Col. E is Mar. 3. So the following day I will copy the work of
Mar 4 into the next empty column, eliminate Mar 1, so I am working only with
Mar 2, 3 & 4th data, etc. The day after, it would be Mar 3, 4 & 5th, etc.

Hope this explains it better!
--
Linda


"Bob Phillips" wrote:

First problem.

Why do you use VLOOKUP to compare two cells, why not just use

=C2=D2

Second problem

Why delete column C, just copy over it.

--

HTH

Bob

"L.Mathe" wrote in message
...
I'm working with Excel 2003 and created a workbook (1 worksheet) to
compare 3
columns of data. Each column varies in number of rows, but could have as
many as 800 lines. Two problems (although minor, as I have a work-around
to
resolve), but I am wondering if there is a better way to do it.

Columns C, D & E have the data I need to compare. In Column A there is a
VLookup formula that compares C & D to find a match. Then in Column B, I
did
another VLookup formula that looks for a match for the results of Column A
&
E. It works, but possibly there is a better solution.

Second problem, each day we are copying a new set of data into the next
blank column (F), then deleting Column C, so that we always have only 3
columns. Of course when Column C is deleted, the formula goes to REF#. I
did a work around by writing a small macro that re-inputs the formulas
into
Columns A & B. Is there any way to retain the formula?

Thanks for any help.
--
Linda


.

Is the data numeric or text?

And what exactly is meant when you say the word "compare"?

Are the cells being used as "checkboxes" and you look for them to have
an "x" or other non-blank character?


On Sun, 7 Mar 2010 08:52:01 -0800, L.Mathe
wrote:

The data could be anywhere in the other 2 columns (or may not). So I need to
find the data that matches (exists) in all 3 columns.

Second problem: What we have to do is sequentially pull in the previous
day's work for comparison. IE: Col C has the work from Mar. 1, Col. D is
Mar. 2, and Col. E is Mar. 3. So the following day I will copy the work of
Mar 4 into the next empty column, eliminate Mar 1, so I am working only with
Mar 2, 3 & 4th data, etc. The day after, it would be Mar 3, 4 & 5th, etc.

Hope this explains it better!

I may have used the wrong word when I wrote 'compare'. What I need to do is
find the numbers (19 digit number formatted as Text) that are in Column C, D
and E. If it is in all 3 columns then I need the number displayed (either in
another column, highlighted, or something) in order to do something with the
data.

I hope this helps to clarify.
--
Linda


"CellShocked" wrote:

Is the data numeric or text?

And what exactly is meant when you say the word "compare"?

Are the cells being used as "checkboxes" and you look for them to have
an "x" or other non-blank character?


On Sun, 7 Mar 2010 08:52:01 -0800, L.Mathe
wrote:

The data could be anywhere in the other 2 columns (or may not). So I need to
find the data that matches (exists) in all 3 columns.

Second problem: What we have to do is sequentially pull in the previous
day's work for comparison. IE: Col C has the work from Mar. 1, Col. D is
Mar. 2, and Col. E is Mar. 3. So the following day I will copy the work of
Mar 4 into the next empty column, eliminate Mar 1, so I am working only with
Mar 2, 3 & 4th data, etc. The day after, it would be Mar 3, 4 & 5th, etc.

Hope this explains it better!
.

In say, F2:
=IF(AND(COUNTA(C2:E2)=3,AND(C2=D2,C2=E2,D2=E2)),C2 ,"")
Copy down. Success? hit YES below
--
Max
Singapore
---
"L.Mathe" wrote:
.. find the numbers (19 digit number formatted as Text) that are in Column C, D
and E. If it is in all 3 columns then I need the number displayed (either in
another column, highlighted, or something) in order to do something with the
data