Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
changing integers in a list
Hello,
I have a list of integer numbers in a single column. They are in no particular order, and I cannot sort them (it would upset the rest of the spreadsheet). Some of the integers are repeated more than once, & I want to increase the repeats by 1, then 2, 3 ... so that I end up with a list of unique integers. So for example if I have 5,27,21,5,33,5,5 I would end up with 5,27,21,6,33,7,8. I've been studying the Match, & other Lookup functions, but am unclear about the best approach. Hope this makes sense ? Thanks K |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
changing integers in a list
Hi,
You could try this: =A1*100+COUNTIF(A1:$A$9,A1) (data e.g. in A1:A9) If you multiply the original figure by 100 you allow enough duplicates and you avoid that a modified figure matches another number i.e. if your duplicate is "5" and you append "3" you might have a number "53" already in your list. Regards, Hans KK schrieb: Hello, I have a list of integer numbers in a single column. They are in no particular order, and I cannot sort them (it would upset the rest of the spreadsheet). Some of the integers are repeated more than once, & I want to increase the repeats by 1, then 2, 3 ... so that I end up with a list of unique integers. So for example if I have 5,27,21,5,33,5,5 I would end up with 5,27,21,6,33,7,8. I've been studying the Match, & other Lookup functions, but am unclear about the best approach. Hope this makes sense ? Thanks K |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
changing integers in a list
Let's say your data from A1:A7
In B1 =IF(COUNTIF($A$1:A1,A1)=1,A1,$A$1+COUNTIF($A$1:A1, $A$1)-1) Copy as far as needed "KK" wrote: Hello, I have a list of integer numbers in a single column. They are in no particular order, and I cannot sort them (it would upset the rest of the spreadsheet). Some of the integers are repeated more than once, & I want to increase the repeats by 1, then 2, 3 ... so that I end up with a list of unique integers. So for example if I have 5,27,21,5,33,5,5 I would end up with 5,27,21,6,33,7,8. I've been studying the Match, & other Lookup functions, but am unclear about the best approach. Hope this makes sense ? Thanks K |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
changing integers in a list - THANKS
Thanks, this is very helpful - I'd not met the COUNTIF function.
K "KK" wrote in message ... Hello, I have a list of integer numbers in a single column. They are in no particular order, and I cannot sort them (it would upset the rest of the spreadsheet). Some of the integers are repeated more than once, & I want to increase the repeats by 1, then 2, 3 ... so that I end up with a list of unique integers. So for example if I have 5,27,21,5,33,5,5 I would end up with 5,27,21,6,33,7,8. I've been studying the Match, & other Lookup functions, but am unclear about the best approach. Hope this makes sense ? Thanks K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing cell value according to list value | Excel Discussion (Misc queries) | |||
auto updating list | Excel Worksheet Functions | |||
Changing the name of a list. | Excel Discussion (Misc queries) | |||
Changing the color of a list entry based on a tolerance | Excel Worksheet Functions | |||
Need to find oldest date in ever changing list. | Excel Worksheet Functions |