#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Match worksheet

I have 2 worksheets, the first one shows results from last month while the
second shows resutls for the current month. What I need to get the ability to
identify any changes between the 2 months i.e. add, change, del.

Nothe both worksheets have the same number of column but not the same number
of rows. Is there a way to achieve this match within excel, thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 915
Default Match worksheet

Bob F wrote:
I have 2 worksheets, the first one shows results from last month while the
second shows resutls for the current month. What I need to get the ability to
identify any changes between the 2 months i.e. add, change, del.

Nothe both worksheets have the same number of column but not the same number
of rows. Is there a way to achieve this match within excel, thanks


Try VLOOKUP. Without knowing the structure of your worksheets a specific
solution cannot be given, but in general...

Adds are detected by looking up the keys in the new month against the
keys in the old month. !N/A means you have a new entry.

Changes are detected by looking up the values in the new month against
the values in the old month and testing for equality. Note since !N/A
means a new entry, you can combine the add formula with the change formula.

Deletes are the converse of adds: Look up the keys in the old month
against the keys in the new. !N/A means this record was deleted.

Play with VLOOKUP for a while and see if you can get this working.
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Match worksheet

Thank you Martin, I have looked at the 'vlookup' help and I can't see an
example to match several columns between 2 sheets. The key for the item is
column A to C and the match is for column D to F.
I am including a sample of my worksheet and wonder if you could give me an
example of the vlookup formula to compare columns D to F for all rows.
Month 1
A B C
D E F
4440 Chemineaud ***** brandy 750 42 3 29
4945 St-Leger scotch blended 1140 41 0 34
387027 Château des Tuileries Bordeaux 750 26 1 13
10454 Melchers Maxi Dry dry gin 750 12 0 12

Month 2

4440 Chemineaud ***** brandy 750 42 3 0
new 1 750 1 2 3
4945 St-Leger scotch blended 1140 43 0 0
387027 Château des Tuileries Bordeaux 750 26 1 0
567891 Domaine La Hitaire 750 1 0 0

Thank you in advance





"smartin" wrote:

Bob F wrote:
I have 2 worksheets, the first one shows results from last month while the
second shows resutls for the current month. What I need to get the ability to
identify any changes between the 2 months i.e. add, change, del.

Nothe both worksheets have the same number of column but not the same number
of rows. Is there a way to achieve this match within excel, thanks


Try VLOOKUP. Without knowing the structure of your worksheets a specific
solution cannot be given, but in general...

Adds are detected by looking up the keys in the new month against the
keys in the old month. !N/A means you have a new entry.

Changes are detected by looking up the values in the new month against
the values in the old month and testing for equality. Note since !N/A
means a new entry, you can combine the add formula with the change formula.

Deletes are the converse of adds: Look up the keys in the old month
against the keys in the new. !N/A means this record was deleted.

Play with VLOOKUP for a while and see if you can get this working.

  #4   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Bob F View Post
Thank you Martin, I have looked at the 'vlookup' help and I can't see an
example to match several columns between 2 sheets. The key for the item is
column A to C and the match is for column D to F.
I am including a sample of my worksheet and wonder if you could give me an
example of the vlookup formula to compare columns D to F for all rows.
Month 1
A B C
D E F
4440 Chemineaud ***** brandy 750 42 3 29
4945 St-Leger scotch blended 1140 41 0 34
387027 Château des Tuileries Bordeaux 750 26 1 13
10454 Melchers Maxi Dry dry gin 750 12 0 12

Month 2

4440 Chemineaud ***** brandy 750 42 3 0
new 1 750 1 2 3
4945 St-Leger scotch blended 1140 43 0 0
387027 Château des Tuileries Bordeaux 750 26 1 0
567891 Domaine La Hitaire 750 1 0 0

Thank you in advance





"smartin" wrote:

Bob F wrote:
I have 2 worksheets, the first one shows results from last month while the
second shows resutls for the current month. What I need to get the ability to
identify any changes between the 2 months i.e. add, change, del.

Nothe both worksheets have the same number of column but not the same number
of rows. Is there a way to achieve this match within excel, thanks


Try VLOOKUP. Without knowing the structure of your worksheets a specific
solution cannot be given, but in general...

Adds are detected by looking up the keys in the new month against the
keys in the old month. !N/A means you have a new entry.

Changes are detected by looking up the values in the new month against
the values in the old month and testing for equality. Note since !N/A
means a new entry, you can combine the add formula with the change formula.

Deletes are the converse of adds: Look up the keys in the old month
against the keys in the new. !N/A means this record was deleted.

Play with VLOOKUP for a while and see if you can get this working.
You got good advice to use the VLookup formula. This is a pretty simple case. I would suggest writing the formulas within the worksheet that contains the current month data. It would seem that you are trying to calculate any variance between the months for columns C, D, E, and F, yes? If so then you will need one vlookup for each of those data. Perhaps in column G you can write the first vlookup which would be =vloookup(a1,"month 1 range a1:f100?,3,false). That will return the value from column C for the item listed in row 1 on the month 2 workhsheet, which you can compare to what's in cell a3. the formula for column H would be the same except for that the column reference would be 4, and so on. If you get an error then that means this particular item is not found in the month 1 worksheet.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Match to find value on worksheet Dkline Excel Worksheet Functions 3 March 4th 09 02:28 PM
Match() worksheet function WCM Excel Worksheet Functions 10 May 2nd 08 05:59 AM
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet insitedge Excel Worksheet Functions 2 March 12th 08 12:36 AM
Match WORKSHEET Aslam Excel Worksheet Functions 1 September 2nd 07 01:10 PM
Match 2 worksheet mohsin Excel Worksheet Functions 1 August 12th 06 01:46 PM


All times are GMT +1. The time now is 06:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"