| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 26579 | 2002-10-30 21:32:00 | Excel spreadsheet multiple entrys | asdex (1488) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 94710 | 2002-10-30 21:32:00 | Hi, I have a spreadsheet with multiple entrys in a column. eg. 1123,1123,1123. 1255,1255,1255. 1877,1877,1877,1877. Is there any way to delete all the entrys except one of each? I don't want to do it manually as there is 50-60 in some of the spreadsheets. Thanks, asdex |
asdex (1488) | ||
| 94711 | 2002-10-30 21:40:00 | Iv've used a rather long way via ...data..consolidate..., but would be interested if there is a shorter way? | jeep (1673) | ||
| 94712 | 2002-10-30 22:25:00 | Using Data/Filter/AdvancedFilter and selecting UniqueRecordsOnly can perform this function. Providing that the information in the other corresponding columns is exactly the same as well, otherwise they not Unique. You can copy the data to a new location, of filter in place, copy the filtered data temporarily to a new sheet, select Data/Filter/ShowAll and delete the area then copy the temporary data back. Have a play with a copy of your worksheet to see how it can work for you. HTH |
Russell D (18) | ||
| 94713 | 2002-10-30 22:55:00 | The easiest way I know is: Insert a new column to the right of the one containing multiple entries. in first cell insert "=LEFT(A1,4)" (without the "") press enter. then just drag it to the bottom of the list or use "ctrl + downarrow" . Provided all the entries are 4 digits of course :-) Finally you will need to highlight this new column select copy, then use paste special and select values only. |
BarryMcQ (1578) | ||
| 94714 | 2002-10-30 23:12:00 | Thanks everyone for that information. I shall try both ideas or variations on them. The data in each row isn't quite the same, in one column each has a different reference number. E10029 ADAPTOR X1110021450-04 E10029 ADAPTOR X1410021436-04 E10029 ADAPTOR X1510020945-05 E10029 ADAPTOR X1410021436-06 E10030 TUBE TUMBLER X1110021450-04 E10030 TUBE TUMBLER X1110021450-06 This is a cut and paste from the spreadsheet, I only want one adaptor and one tube tumbler. Each spreadsheet has up to 60 rows and most 6 repeats. The reference number is difference each time but everything else is the same. There are 12 columns, these three and test results in the rest. asdex |
asdex (1488) | ||
| 94715 | 2002-10-31 01:06:00 | A couple of questions- Is the following data example you gave in a single column? E10030 TUBE TUMBLER X1110021450-06 If it is, is there always an "X" in front of the reference number? |
Russell D (18) | ||
| 94716 | 2002-10-31 01:55:00 | If you're just using this list as a reference/lookup of your inventory, then the easiest way to order/sort your list would just be to use the ...data...autofilter. Starting this up, you can use he drop down box, pick a custom criteria and away you go! | jeep (1673) | ||
| 94717 | 2002-10-31 02:41:00 | Hi, they are in different columns and all the reference numbers are different but do all start with an "x" Thanks, asdex |
asdex (1488) | ||
| 94718 | 2002-10-31 02:56:00 | I got it! By selecting the first column and using sort unique records only in the advanced filter, I got rid of all repeats in that column and their associated rows. Thanks very much for the help, asdex |
asdex (1488) | ||
| 94719 | 2002-10-31 06:01:00 | You might also try the following: Suppose your repeated entries are in column A starting at A1. In Col B (or any other) try =IF(A2<>A1,A1,"") and fill down col B. Depends if you want your unique to remain in the same place with nulls (or anything else for that matter) in the duplicate spaces or want to have them closed up, in which case you can modify the above accordingly. |
rugila (214) | ||
| 1 | |||||