How to find missing numbers in a column

[Spacy22]

Hi,
I have a column of 1277 sequenced data points numbered 1 thru 12
(representing months) which repeat throughout the column. Some of the
numbers (months) are missing over numerous years and I need to locate these
numbers and replace the missing number with a blank column. Is there a very
simple way to do this??

Thanks!

 

[Pete_UK]

Do you have year information in a different column? And do you mean
that you need to insert a blank row for the missing numbers?

Pete

 

[Spacy22]

Hi Pete,

Yes, the years are in the column beside and I did mean that I would like to
insert a blank row for the missing numbers!

Thanks

 

[Pete_UK]

Okay, well I don't know what other data you have but from the month
and year columns you could create a date in a helper column (suppose
column F) using this:

=DATE(year,month,1)

where year might be D1 and month might be C1, and then you can copy
this down.

Elsewhere on your sheet, say in column K, you could form a sequential
date column by putting the earliest date in your sequence in K1 (in
the form 1st of the month), and in K2 you could put:

=DATE(YEAR(K1),MONTH(K1)+1,1)

and copy this down as far as you need.

Then you could set up some INDEX/MATCH formulae using the dates to
copy your data across to other columns on the right of column K. I
don't know what data you have, so I can't give you the exact formulae.
Once you have all these, then you can fix the values in columns K
onwards.

Eventually, you can delete the columns up to J and you will end up
with what you want.

Hope this helps.

Pete