Trying to do this without VBA 1

[gadero]

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

 

[Feric]

Hi there:

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

 

[MintJulep]

Feric's suggestion is good, certainly a properly structured datatbase would be far superior than the train wreck you seem to have now.

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.

 

[yogia]

Hi gadero:

Following is a formula based solution ...

   |    A     |    B    |      C     |   D   |   E   |   F
---|----------|---------|------------|-------|-------|----
  1|MonthCompare				
  2|[b]column1	  column2      column3   data1   data2[/b]
  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

Columns A, B, and C house the unique set of records for Month1 and Month2 combined (use AdvancedFilter to extract Unique Records)

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

 

[MortenA]

A pivot table would not do excatly what you ask for - but close.

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