Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing numbers in a column
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing numbers in a column
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 On Jan 27, 11:43*pm, Spacy22 wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing numbers in a column
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" wrote: 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 On Jan 27, 11:43 pm, Spacy22 wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find missing numbers in a column
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 On Jan 28, 1:34*am, Spacy22 wrote: 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" wrote: 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 On Jan 27, 11:43 pm, Spacy22 wrote: 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!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A Macro to find missing serial numbers in a column | Excel Discussion (Misc queries) | |||
find missing numbers in a sequence | Excel Discussion (Misc queries) | |||
find missing numbers in a sequence | Excel Discussion (Misc queries) | |||
Find missing sequential numbers | New Users to Excel | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions |