Automatically Comparing Tables with Different Entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RalphSE - 28 Dec 2005 00:22 GMT

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!

Signature

RalphSE

Reply to this Message

Max - 28 Dec 2005 01:29 GMT

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
--

> Hi,
>
[quoted text clipped - 28 lines]
> RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
> View this thread: http://www.excelforum.com/showthread.php?threadid=496305

Reply to this Message

RalphSE - 28 Dec 2005 16:11 GMT

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

Reply to this Message

Max - 28 Dec 2005 16:27 GMT

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
--

> Hi Max,
>
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

RalphSE - 28 Dec 2005 16:51 GMT

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?

Signature

RalphSE

Reply to this Message

Max - 28 Dec 2005 17:10 GMT

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
--

> 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?

Reply to this Message

RalphSE - 28 Dec 2005 17:52 GMT

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

Reply to this Message

Max - 29 Dec 2005 02:11 GMT

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
--

> OK MAX! I really appreciate your help, here is the file ~
> http://www.flypicture.com?display=updone&id=rdD3mqXQ.
[quoted text clipped - 15 lines]
> RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
> View this thread: http://www.excelforum.com/showthread.php?threadid=496305

Reply to this Message

RalphSE - 29 Dec 2005 15:20 GMT

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?

Signature

RalphSE

Reply to this Message

RalphSE - 29 Dec 2005 15:38 GMT

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!!:) :) :) :) :

Reply to this Message

Max - 29 Dec 2005 22:04 GMT

Glad you got it working there !
Thanks for feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Reply to this Message