Hi,

I have the following type of data that I am trying to sort so that blank
cells are kept in their proper relationship to data.

A B C D E F G H

LName FName IDNum Amt LName FName IDNum Amt
Allen Art 12345 100 Allen Art 12345 100
Bell Ben 23456 200 Bell Ben 23456 200
Davis Dan 34557 275 Corn Cal 67891 75
Allen Ann 12345 325 Ball Bob 77793 100
Elon Ed 89012 125 Gill Gay 90876 400
Ford Hal 11456 100
Gill Gay 90876 600

The resulting sort would be:

A B C D E F G H
LName FName IDNum Amt LName FName IDNum Amt
Allen Ann 12345 325
Allen Art 12345 100 Allen Art 12345 100
Ball Bob 77793 100
Bell Ben 23456 200 Bell Ben 23456 200
Corn Cal 67891 75
Davis Dan 34557 275
Elon Ed 89012 125
Ford Hal 11456 100
Gill Gay 90876 600 Gill Gay 90876 400

The goal after the sort is to use the EXACT function see if the Amt columns
(D and H) match for each person.

Perhaps from the above examples and explantion, there may be a better way to
accomplish this task.

Thanks in advance for any and all help.

Try Conditional formatting to higlight mismatches without sorting:

On column H

=$H2<INDEX($D$2:$D$8,MATCH(1,($E2=$A$2:$A$8)*($F2 =$B$2:$B$8),0))

set colour (pattern)

"Robert M" wrote:

Hi,

I have the following type of data that I am trying to sort so that blank
cells are kept in their proper relationship to data.

A B C D E F G H

LName FName IDNum Amt LName FName IDNum Amt
Allen Art 12345 100 Allen Art 12345 100
Bell Ben 23456 200 Bell Ben 23456 200
Davis Dan 34557 275 Corn Cal 67891 75
Allen Ann 12345 325 Ball Bob 77793 100
Elon Ed 89012 125 Gill Gay 90876 400
Ford Hal 11456 100
Gill Gay 90876 600

The resulting sort would be:

A B C D E F G H
LName FName IDNum Amt LName FName IDNum Amt
Allen Ann 12345 325
Allen Art 12345 100 Allen Art 12345 100
Ball Bob 77793 100
Bell Ben 23456 200 Bell Ben 23456 200
Corn Cal 67891 75
Davis Dan 34557 275
Elon Ed 89012 125
Ford Hal 11456 100
Gill Gay 90876 600 Gill Gay 90876 400

The goal after the sort is to use the EXACT function see if the Amt columns
(D and H) match for each person.

Perhaps from the above examples and explantion, there may be a better way to
accomplish this task.

Thanks in advance for any and all help.

If you were only going to use your eyeballs to look for discrepancies, then
lining up your data may be the way to go.

But as long as you're going to use excel, you won't really have to care about
where the data is to find those discrepancies.

In fact, I'd put each table A:D and E:H on separate sheets--just to make it a
bit easier to work with (I think).

Then in E2 (headers in row 1), I'd put this:

=vlookup(a2,sheet2!a:d,4,false)
You'll either get an #n/a if there is no match between the last names--or you'll
get the value from the first match between last names.

Then you can use this in F2:
=if(iserror(e2),"No matching name in Sheet2",if(d2=e2,"Same","Different")

And drag both formulas down the columns.

And do the same in the other sheet.

========
Yes, this depends on unique last names in each list.

And you could have used additional columns in the first worksheet. But (to me),
that gets cluttered fast.

And besides, I may want to use data|filter|autofilter to see different results
of the comparison. And since entire rows are hidden, it may hide stuff I don't
want.




Robert M wrote:

Hi,

I have the following type of data that I am trying to sort so that blank
cells are kept in their proper relationship to data.

A B C D E F G H

LName FName IDNum Amt LName FName IDNum Amt
Allen Art 12345 100 Allen Art 12345 100
Bell Ben 23456 200 Bell Ben 23456 200
Davis Dan 34557 275 Corn Cal 67891 75
Allen Ann 12345 325 Ball Bob 77793 100
Elon Ed 89012 125 Gill Gay 90876 400
Ford Hal 11456 100
Gill Gay 90876 600

The resulting sort would be:

A B C D E F G H
LName FName IDNum Amt LName FName IDNum Amt
Allen Ann 12345 325
Allen Art 12345 100 Allen Art 12345 100
Ball Bob 77793 100
Bell Ben 23456 200 Bell Ben 23456 200
Corn Cal 67891 75
Davis Dan 34557 275
Elon Ed 89012 125
Ford Hal 11456 100
Gill Gay 90876 600 Gill Gay 90876 400

The goal after the sort is to use the EXACT function see if the Amt columns
(D and H) match for each person.

Perhaps from the above examples and explantion, there may be a better way to
accomplish this task.

Thanks in advance for any and all help.

--

Dave Peterson