new user desperate for help

[drooby]

hello all,
I am wondering if anyone has the answer to this.

my first column A has a lot of repetitive value
aaaa,bbb,cccccccc,dd,eeeeeeeeeeeeee,f,ggggg,hhh

Second column B has different values (that correspond to each number i
column A .(x,y,z,q,w,e,r,t,y,u,i,o,p)

This is a BIG spreadsheet that runs through all the rows in excell.

I want to make all the repeated rows of A dissapear with the criteri
that the chosen value (the values that shows from A have the LEAS
(minimum) corresponding B value

example:
A B
10 5
10 3
10 10
10 20
10 6
10 8
15 12
15 5
15 8

I would like the output to be
A B
10 3
15 5


CAN ANYONE HELP PLEASE
thankyou very much in advance :confused

 

[Sukhjeet]

Here is a simple method, no macros used.

If u wish to keep the current order in which these values appear, insert a new column on the left and enter 1,2,3... in this. You will be sorting these values later to get back the order u had initially. Lets call this column Sr.
Now ur example table looks like this
Sr. A B
1 10 5
2 10 3
3 10 10
4 10 20
5 10 6
6 10 8
7 15 12
8 15 5
9 15 8


Now, sort your table by A, then B, both ascending. The table looks like this
Sr. A B
2 10 3
1 10 5
5 10 6
6 10 8
3 10 10
4 10 20
8 15 5
9 15 8
7 15 12

In the column after B, create a helper column called C. Enter the formula
=IF(B6=B5, "D", "") where B6 is the cell under column A and row in coumn Sr. entry 2. B5, obviously is the column Header A. Drag this down until the last row in the table. Your table, is like this. The D tells u that the column is duplicate.

Sr. A B C
2 10 3
1 10 5 D
5 10 6 D
6 10 8 D
3 10 10 D
4 10 20 D
8 15 5
9 15 8 D
7 15 12 D

Now, select the entire table and use data->Filter->Autofilter to filter this. Filter the table to show only D in the column. YOu table, after filtering looks like this.

Sr. A B C
1 10 5 D
5 10 6 D
6 10 8 D
3 10 10 D
4 10 20 D
9 15 8 D
7 15 12 D

Select the entire rows shown above, and delete them. Remove the filtering.
Your table looks like this.
Sr. A B C
2 10 3
8 15 5 #REF!

Resort by Sr. ascending to get your original order.

Delete columns Sr. and C.

You have the results!

Sukhjeet

 

[Paul Falla]

Dear Drooby

An alternative to Sukhjeet's method would be to use a
pivot table.

Expand the <Data> menu and select <Pivit Table and Pivot
Chart Wizard>. Select <Microsoft Excel list or database>.
Hightlight the range containing your data. Put the pivot
table on a new worksheet. On the new worksheet drag the
field name into the (A in your example) into the area that
says <Drop row fields here>, and then drag (B in your
example) into the <Drop data items here> area.
In the top left of your table you will have a header which
looks like a button and reads <Sum of(B)>. Double click on
the header and select <Min> from the list.

By doing it this way you can still have your original data
intact and still see at a glance the min values. You can
then update the main data source to your heart's content,
and by simply refreshing the pivot table your table will
stay up to date with the main data.

Hope this helps

Paul Falla

-----Original Message-----
Here is a simple method, no macros used.

If u wish to keep the current order in which these values

appear, insert a new column on the left and enter 1,2,3...
in this. You will be sorting these values later to get
back the order u had initially. Lets call this column Sr.

Now ur example table looks like this
Sr. A B
1 10 5
2 10 3
3 10 10
4 10 20
5 10 6
6 10 8
7 15 12
8 15 5
9 15 8


Now, sort your table by A, then B, both ascending. The table looks like this
Sr. A B
2 10 3
1 10 5
5 10 6
6 10 8
3 10 10
4 10 20
8 15 5
9 15 8
7 15 12

In the column after B, create a helper column called C. Enter the formula
=IF(B6=B5, "D", "") where B6 is the cell under column A

and row in coumn Sr. entry 2. B5, obviously is the column
Header A. Drag this down until the last row in the table.
Your table, is like this. The D tells u that the column is
duplicate.

Sr. A B C
2 10 3
1 10 5 D
5 10 6 D
6 10 8 D
3 10 10 D
4 10 20 D
8 15 5
9 15 8 D
7 15 12 D

Now, select the entire table and use data->Filter-
Autofilter to filter this. Filter the table to show only

D in the column. YOu table, after filtering looks like
this.