Trying to do this without VBA
Trying to do this without VBA
(OP)
Never written VBA code and feel a bit intimidated. May be that this is a good time to start??
So, here's what I'm trying to do. I have two worksheets full of data. The worksheets are updated monthly with new rows added anywhere in the table. The data is in a random order. The goal is to compare information in a specific column from one month to the next. Unfortunately there isn't any single column that I can use as a refernce. I must compare at least 3 columns so that I know that I'm comparing the correct informtion. I think it's easier to explain with an example:
Month 1
column 1 column 2 column 3 data 1
Bill 1/2/07 1/23/07 5
Bill 1/2/07 1/13/07 6
Bill 1/5/07 1/23/07 7
Bill 1/18/07 1/13/07 8
John 1/18/07 1/13/07 8
Ted 1/18/07 1/13/07 8
John 1/18/07 1/16/07 8
Month 2
column 1 column 2 column 3 data 2
Bill 1/2/07 1/13/07 16
Jenny 1/2/07 1/13/07 16
Bill 1/2/07 1/23/07 11
Bill 1/18/07 1/13/07 18
John 1/18/07 1/13/07 18
Ted 1/18/07 1/13/07 18
Bill 1/5/07 1/23/07 17
John 1/18/07 1/16/07 8
What I'm trying to do:
If information in column 1, 2 and 3 are identical, I'd like to compare the values in column 4.
I'd get:
column 1 column 2 column 3 data 1 data 2
Bill 1/2/07 1/23/07 5 11
Bill 1/2/07 1/13/07 6 16
Bill 1/5/07 1/23/07 7 17
Bill 1/18/07 1/13/07 8 18
John 1/18/07 1/13/07 8 18
Ted 1/18/07 1/13/07 8 18
John 1/18/07 1/16/07 8 8
Jenny 1/2/07 1/13/07 16 new
Any thoughts would be appreciated.
Thanks
So, here's what I'm trying to do. I have two worksheets full of data. The worksheets are updated monthly with new rows added anywhere in the table. The data is in a random order. The goal is to compare information in a specific column from one month to the next. Unfortunately there isn't any single column that I can use as a refernce. I must compare at least 3 columns so that I know that I'm comparing the correct informtion. I think it's easier to explain with an example:
Month 1
column 1 column 2 column 3 data 1
Bill 1/2/07 1/23/07 5
Bill 1/2/07 1/13/07 6
Bill 1/5/07 1/23/07 7
Bill 1/18/07 1/13/07 8
John 1/18/07 1/13/07 8
Ted 1/18/07 1/13/07 8
John 1/18/07 1/16/07 8
Month 2
column 1 column 2 column 3 data 2
Bill 1/2/07 1/13/07 16
Jenny 1/2/07 1/13/07 16
Bill 1/2/07 1/23/07 11
Bill 1/18/07 1/13/07 18
John 1/18/07 1/13/07 18
Ted 1/18/07 1/13/07 18
Bill 1/5/07 1/23/07 17
John 1/18/07 1/16/07 8
What I'm trying to do:
If information in column 1, 2 and 3 are identical, I'd like to compare the values in column 4.
I'd get:
column 1 column 2 column 3 data 1 data 2
Bill 1/2/07 1/23/07 5 11
Bill 1/2/07 1/13/07 6 16
Bill 1/5/07 1/23/07 7 17
Bill 1/18/07 1/13/07 8 18
John 1/18/07 1/13/07 8 18
Ted 1/18/07 1/13/07 8 18
John 1/18/07 1/16/07 8 8
Jenny 1/2/07 1/13/07 16 new
Any thoughts would be appreciated.
Thanks





RE: Trying to do this without VBA
I do believe that this is a good problem for using and applying SQL.
If you have a copy of MS Office Suite on your PC, use MS Access.
Thanks,
Gordan Feric, PE
Engineering Software
http://members.aol.com/engware
RE: Trying to do this without VBA
If you are forced to attempt this in Excel, look into data tables and MS Query.
A pivot table, properly layed out, might also be useful.
RE: Trying to do this without VBA
Hi gadero:
Following is a formula based solution ...
CODE
---|----------|---------|------------|-------|-------|----
1|MonthCompare
2|column1 column2 column3 data1 data2
3|Bill 1/2/2007 1/13/2007 6 16
4|Bill 1/2/2007 1/23/2007 5 11
5|Bill 1/5/2007 1/23/2007 7 17
6|Bill 1/18/2007 1/13/2007 8 18
7|Jenny 1/2/2007 1/13/2007 16 New
8|John 1/18/2007 1/13/2007 8 18
9|John 1/18/2007 1/16/2007 8 8
10|Ted 1/18/2007 1/13/2007 8 18
then array formula in cell D3 is ...
=IF(ISNUMBER(MATCH(A3&B3&C3,Month1!$A$3:$A$10&Month1!$B$3:$B$10&Month1!$C$3:$C$10,0)),INDEX(Month1!$D$3:$D$10,MATCH(A3&B3&C3,Month1!$A$3:$A$10&Month1!$B$3:$B$10&Month1!$C$3:$C$10,0)),"")
and array formula in cell E3 is ...
=INDEX(Month2!$D$3:$D$10,MATCH(A3&B3&C3,Month2!$A$3:$A$10&Month2!$B$3:$B$10&Month2!$C$3:$C$10,0))&IF(D3=""," New","")
these are then copied down.
I hope this helps.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Trying to do this without VBA
It will make a table such as the one below where you could get each combination of collum 2/3 for each person (collum 1) but it will summarize for collum2/3 combinations - not print each occurance seperately:
Sum of data 1 column 3
column 1 column 2 1/13/2007 1/16/2007 1/23/2007 Grand Total
Bill 1/2/2007 22 16 38
1/5/2007 24 24
1/18/2007 26 26
Bill Total 48 40 88
Jenny 1/2/2007 16 16
Jenny Total 16 16
John 1/18/2007 26 16 42
John Total 26 16 42
Ted 1/18/2007 26 26
Ted Total 26 26
Grand Total 116 16 40 172