×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Trying to do this without VBA

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

RE: Trying to do this without VBA

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

RE: Trying to do this without VBA

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.

RE: Trying to do this without VBA


Hi gadero:

Following is a formula based solution ...

CODE

   |    A     |    B    |      C     |   D   |   E   |   F
---|----------|---------|------------|-------|-------|----
  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
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

RE: Trying to do this without VBA

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources