# Insert Row For Missing Number In List

Dec 19, 2011
We recently bought some new software at work, and I'm having trouble formatting the exported data in a way that will work with existing reports. The attached file is what the data looks like after being exported. Basically, what I'm trying to do is insert rows for the missing numbers -- i.e., each name should be followed by rows numbered 1 through 10.

John Smith John Smith

1 1

4 2

8 3

4

5

6

7

8

9

10

My ultimate end goal is to have columns of data for each name, instead of rows:

John Smith 1 2 3 4 5 6 7 8 9 10

I've created a formula that can do that part, but it operates on the assumption that each name will be followed by a set number of rows.

What little I know about vba I've learned on my own, so I may just be missing something obvious.

View 4 Replies
ADVERTISEMENT
Sep 17, 2009

I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.

I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.

Here is my problem:

I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)

Example of data (Where "/" delienates column seperation):

Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain

6-1-06 12:15 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0

6-1-06 12:30 am / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0

6-1-06 12:45 am / 45 / 35 / .0001/ 95 / 5 / 7 / 3 / 0

6-2-06 6:00 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0

6-2-06 6:15 pm / 45 / 35 / .0001 / 95 / 5 / 7 / 3 / 0

So what I need to do is this:

1) Find which data times are missing

2) Add the appropriate amount of rows in between where the missing data would be

3) Add the correct dates to the new rows

4) Add "N/A" to the columns that have no data

You can see that my date and time are formatted in mm/dd/yy hh:mm

I found this on the website and was trying to modify it to my needs:

Sub InsRow()

Dim c

View 9 Replies
View Related
Jul 8, 2008

I have two columns which i want to compare, they contain text data such as A123.

what I'd like is if its in column A and not in Column B then add to bottom of column A.

Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.

View 9 Replies
View Related
Aug 4, 2009

I am looking for help. Sheet1 B1:1 has dates in each column in ascending order for one month. There are missing dates in between the days and they might look like this.

Example 1

7/1/20097/2/20097/6/20097/7/2009

Is there a code that will insert columns with the missing dates between the dates that already exist so that example 1 would become example 2?

Example 2

7/1/20097/2/20097/3/20097/4/20097/5/20097/6/20097/7/2009

View 6 Replies
View Related
Jun 18, 2008

can do the following thing:

Here's a simplified form of my data under the two columns B and C

B C

1 a

2 b

5 c

7 d

I want dummy rows to be inserted for the missing rows like:

B C

1 a

2 b

3 dummy

4 dummy

5 c

6 dummy

7 d

View 2 Replies
View Related
Nov 27, 2007

I have a sequence(or a series is more proper) of dates(without the hours) for. exapmple: 10/22/2007,1025/2007,10/25/2007. What I need is to insert rows with the missing dates. I found a VBE code for a macro which does the work but now the problem is that in some rows the dates are the same and if I have two times 10/25/2007 it adds the row with the next date after the first one and all messes up.

Range("A2").Select

Do Until ActiveCell.Value = Empty

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 Then

ActiveCell.Offset(1, 0).Select

Else

ActiveCell.EntireRow.Insert

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1

ActiveCell.Offset(1, 0).Select

End If

Loop

View 9 Replies
View Related
Aug 3, 2006

I am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:

Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

5 -------Part E

6 -------Part F

As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.

ColumnA ColumnB

1 -------Part A

2 -------Part B

3 -------Part C

4

5 -------Part E

6 -------Part F

I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.

View 4 Replies
View Related
Feb 19, 2008

The excel file has industrial information arranged by sector. The sectors are represented by six digit codes that are not strictly sequential. For example, a typical example of consecutive codes is 120011,120012,120030,120040,130011. There are upwards of three hundred sectors.

The data I am using is missing certain sectors - sometimes more than 30 at a time. I would like to find a quick way to identify the missing sectors and insert rows there, preferably with the first cell in the row being the missing sectoral code.

I have a similar problems with columns as well. But I can always just transpose the data and use whatever solution works for the rows.

View 9 Replies
View Related
Dec 18, 2012

I am currently using MS Excel 2007. Is there a way in VBA to automatically insert missing columns based on a defined series or range of values?

For example, we have a report that displays columns of: Year 2004 to Year 2012 per column (9 columns in total).

Sometimes, it displays missing a year or years (thus; our report has less columns). For example, it only shows 2008 to 2012 (missing 2004 to 2007). At times, it misses some years in between. What we do is manually add those missing years by manually inserting columns then inputting the missing year as their column headers.

Is it posisble for VBA to check if the columns contain all of the defined Year range (for example, you've initially defined: Year 2004 to Year 2012)? Afterwards, all missing columns are automoatically inserted (in chronological order or based on your defined values or range).

View 6 Replies
View Related
Jan 22, 2013

Excel 2007

ABCDE12145101843222121028543291410388563015104796731501058178325210

685894953107839104354108841011445510985111349661101215516710013135668

981417576999151858708916195971801720607291182161738219236274902024637

59121276476872228657786232425Sheet1

View 7 Replies
View Related
Apr 25, 2014

I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.

I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.

Still I have to check and manually insert extra missing for every half hour missing but that's bearable.

This formula inserts a "missing" every time it finds a gap.

My question is: How can I insert a row above every cell with "missing"?

How do I do that? Here is also my excel sheet.

CR1000_Meteo_20131113_2_CLEANED.xls

View 3 Replies
View Related
Oct 31, 2007

I have some code to generate hyprlinks from a folder

View 13 Replies
View Related
Dec 26, 2007

I have a list of 9 values ranging from 1 to 9 generated by formulas in column A. they can only be "1,2,3,4,5,6,7,8, or 9" and can be repetitive. Is there a formula i can use (not macro please) that will show me what values are missing from the list?

Example

A B

1

4

5

1

2

1

8

7

1

*Note there are 9 values in column A in any order. In this scenario the answer i would like shown is "3,6,9".

View 4 Replies
View Related
Feb 5, 2008

I have two lists of data. The Master data in sheet one and the monthly download in sheet two. I want to create a macro that will identify the new entries in sheet two and add it to the bottom of the list in sheet one.

View 2 Replies
View Related
Apr 4, 2008

I have a long list of numbers (1 to around 3000) in Column A and I need to print out only the numbers missing from that list. The numbers are sequential (except the ones missing) and there are no blank or missing cells, only missing numbers.

EXAMPLE:

1

2

4

5

7

.....

So I would only need to print the number 3 and 6. - it can be all in one cell, or in one column, it doesnt matter.

View 2 Replies
View Related
Jun 18, 2009

Hi guys/gurls.. is there a way i can get the following check against range for orders which give result as invoiced(if within the range), duplicated(if 2 or more than 2 are on the orders list) & missing (which on not in the orders when checked against the range).

View 6 Replies
View Related
Oct 2, 2009

i have two chart A and chart B from ,chart A got nine repeting numbers and Chart B will show what is the number not in chart A or is outside from this number 0.1.2.3.4.5.6.7.8.9. i want auto generate to chart B.

View 2 Replies
View Related
Jul 15, 2014

If seen a couple of examples but for whatever reason it doesn't work on my sheet. (Excel 2010 32 bit) I don't understand how to highlight the entire column to see this particular value is not in this list. I have two lists, I want to see what data am I missing In list 2 that I have in list one. Basically all I am trying to do is see what values are not in the second list but are in the first list so I can later add them to the second list. (Also the 2nd list is longer than the first if that makes a difference.)

View 3 Replies
View Related
Feb 7, 2008

I have sub folders with names 0001, 003A, 0032 etc in a main folder. I am using the following code to copy these sub folders name to the worksheet column A. This works well except that folder 0001 is copied as 1, 0032 as 32. How to modify the code so that 0001 is displayed in column A instaed of 1.

Private Sub Workbook_Open()

Range("b3:b6500").Clear

Range("c3:c6500").Clear

Dim fs, F, f1, fc, s, i

Range(Cells(3, 1), Cells(6500, 1)).Clear

parentfolder = ThisWorkbook.Path

Set fs = CreateObject("Scripting.FileSystemObject")

Set F = fs.GetFolder(parentfolder)

Set fc = F.SubFolders

For Each f1 In fc

Cells(3 + i, 1) = f1.Name

i = i + 1

Next

End Sub

View 7 Replies
View Related
Jul 20, 2009

I have some 9digit numbers and i have to enter the number 9 afther the fourth digit. Example : 50200491 - turned into 502900491.

View 5 Replies
View Related
Mar 22, 2012

This is an extract from my workbook.

11661681192203

I need a formula in column d that will return all the missing values between for example b1 and c2 and b21 and c21. The catch is that the numbers in column b and c can be anywhere but row b will always have the starting number and next to that cell the ending number in column d.

a further condition is that it must only calculate the missing number if the value in column a=1. if it is blank than nothing must be calculated.

View 1 Replies
View Related
Apr 26, 2012

Monthly I am sent a spreadsheet detailing cost centre approval ranges for our management team. Unfortunately, the data provided shows a low range and high range in two different columns. Is their a formula to automatically generate the values for the missing numbers

For example

columns A

1001

&

columns B 1007

Can excel automatically generate the 1002, 1003, 1004, 1005 & 1006

View 3 Replies
View Related
Oct 12, 2010

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1

2

3

4

8

9

becomes

1

2

3

4

8

9

View 7 Replies
View Related
Sep 15, 2014

i got worksheet which i store a list of all filing number in the same Col "R"

The combination of Filing Number Example

1) Invoice Filing Number = Inv2014070001 and continual

2) Purchase Order Filing Number = POD2014070001 and continual

3) Sublet Filing Number = SLT2014070001 and continual

Currently i am using a code which it can find the last filing number return to my userform textbox and increase the last number by 1. but this code can only return the very last filing number.

i need to find all the missing filing number in between all the filing number in Col R and return it to my userform listbox

VB:

If me.Remark.Value = "Sales" Then A = "Inv"

If me.Remark.Value = "Purchase" Then A = "POD"

If me.Remark.Value = "Sublet" Then A = "SLT"

Y = Me.TransYear.Value

myName = UCase(A & Y)

[Code]...

View 9 Replies
View Related
Apr 23, 2008

I need to create a formula (not a VBA code as it is a shared spreadsheet) to bring up the next number in a sequence. For example

1

2

4

5

The outcome would be 3 as it is the first number that has not been used.

View 2 Replies
View Related
Apr 1, 2014

Using Excel 2013,

I clicked on a field in my RowLabels

I then clicked on the Filter Arrow for the RowField

The SelectedField prompted with the correct field of 4 possible fields

However the item I am looking for is not in the list but I can plainly see it on the screen.

View 3 Replies
View Related
Mar 28, 2014

Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this

List 1 List 2 Missing in List 2

1 1 3

3 4 6

5 5

6 6

View 3 Replies
View Related
Feb 16, 2014

I have a time sheet in which i need user to enter a time sheet number in cell c14

I need a formula for that cell so the user can input the time sheet number but only be able to do this if the time sheet number they are imputing is the next number in sequence of the last one used.

View 1 Replies
View Related
May 2, 2007

I need this for a tracking sheet of scores. For example, 1 gets 100 points, 2 gets 90 points, 3 gets 80 points, etc. I need to set it up for 10 places. I have no idea and have fiddled with it for two hours now. I need to be able to put a 1 in the cell and 100 appears after I hit enter, etc.

View 5 Replies
View Related
May 4, 2009

I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.

View 4 Replies
View Related