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

=VALUE(TEXT(A1,0)&TEXT(B1,0))

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

=MATCH(E1,C:C,FALSE)

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