Inserting rows:middle of sorting

[ckim8]

Hey all, I am in a middle of sorting through data for a project and it is becoming very tedious (there is an enormous amount of data). I would appreciate any help in coding a VBA macro to automate the process below:

To simplify, I have two columns of data that have number sets that are repeating (for example, I will denote these two columns as 'A' and 'B'):

A B
1 1
1 2
1 3
1 4
1 5
1 6
1 7

* Column A will repeat a constant from 1 to 9, so for the next iteration, column A will repeat the number two 7 times, and the iteration after will repeat three 7 times and etc...

* Column B will always range from 1 to 7

* This pattern will repeat 7000 rows!

The problem is that in the set of data that I have, some rows are missing. For example, for some repeat of the sets will look like below:

A B
2 1
2 2
2 3
2 4
2 6
2 7

*Notice that the above set is missing the one row: 2,5

I would appreciate some help in developing a macro that will run through the data, find the missing points and insert the proper numbers so that every repeat of the sets will have a column A that is repeating a certain constant (again, the constant ranges from 1 to 9) and a column B that will range consistently from 1-7.

Thanks in advance!

[Richard Buttrey]

Hi,

You don't necessarily need a macro. Perhaps the easiest way is to use a helper column C and enter the following in C1 and copy down your range

Please Login or Register  to view this content.

Now in another column say E enter a complete list of all your expected numbers. In this example this would be 11,12,13 ....etc

Now in E1 copied down enter

Please Login or Register  to view this content.

Now filter this column for #N/A which indicates a missing number. Copy these missing numbers underneath your last value in column C and then sort columns A:C using C as the sort key.

HTH

[ckim8]

Hi Richard,

Thanks for the suggestions. However I need to write a code that will automatically scan through the columns, find where the numbers do not repeat and automatically fill in the correct values... Again as an example the following is a correct iteration:

A B
2 1
2 2
2 3
2 4
2 5
2 6
2 7

*column A will always contain an integer (ranging from 1-9) that repeats 7 times, column B will always contain the values 1-7, increasing by one.

The following is a problematic iteration:
A B
2 1
2 2
2 3
2 6
2 7

*Notice that in comparison to the first example, the above iteration does not contain two rows:

2 4
2 5

What I am aiming to do is to write a macro that will find where these errors occur and place cells with the correct cells. Hope that clarifies things.

[Richard Buttrey]

Hi,

I understand the requirement which could be met by my suggestion. However if you need a macro to perform those steps rather than just doing it, then you could record one.

Regards