Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I have a list which contains seven digit numbers with around 30 columns and
3,000 rows approximately. Not all cells contain a number - some are blank. I want to find duplicate numbers in each row and delete them. For example, I have: A B C D E F 1234567 2345678 2584987 1234567 1234567 5896584 2584987 6598475 5896584 1234567 2584987 5897845 5897845 2584987 etc. ... The numbers are in no particular order and may occur more than once in each row. In some rows they may occur 20 or more times. They also recur throughout the worksheet. What I want to do is delete all the duplicates in each row only, i.e. in the first row above, where the same number occurs three times, I want to get rid of two of them and only leave one. It doesn't matter how the cells end up in regard to the columns, as long as the numbers stay in the same row, as each row will be concatenated into one number eventually. Some rows may only have two numbers in them (in the first two columns) but other rows may have anywhere up to 30 numbers. Each number has seven digits. I hope this makes sense and someone can come up with a formula to enable me to do this easily. Thank you in anticipation. Lyn. |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
hi "LM"
this site is useful for you: www.cpearson.com/excel/duplicates.aspx hope this helps. nader "LM" wrote: I have a list which contains seven digit numbers with around 30 columns and 3,000 rows approximately. Not all cells contain a number - some are blank. I want to find duplicate numbers in each row and delete them. For example, I have: A B C D E F 1234567 2345678 2584987 1234567 1234567 5896584 2584987 6598475 5896584 1234567 2584987 5897845 5897845 2584987 etc. ... The numbers are in no particular order and may occur more than once in each row. In some rows they may occur 20 or more times. They also recur throughout the worksheet. What I want to do is delete all the duplicates in each row only, i.e. in the first row above, where the same number occurs three times, I want to get rid of two of them and only leave one. It doesn't matter how the cells end up in regard to the columns, as long as the numbers stay in the same row, as each row will be concatenated into one number eventually. Some rows may only have two numbers in them (in the first two columns) but other rows may have anywhere up to 30 numbers. Each number has seven digits. I hope this makes sense and someone can come up with a formula to enable me to do this easily. Thank you in anticipation. Lyn. |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi! LM
Think your data in A1 put this formula in B1 =if(A2=A1,"Deleted","ok") If the data is shown three times two times it show DELETED AND ONE TIME OK FILTER THE COLUMN A DELETE THE DATA OR ROW "LM" wrote: I have a list which contains seven digit numbers with around 30 columns and 3,000 rows approximately. Not all cells contain a number - some are blank. I want to find duplicate numbers in each row and delete them. For example, I have: A B C D E F 1234567 2345678 2584987 1234567 1234567 5896584 2584987 6598475 5896584 1234567 2584987 5897845 5897845 2584987 etc. ... The numbers are in no particular order and may occur more than once in each row. In some rows they may occur 20 or more times. They also recur throughout the worksheet. What I want to do is delete all the duplicates in each row only, i.e. in the first row above, where the same number occurs three times, I want to get rid of two of them and only leave one. It doesn't matter how the cells end up in regard to the columns, as long as the numbers stay in the same row, as each row will be concatenated into one number eventually. Some rows may only have two numbers in them (in the first two columns) but other rows may have anywhere up to 30 numbers. Each number has seven digits. I hope this makes sense and someone can come up with a formula to enable me to do this easily. Thank you in anticipation. Lyn. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Delete duplicates? | Excel Discussion (Misc queries) | |||
Delete duplicates? | Excel Discussion (Misc queries) | |||
Delete Duplicates | New Users to Excel | |||
combine row and the delete duplicates | Excel Discussion (Misc queries) | |||
Delete duplicates | Excel Worksheet Functions |