Shortening a list
 

Is there a way to combine functions to eliminate rows from a list based on
criteria?
I have column C and D which have 500 rows of data. In about 10% of the rows
the data in C does not match the data in D. I want to reproduce the list in
sheet 2 but only disply the rows that have non-matching data:

Sheet 1 Sheet 2
Col C Col D Becomes Col C Col D
12 12 tom david
tom david 8 0
3 3 table chair
8 0
jim jim
table chair

I know I could use a data column to compare the columns and then use vlookup
to get the next non-matching row, but I'm looking for a way to do it with
just formulas in sheet 2 and now have to create a compare column.

Thanks in advance for any assistance.
ed

Shortening a list
 

Herr is the easy way

1) Copy sheet 1 to sheet 2
2) Add an index number in column E. Put 1 in E1, put 2 in E2 and pull down
sheet to get a column of sequential numbers. I'm going to sort and this is
so we can get the order back to the original order when done.
3) put tis formula in cell F1 =C1=D1 This will put either True or
False in colun F
4) Select column F and Copy. then use PasteSpecial with Value selected to
convert formula to Values
5) Select data to be sorted. then go to menu Data - Sort and select column
F as only key
6) Delete Rows with TRUE.
7) sort again using column E to return the data to its original order.

This seems like a lot of steps but it is very quick.

"expect_ed" wrote:

Is there a way to combine functions to eliminate rows from a list based on
criteria?
I have column C and D which have 500 rows of data. In about 10% of the rows
the data in C does not match the data in D. I want to reproduce the list in
sheet 2 but only disply the rows that have non-matching data:

Sheet 1 Sheet 2
Col C Col D Becomes Col C Col D
12 12 tom david
tom david 8 0
3 3 table chair
8 0
jim jim
table chair

I know I could use a data column to compare the columns and then use vlookup
to get the next non-matching row, but I'm looking for a way to do it with
just formulas in sheet 2 and now have to create a compare column.

Thanks in advance for any assistance.
ed

Shortening a list
 

Thanks Joel,

I'm sure that would work, but not quite what I'm looking for.

I already have a solution that does not require any steps (see below). What
I am looking for is a solution that does not require the data column.
"Perhaps me asks too much."

Current solution
Sheet2 - Data starts on row 3
Column C- Displays list from Sheet1 Col C - only if items in row differ
=OFFSET(Sheet1!C$1,VLOOKUP(J3,I3:J$380,2,0),0)

Column D- Displays list from Sheet1 Col D - only if items in row differ
=OFFSET(Sheet1!D$1,VLOOKUP(J3,I3:J$380,2,0),0)

Column I = Data row - If Col C & D do not match, increment value, otherwise
show 0
=IF(Sheet1!C3=Sheet1!D3, MAX(I$2:I2)+1,0)

Column J = Data row - start w/ 1 incremented by 1 (1, 2, 3, etc.)

So Column J contains 0, 0, 1, 0, 0, 0, 2, 0, 3, 0, 0, 0, 0, 4, 0 etc.

And Cols C & D show the data from only rows with mismatch because VLOOKUP
finds which row has the next non zero value.

What I'm looking for is a formula that would allow me to eliminate column I.
(I already know I can eliminate column J).

Any help appreciated.
ed






"joel" wrote:

Herr is the easy way

1) Copy sheet 1 to sheet 2
2) Add an index number in column E. Put 1 in E1, put 2 in E2 and pull down
sheet to get a column of sequential numbers. I'm going to sort and this is
so we can get the order back to the original order when done.
3) put tis formula in cell F1 =C1=D1 This will put either True or
False in colun F
4) Select column F and Copy. then use PasteSpecial with Value selected to
convert formula to Values
5) Select data to be sorted. then go to menu Data - Sort and select column
F as only key
6) Delete Rows with TRUE.
7) sort again using column E to return the data to its original order.

This seems like a lot of steps but it is very quick.

"expect_ed" wrote:

Is there a way to combine functions to eliminate rows from a list based on
criteria?
I have column C and D which have 500 rows of data. In about 10% of the rows
the data in C does not match the data in D. I want to reproduce the list in
sheet 2 but only disply the rows that have non-matching data:

Sheet 1 Sheet 2
Col C Col D Becomes Col C Col D
12 12 tom david
tom david 8 0
3 3 table chair
8 0
jim jim
table chair

I know I could use a data column to compare the columns and then use vlookup
to get the next non-matching row, but I'm looking for a way to do it with
just formulas in sheet 2 and now have to create a compare column.

Thanks in advance for any assistance.
ed

Shortening a list
 

Excel 2007 Tables
No formulas, no code, no compare columns.
http://www.mediafire.com/file/tnyqmv...04_06_09b.xlsx

Shortening a list
 

Thanks for the help.
I'm using a work machine. They are still running Excel '03.
Doesn't like this file.
ed

"Herbert Seidenberg" wrote:

Excel 2007 Tables
No formulas, no code, no compare columns.
http://www.mediafire.com/file/tnyqmv...04_06_09b.xlsx

Shortening a list
 

How to open and save Word 2007, Excel 2007, and PowerPoint 2007 files in
earlier versions of Office programs
http://support.microsoft.com/kb/924074

expect_ed wrote:

Thanks for the help.
I'm using a work machine. They are still running Excel '03.
Doesn't like this file.
ed

"Herbert Seidenberg" wrote:


Excel 2007 Tables
No formulas, no code, no compare columns.
http://www.mediafire.com/file/tnyqmv...04_06_09b.xlsx

Shortening a list
 

YES! - that is what I want to do. Now how do I replicate that for my list?
I did the online training on lists but it does not get me to the point where
I can do this. When I filter a normal list it simply hides the rows that are
not in the filter. Clearly you are doing something different. Please
explain.
thanks
ed

"Herbert Seidenberg" wrote:

Excel 2007 Tables
No formulas, no code, no compare columns.
http://www.mediafire.com/file/tnyqmv...04_06_09b.xlsx