Comparing two sets of data

[Eric G]

I have a sheet with 600 records (LastName, FirstName, Col3, Col4,
Col5,Col6).
My second sheet has 670 records with the same field headings but the
data is different in Col3, Col4, Col5, Col6 for ALL the records.

Can someone please tell me how I can sort Sheet Two so that the first
600 records are arranged in the same order as Sheet One's (Last Name,
FirstName sort) ?

I hope this makes sense.

Thanks! Eric

 

[Max]

Perhaps this might help ..

Assume that the set-up you have in Sheets 1 and 2 are identical,
i.e. Lastname & Firstname in cols A and B, then Col3 to Col6's data
in col C to F, with data in row2 down
(The names are assumed unique in cols A and B)

In Sheet2
-----------
In say, col G
Put in G2: =TRIM(A2&" "&B2)
Copy G2 down as many rows as there is data

In Sheet1
-----------
Put in G2:

=IF(ISNA(MATCH(TRIM($A3&"
"&$B3),Sheet2!$G:$G,0)),"",OFFSET(Sheet2!$A$1,MATCH(TRIM($A3&"
"&$B3),Sheet2!$G:$G,0)-1,COLUMN()-5))

Copy G2 across to J2, then copy down as many rows as there is data

In cols G to J will be the corresponding data for the matched Lastname &
Firstname
extracted from Col3 to Col6 of Sheet2

Unmatched names will return blanks [""]

 

[Max]

Sorry, correction to formula in G2 of Sheet1
(the earlier one given was actually for G3)

Put instead in G2 of Sheet1:

=IF(ISNA(MATCH(TRIM($A2&"
"&$B2),Sheet2!$G:$G,0)),"",OFFSET(Sheet2!$A$1,MATCH(TRIM($A2&"
"&$B2),Sheet2!$G:$G,0)-1,COLUMN()-5))

 

[Eric G]

Sorry, correction to formula in G2 of Sheet1

(the earlier one given was actually for G3)

Put instead in G2 of Sheet1:

=IF(ISNA(MATCH(TRIM($A2&"
"&$B2),Sheet2!$G:$G,0)),"",OFFSET(Sheet2!$A$1,MATCH(TRIM($A2&"
"&$B2),Sheet2!$G:$G,0)-1,COLUMN()-5))

HI Max,

Thanks for helping out, I really appreciate it.
Right now not much is happening in the sheets after I input the
formulas.
Sheet2 trims the names properly into column G.
Sheet1 starts calculating and then asks where Sheet2 is (pops up the
File folder box). I hit Esc/cancel and then calculating resumes but I
don't see a result.

Let me clarify what I have/need:

Sheet1

LAST FIRST P1 P2 P3 P4
Aasman Jesse 106D 102P 101D 103D
Abel Matthew102D 104P 106D 102C
Addis Matthew 107D
Agar Rebecca103A 101B 105B 107D
Ahmad Cassy 101B 105B 102C 108P
Aitken Ashley 106P 107A 103M 105B
Allen Stephanie101C 101M 104C 105C
Allen Stewart 109P 103C 104M 106C

Sheet2 (note all rooms have changed)

LAST FIRST P1 P2 P3 P4
Aasman Jesse 101D 103P 101D 104D
Abel Matthew104D 104P 106D 101C
Addis Matthew 107D
Adman NEW** 101P 103B <----New stu
Agar Rebecca102A 104B 103B 106D
Ahmad Cassy 102B 103B 102D 108P
Aitken Ashley 105P 103A 102M 105B
Allen NEW** 102B 104P 104D <---- New stu
Allen Stephanie102D 101C 104C 105C
Allen Stewart 102P 104C 104M 106D

I need Sheet2 sorted in alpha but with all the NEW student records
moved to the very bottom of the sheet (also in alpha).

I could put Sheet2 data onto Sheet1 if it would make referencing
easier? I have Excel 2000.

Eric

 

[Eric G]

Hi Max,

Sorry I forgot to mention one other important things which will throw
a monkey wrench into our calculations.

The new list (Sheet2) has two things done to it.
1. New students have been added to it and the list sorted alpha.
2. Some 50+ students have been deleted.

So.... I need placeholders for those students who have been deleted.
The placeholders can be something like "Deleted" for each deleted
record.
I'm hoping there's a way to do this.

Thanks! Eric

 

[Max]

Hi Eric,

Try this revised set-up

In Sheet1
-----------
Put in G2: =TRIM(A2&" "&B2)
Copy G2 down as many rows as there is data

In Sheet2
-----------
Put in G2:

=IF(ISNA(MATCH(TRIM($A2&"
"&$B2),Sheet1!$G:$G,0)),ROW()+2000,MATCH(TRIM($A2&"
"&$B2),Sheet1!$G:$G,0)-1)

Copy G2 down as many rows as there is data

Note: The "2000" in ROW()+2000 is just an arbitrary number,
i.e. a number larger than the max number of students in the list in Sheet1
Adjust this number "2000" higher if necessary

In a new Sheet3
-------------------
Put in A2:

=OFFSET(Sheet2!$A$1,MATCH(SMALL(Sheet2!$G:$G,ROW()-1),Sheet2!$G:$G,0)-1,COLU
MN()-1)

Copy A2 across to F2, then copy down until #NUM! appears

Cols A to F in Sheet3 will give you the sort you want for Sheet2
i.e. according to the list as in Sheet1,
but with the new students added at the bottom

For a cleaner look in Sheet3
----------------------------------
Suppress zeros from showing via:
Tools > Options > View Tab > Uncheck "Zero values" > OK

------

To show the students deleted since in Sheet1
(i.e those names in Sheet1 which do not appear in Sheet2) ,
try this additional set-up

In Sheet2
-----------
Put in H2: =TRIM(A2&" "&B2)
Copy H2 down as many rows as there is data

In Sheet1
-----------
Put in H2:

=IF(ISNA(MATCH(TRIM($A2&" "&$B2),Sheet2!$H:$H,0)),"Deleted","")

Copy H2 down as many rows as there is data

 

[Richard Buttrey]

Hi Max,

Sorry I forgot to mention one other important things which will throw
a monkey wrench into our calculations.

The new list (Sheet2) has two things done to it.
1. New students have been added to it and the list sorted alpha.
2. Some 50+ students have been deleted.

So.... I need placeholders for those students who have been deleted.
The placeholders can be something like "Deleted" for each deleted
record.
I'm hoping there's a way to do this.

Thanks! Eric

The way I'd approach this is as follows.

1. On both sheets add a new temporary column (Col G).
Use Col G to concatenate the last and first names.
i.e. =A1&B1

2. On sheet 1, add another temporary column H1 and use a vlookup
formula to identify the records that have been already deleted on
sheet 2

i.e. H1

=IF(ISERROR(VLOOKUP(G1,Sheet2!$G$1:$G$670,1,FALSE)),"Deleted",VLOOKUP(G1,Sheet2!$G$1:$G$670,1,FALSE))

Copy this down all 600 rows.

3. Use a similar techinique on Sheet2 to identify all the New
Students. i.e. in H1 enter

=IF(ISERROR(VLOOKUP(G1,Sheet1!$G$1:$G$600,1,FALSE)),"New",VLOOKUP(G1,Sheet1!$G$1:$G$600,1,FALSE))

4. Filter Sheet 2 on Col H to list all the "New' Records, then copy
the word "ZZZNew" down Col C against all the filtered records. and
remove the fiter.

5. Filter sheet 1 Col H to list all the "Deleted" rows, copy the
names from Sheet 1 Col A:B to Sheet 2 Col A:B underneath the last
record. (i.e.to row A671) and then copy the word "Deleted" into C671
and copy down as far as necessary.

6. Now Sort the whole of Sheet 2 on Column C (first sort) and Col G
(second sort). This will put all the New students at the bottom in
alpha order.

7. Now sort all the Sheet 2 records apart from those you've just
sorted in step 6, using Col G as the sort.

8. Finally delete your temporary columns G & H

Takes longer to write this than do it, but I'll be interested to know
if there are other quicker/elegant methods, since this is a general
technique I seem to find myself using fairly regularly.

Rgds

Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

The way I'd approach this is as follows.

1. On both sheets add a new temporary column (Col G).
Use Col G to concatenate the last and first names.
i.e. =A1&B1

2. On sheet 1, add another temporary column H1 and use a vlookup
formula to identify the records that have been already deleted on
sheet 2

i.e. H1

=IF(ISERROR(VLOOKUP(G1,Sheet2!$G$1:$G$670,1,FALSE)),"Deleted",VLOOKUP(G1,Sheet2!$G$1:$G$670,1,FALSE))

Copy this down all 600 rows.

3. Use a similar techinique on Sheet2 to identify all the New
Students. i.e. in H1 enter

=IF(ISERROR(VLOOKUP(G1,Sheet1!$G$1:$G$600,1,FALSE)),"New",VLOOKUP(G1,Sheet1!$G$1:$G$600,1,FALSE))

4. Filter Sheet 2 on Col H to list all the "New' Records, then copy
the word "ZZZNew" down Col C against all the filtered records. and
remove the fiter.

5. Filter sheet 1 Col H to list all the "Deleted" rows, copy the
names from Sheet 1 Col A:B to Sheet 2 Col A:B underneath the last
record. (i.e.to row A671) and then copy the word "Deleted" into C671
and copy down as far as necessary.

6. Now Sort the whole of Sheet 2 on Column C (first sort) and Col G
(second sort). This will put all the New students at the bottom in
alpha order.

7. Now sort all the Sheet 2 records apart from those you've just
sorted in step 6, using Col G as the sort.

8. Finally delete your temporary columns G & H

Takes longer to write this than do it, but I'll be interested to know
if there are other quicker/elegant methods, since this is a general
technique I seem to find myself using fairly regularly.

Oops - spoke too soon - a correction......

Step 3 should have "ZZZNew" instead of just "New" in the formula.

Step 4 should be ignored.

Step 5 should also copy the concatenated names from Sheet 1 Col G to
Sheet 2 Col G

Step 6 reference to First sort should be Col H (placing all new
students at the bottom), with second sort on Col G to put them in
alpha order

Step 7 should sort all records apart from those with "ZZZNew" in Col H


Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Eric G]

Hi Max,

Very nice! Thanks very much.
Everything that you coded for works nicely now.

There's just one thing missing. I don't know if you understood me
correctly.

For the records that were deleted from Sheet1, I need placeholders in
Sheet3 sort of like this:

.....
Arsenault Michel 104B 104P 105B 102C
Astins Melissa 104B 101C 103C 101A
Atkins Tylor 109A 103M 101M 108P
DELETED DELETED
Ayles Stephen 108P 109P 105B 102P
Allen** New 105B 106P 109P 102A
Anderson** New 105A 105A 102D 106P
Arsenault** New 102A 101M 102D 103C

So if I've deleted Attwell Alex from Sheet1, Sheet3 should show the
above. Preferably DELETED in Col1 and Col2 as above, as I believe
these two fields need to be filled in.

I hope this last step isn't too difficult.

Thanks! Eric

 

[Eric G]

Hi Richard,

Thanks for helping out!
It looks like you have a very elegant way of tackling this problem.
I'm going to try it out in the next few days and get back to the
newsgroup.

Cheers! Eric

 

[Max]

Eric, you're welcome !
Thanks for the feedback.

I hope this last step isn't too difficult.

Unfortunately, it's proving to be so ..

Try as I did, guess I'm out of further suggestions
to offer you on the deleted records part.
Not sure if an integrated formula solution is possible here.

Perhaps others would step-in to offer you other alternatives.
Maybe a sub could be fashioned to take care of
the comparison / insertion of the deleted records ..

Good luck !

 

[Eric G]

Thanks for all your help anyhow Max. I couldn't have done it myself.
It looks like Richard may have a solution. I'll try it out in the next
day or so.

Eric

 

[Max]

Pleased to help, Eric.
All the best !