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?

 

[jints92]

I know this isn't an easy thing to do but does anyone know how to do it. If there is a way without using VBA that would be best.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG27Aug40
[COLOR="Navy"]Dim[/COLOR] RngAc [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c
c = 1
[COLOR="Navy"]Set[/COLOR] RngAc = Range(Range("A1"), Cells(1, Columns.count).End(xlToLeft))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngAc
    [COLOR="Navy"]If[/COLOR] c > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Dn - Cells(2, c - 1) > 1
            Cells(2, c) = Cells(2, c - 1) + 1
            c = c + 1
        [COLOR="Navy"]Loop[/COLOR]
    [COLOR="Navy"]End[/COLOR] If
        Cells(2, c) = Dn
        c = c + 1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[T. Valko]

I know this isn't an easy thing to do but does anyone know how to do it. If there is a way without using VBA that would be best.

I can see a way to do it with formulas but it will take 2 steps.

With your current list, you could add a formula at the end of it that adds the missing years. Then, you'd create a new list that is sorted in ascending order.

Interested in this approach?

 

[jints92]

I can see a way to do it with formulas but it will take 2 steps.

With your current list, you could add a formula at the end of it that adds the missing years. Then, you'd create a new list that is sorted in ascending order.

Interested in this approach?

Sure, bring it on.

 

[T. Valko]

Sure, bring it on.

What version of Excel are you using?

 

[jints92]

What version of Excel are you using?

2007

 

[T. Valko]

2007

Let's assume the current list is in the range A2:H2.

I'm also assuming that both the min year and max year are present!

Enter this array formula** in I2:

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN($A2:$H2)&":"&MAX($A2:$H2))),$A2:$H2,0)),ROW(INDIRECT(MIN($A2:$H2)&":"&MAX($A2:$H2)))),COLUMNS($I2:I2)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across until you get blanks.

Now, create the new list sorted in ascending order...

Enter this formula A5 and copy across until you get blanks:

=IFERROR(SMALL($A2:$Z2,COLUMNS($A5:A5)),"")

Use an end of range that is sure to include all the data in the newly expanded original list.

 

[Marcelo Branco]

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.

 

[jints92]

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