If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
new user desperate for help
hello all,
I am wondering if anyone has the answer to this. my first column A has a lot of repetitive values aaaa,bbb,cccccccc,dd,eeeeeeeeeeeeee,f,ggggg,hhh Second column B has different values (that correspond to each number in 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 criteria that the chosen value (the values that shows from A have the LEAST (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 --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
new user desperate for help
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 "drooby " wrote: hello all, I am wondering if anyone has the answer to this. my first column A has a lot of repetitive values aaaa,bbb,cccccccc,dd,eeeeeeeeeeeeee,f,ggggg,hhh Second column B has different values (that correspond to each number in 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 criteria that the chosen value (the values that shows from A have the LEAST (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 --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
new user desperate for help
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. 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 "drooby " wrote: hello all, I am wondering if anyone has the answer to this. my first column A has a lot of repetitive values aaaa,bbb,cccccccc,dd,eeeeeeeeeeeeee,f,ggggg,hhh Second column B has different values (that correspond to each number in 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 criteria that the chosen value (the values that shows from A have the LEAST (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 --- Message posted from http://www.ExcelForum.com/ . |
Thread Tools | |
Display Modes | |
|
|