How to make a list?

[jints92]

If I have a list of years (some occurring many times) how do I make a list in the next column to add any years that don't appear. For example:

My list looks like this:
[TABLE="width: 408"]
<colgroup><col span="8" width="51"></colgroup><tbody>[TR]
[TD="class: xl141, width: 51, align: right"]2000[/TD]
[TD="class: xl141, width: 51, align: right"]2001[/TD]
[TD="class: xl141, width: 51, align: right"]2001[/TD]
[TD="class: xl141, width: 51, align: right"]2002[/TD]
[TD="class: xl141, width: 51, align: right"]2004[/TD]
[TD="class: xl141, width: 51, align: right"]2004[/TD]
[TD="class: xl141, width: 51, align: right"]2004[/TD]
[TD="class: xl141, width: 51, align: right"]2006[/TD]
[/TR]
</tbody>[/TABLE]

I want it to look like this:
[TABLE="width: 510"]
<colgroup><col span="10" width="51"></colgroup><tbody>[TR]
[TD="class: xl141, width: 51, align: right"]2000[/TD]
[TD="class: xl141, width: 51, align: right"]2001[/TD]
[TD="class: xl141, width: 51, align: right"]2001[/TD]
[TD="class: xl141, width: 51, align: right"]2002[/TD]
[TD="class: xl141, width: 51, align: right"]2003
[/TD]
[TD="class: xl141, width: 51, align: right"]2004[/TD]
[TD="class: xl141, width: 51, align: right"]2004[/TD]
[TD="class: xl141, width: 51, align: right"]2004[/TD]
[TD="class: xl141, width: 51, align: right"]2005
[/TD]
[TD="class: xl141, width: 51, align: right"]2006
[/TD]
[/TR]
</tbody>[/TABLE]

If anyone can help please?

 

[Marcelo Branco]

You are welcome and thanks for the feedback

The key-part of the formula is
IF(COUNTIF($A$2:$H$2,A3)>COUNTIF($A$3:A3,A3),A3,A3+1)

The first part
IF(COUNTIF($A$3:A3,MAX($A$2:$H$2))=COUNTIF($A$2:$H$2,MAX($A$2:$H$2)),"",...
only serves to prevent the formula returns numbers greater than MAX


M.

 

[jints92]

Maybe...another possible solution

Assuming your data in A2:H2
[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1999[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2001[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2001[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2002[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2004[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2004[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2006[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2006[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1999[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2001[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2001[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2002[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2003[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2004[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2004[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2005[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2006[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2006[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Type in A3 the first value of the serie (1999 in the example above)

Formula in B3
=IF(COUNTIF($A$3:A3,MAX($A$2:$H$2))=COUNTIF($A$2:$H$2,MAX($A$2:$H$2)),"",IF(COUNTIF($A$2:$H$2,A3)>COUNTIF($A$3:A3,A3),A3,A3+1))

copy across till you get a blank cell

M.

This worked too. Thanks for the help.

 

[T. Valko]

This worked. I didn't have the min and max in there but once I saw your formula I was able to tweak the rest to get what I wanted.

Thanks

Good deal. Thanks for the feedback!

 

[Marcelo Branco]

This worked too. Thanks for the help.

Sorry, it looks like I "stole" your thanks to T. Valko

I thought you were in # 10 was referring to my suggestion.
(T. Valko solutions always work!)

So, you have two solutions that work with formulas and another using a macro.

Enjoy!

M.