If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VLookup
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 |
#2
|
|||
|
|||
VLookup
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 |
#3
|
|||
|
|||
VLookup
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 . |
#4
|
|||
|
|||
VLookup
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! |
#5
|
|||
|
|||
VLookup
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! . |
#6
|
|||
|
|||
VLookup
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 |
#7
|
|||
|
|||
VLookup
Sorry, this didn't work. What I have to accomplish is to find if what is in
Col. C is in D & E, so that I have a match in all 3 columns. The data could be in any row from 2 to approximatley 500. I think it would need some kind of array formula which I am not familiar with. Thanks for your input. I find this forum is has so many helpful people and so many great suggestions! -- Linda "Max" wrote: 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 |
#8
|
|||
|
|||
VLookup
That was a guess. Think its best that you illustrate your query/scenario.
Paste some sample data before/after, show what you have, explain what you want to make happen. -- Max Singapore --- "L.Mathe" wrote: Sorry, this didn't work. What I have to accomplish is to find if what is in Col. C is in D & E, so that I have a match in all 3 columns. The data could be in any row from 2 to approximatley 500. I think it would need some kind of array formula which I am not familiar with. Thanks for your input. I find this forum is has so many helpful people and so many great suggestions! |
#9
|
|||
|
|||
VLookup
Hi Max,
A sample of the WS I have: Col. C Col. D Col. E 4-Mar-10 5-Mar-10 6-Mar-10 6333880064104573811 6333880064111021143 6333880064143241511 6333880064109655852 6333880064132072661 6333880064125485573 6333880064143241511 6333880064132072661 6333880064147798581 6333880064147798581 6333880064143241511 6333880064206960742 There could be as many as 500 rows of data per in column, but will vary in the number of rows. Using the numbers in Col. C, I need to see if it exists in BOTH Col. D & E. If the number is in all 3 columns, I need it either hightlighted, displayed in another column, or something to reference the number. In my original submission to this forum, as stated, what I am doing right now is, in Col. A I have VLookup formula (=VLOOKUP(Sheet1!C3,Sheet1!D$2$1000,1,FALSE) that gives me the numbers that match in Col. C & D. In Col. B, VLookup (=VLOOKUP(Sheet1!A3,Sheet1!E$2:E$1000,1,FALSE) gives me a match of Col. A & D. So the end results would look like: Col. A Col. B #N/A #N/A #N/A #N/A 6333880064143241511 6333880064143241511 #N/A #N/A I hope this helps. But, please don't feel a solution must be found, as what I have works right now. I thought there might be a better (easier) way of accomplishing the same. I am relatively new to Excel (about 2 years into it), and always looking to improve what I have. Thanks! -- Linda -- Linda "Max" wrote: That was a guess. Think its best that you illustrate your query/scenario. Paste some sample data before/after, show what you have, explain what you want to make happen. -- Max Singapore --- "L.Mathe" wrote: Sorry, this didn't work. What I have to accomplish is to find if what is in Col. C is in D & E, so that I have a match in all 3 columns. The data could be in any row from 2 to approximatley 500. I think it would need some kind of array formula which I am not familiar with. Thanks for your input. I find this forum is has so many helpful people and so many great suggestions! |
#10
|
|||
|
|||
VLookup
Try
=IF(AND(ISNUMBER(MATCH(C3,D,0)),ISNUMBER(MATCH(C 3,E:E,0))),C3,"") -- HTH Bob "L.Mathe" wrote in message ... Hi Max, A sample of the WS I have: Col. C Col. D Col. E 4-Mar-10 5-Mar-10 6-Mar-10 6333880064104573811 6333880064111021143 6333880064143241511 6333880064109655852 6333880064132072661 6333880064125485573 6333880064143241511 6333880064132072661 6333880064147798581 6333880064147798581 6333880064143241511 6333880064206960742 There could be as many as 500 rows of data per in column, but will vary in the number of rows. Using the numbers in Col. C, I need to see if it exists in BOTH Col. D & E. If the number is in all 3 columns, I need it either hightlighted, displayed in another column, or something to reference the number. In my original submission to this forum, as stated, what I am doing right now is, in Col. A I have VLookup formula (=VLOOKUP(Sheet1!C3,Sheet1!D$2$1000,1,FALSE) that gives me the numbers that match in Col. C & D. In Col. B, VLookup (=VLOOKUP(Sheet1!A3,Sheet1!E$2:E$1000,1,FALSE) gives me a match of Col. A & D. So the end results would look like: Col. A Col. B #N/A #N/A #N/A #N/A 6333880064143241511 6333880064143241511 #N/A #N/A I hope this helps. But, please don't feel a solution must be found, as what I have works right now. I thought there might be a better (easier) way of accomplishing the same. I am relatively new to Excel (about 2 years into it), and always looking to improve what I have. Thanks! -- Linda -- Linda "Max" wrote: That was a guess. Think its best that you illustrate your query/scenario. Paste some sample data before/after, show what you have, explain what you want to make happen. -- Max Singapore --- "L.Mathe" wrote: Sorry, this didn't work. What I have to accomplish is to find if what is in Col. C is in D & E, so that I have a match in all 3 columns. The data could be in any row from 2 to approximatley 500. I think it would need some kind of array formula which I am not familiar with. Thanks for your input. I find this forum is has so many helpful people and so many great suggestions! |
|
Thread Tools | |
Display Modes | |
|
|