Hi,

I am compiling a list every week with approxiamtely 7,000+ rows of data
and 4or 5 columns of data. I want to able to make comparisons of the
data but there is a problem. Each week the first column data will be
slightly different which will prevent me from simply staking the new
columns alphabetically next to the old columns to make the comparisons.
Here is a very simplified example of what I mean.

First weeks table:
apple 3 5 7
orange 2 6 9
pear 6 0 0

Seconds weeks table:
apple 1 9 9
peach 4 0 0
orange 5 5 5
pear 2 1 0

You see, I couldnt just stack the second weeks data in front of the
first weeks data and perform comparisons because peach is the 2nd entry
in week 2 and its not there in week 1. Is there a way I can have excel
do what I'm trying to do here?

Thanks!


--
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php...o&userid=29931
View this thread: http://www.excelforum.com/showthread...hreadid=496305

Perhaps one approach ..

Assuming the data is housed within cols A to D
in sheets named as: 1st, 2nd

The key col is assumed to be col A
and data starts from row1 down

In sheet: 2nd,

Put in say, E1:
=IF(ISNA(MATCH($A1,'1st'!$A:$A,0)),"",
VLOOKUP($A1,'1st'!$A:$D,COLUMN(B1),0))
Copy E1 across 3 cols to G1, fill down as far as required

This would extract over corresponding cols from sheet: 1st
into cols E to G for comparison.

Non matches, if any, would return blanks: ""
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"RalphSE" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Hi,
>
> I am compiling a list every week with approxiamtely 7,000+ rows of data
> and 4or 5 columns of data. I want to able to make comparisons of the
> data but there is a problem. Each week the first column data will be
> slightly different which will prevent me from simply staking the new
> columns alphabetically next to the old columns to make the comparisons.
> Here is a very simplified example of what I mean.
>
> First weeks table:
> apple 3 5 7
> orange 2 6 9
> pear 6 0 0
>
> Seconds weeks table:
> apple 1 9 9
> peach 4 0 0
> orange 5 5 5
> pear 2 1 0
>
> You see, I couldnt just stack the second weeks data in front of the
> first weeks data and perform comparisons because peach is the 2nd entry
> in week 2 and its not there in week 1. Is there a way I can have excel
> do what I'm trying to do here?
>
> Thanks!
>
>
> --
> RalphSE
> ------------------------------------------------------------------------
> RalphSE's Profile:
http://www.excelforum.com/member.php...o&userid=29931
> View this thread: http://www.excelforum.com/showthread...hreadid=496305
>

Hi Max,

Thanks so much for your help although I must confess you are waaay ove
my head with that formula. Would it be possible for me to email you th
file? or post it to this message board somehow so you can show me ho
you intend to apply that formula?

Thank

--
RalphS
-----------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php...fo&userid=2993
View this thread: http://www.excelforum.com/showthread.php?threadid=49630

Here's a sample construct:
http://www.savefile.com/files/6583201
Automatically_Comparing_Tables_with_Different_Entries_RalphSE_gen.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"RalphSE" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Hi Max,
>
> Thanks so much for your help although I must confess you are waaay over
> my head with that formula. Would it be possible for me to email you the
> file? or post it to this message board somehow so you can show me how
> you intend to apply that formula?
>
> Thanks

Max, gosh, very kind of you to create that file but I apologize, it is
waaay over my head still, I wish I could post the file somehow, the
actual file I'm using has columns A thru G, A is the key column as you
assumed, but there are columns B thru G to compare and approximately
7,500 rows in each table, I wouldnt know how to adapt your formula to
accomodate this? Is there a way I can post the file somehow?


--
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php...o&userid=29931
View this thread: http://www.excelforum.com/showthread...hreadid=496305

One way is to upload a small sample copy of your file (zipped) via a free
filehost, and then include a *link* to it in your response here.

Some free filehosts that could be used:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

For cjoint.com (it's in French), just click "Browse" button, navigate to
folder > select the file > Open, then click the button centred in the page
below (labelled "Creer le lien Cjoint") and it'll generate the link. Then
copy & paste the generated link as part and parcel of your response.

Pl note that no attachments should be posted *direct* to the newsgroup
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"RalphSE" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Max, gosh, very kind of you to create that file but I apologize, it is
> waaay over my head still, I wish I could post the file somehow, the
> actual file I'm using has columns A thru G, A is the key column as you
> assumed, but there are columns B thru G to compare and approximately
> 7,500 rows in each table, I wouldnt know how to adapt your formula to
> accomodate this? Is there a way I can post the file somehow?

OK MAX! I really appreciate your help, here is the file
http://www.flypicture.com?display=updone&id=rdD3mqXQ.

I deleted rows 801 thru 7,500 to make the file smaller but I will nee
a formula that will cover at least 7,500 rows of data please. So th
idea here is that I want to be able to compare data in columns B thru
for each item in column A between sheet "122805" and "122505", th
formula will need to lookup the matches as they are not in exactly th
same order.

THANKS!!!!!

p.s. i dont need to compare columns G as they will be the sam

--
RalphS
-----------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php...fo&userid=2993
View this thread: http://www.excelforum.com/showthread.php?threadid=49630

Here's a sample implementation at:
http://www.savefile.com/files/9526722
Automatically_Comparing_Tables_with_Different_Entries_RalphSE_2.zip

In sheet: 122505

Put in H1:
=IF(ISNA(MATCH($A1,'122805'!$A:$A,0)),"",
VLOOKUP($A1,'122805'!$A:$G,COLUMN(B1),0))
Copy H1 across to M1, fill down as far as required

Cols H to M will match & return (line up) the extracts from cols B to G in
sheet: 122805 according to the symbols in col A for easy side-by-side
comparison. Unmatched cases will return blanks: "".

Enter a label into N1: Identical?

Put in N2:
=IF(AND(B2=H2,C2=I2,D2=J2,E2=K2,F2=L2),"Yes","No")
Copy N2 down

Col N will return "Yes" if the data in cols B to F match exactly with those
extracted in cols H to L (the corresponding cols B to F in the other sheet).
Unmatched lines will return: "No". You can then do an autofilter on N1 and
filter out the "No" cases for closer examination, etc.

Similarly ..

In sheet: 122805

Put in H1:
=IF(ISNA(MATCH($A1,'122505'!$A:$A,0)),"",
VLOOKUP($A1,'122505'!$A:$G,COLUMN(B1),0))
Copy H1 across to M1, fill down as far as required

Cols H to M will match & return (line up) the extracts from cols B to G in
sheet: 122505 according to the symbols in col A for easy side-by-side
comparison. Unmatched cases will return blanks: "".

Enter a label into N1: Identical?

Put in N2:
=IF(AND(B2=H2,C2=I2,D2=J2,E2=K2,F2=L2),"Yes","No")
Copy N2 down

Col N will return "Yes" if the data in cols B to F match exactly with those
extracted in cols H to L (the corresponding cols B to F in the other sheet).
Unmatched lines will return: "No". You can then do an autofilter on N1 and
filter out the "No" cases for closer examination, etc.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"RalphSE" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> OK MAX! I really appreciate your help, here is the file ~
> http://www.flypicture.com?display=updone&id=rdD3mqXQ.
>
> I deleted rows 801 thru 7,500 to make the file smaller but I will need
> a formula that will cover at least 7,500 rows of data please. So the
> idea here is that I want to be able to compare data in columns B thru F
> for each item in column A between sheet "122805" and "122505", the
> formula will need to lookup the matches as they are not in exactly the
> same order.
>
> THANKS!!!!!
>
> p.s. i dont need to compare columns G as they will be the same
>
>
> --
> RalphSE
> ------------------------------------------------------------------------
> RalphSE's Profile:
http://www.excelforum.com/member.php...o&userid=29931
> View this thread: http://www.excelforum.com/showthread...hreadid=496305
>

Max, thank you so much but the formulas only work on my spreadhseet down
to row 800, I copied them all the way down to row 7,038 which is the
extent of my data yet there are only blank cells from 801 on down, for
some reason your formula is only working to the row of data that I sent
you, how can I fix that?


--
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php...o&userid=29931
View this thread: http://www.excelforum.com/showthread...hreadid=496305

oops, my mistake, it worked when I copied the formula directly from you
note instead of from your worksheet, works great, excellent job, than
you VERY MUCH!! :

--
RalphS
-----------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php...fo&userid=2993
View this thread: http://www.excelforum.com/showthread.php?threadid=49630