Assigning rows to row numbers

[sektor666]

Hi,
I need a macro/script that will allow me to move rows based on cell values, i.e.:
I have a worksheet with 2 columns. Column B contains data, and column A contains the number of the row that the data from column B should be in.
A B
3 data1 //this is in the 1st row, should be in the 3rd
8 data2 //should be in the 8th row, so i need 4 blank rows above

any idea on how to do this? maybe an external software solution (e.g. asap utilities?)
any help would be greatly appreciated.

[JimmyA]

I have a feeling I'm missing something here.............but if the number of the row that the data should be in is in column A, can you not just sort column A????

[sektor666]

I have a feeling I'm missing something here.............but if the number of the row that the data should be in is in column A, can you not just sort column A????

no, that wouldn't solve the problem. as you can see in the example, the first value of column A is '3', so i would need that cell and the data from column B that corresponds to it to be 'moved' to row 3, i.e. i would need two blank rows inserted above it.

[pike]

its ruff but

Please Login or Register  to view this content.

[sektor666]

its ruff but

Please Login or Register  to view this content.

thank you very much for your response, unfortunately it did not solve the problem
the macro apparently left column A untouched and replaced all data in column B with numbers from column A, some of which are in the correct rows (so it's a step in the right direction, but I need the data in column B to move long with the numbers from column A, not get replaced by them). For some strange reason, however, some of the higher numbers ended up in the wrong rows, for example, while row 17 contains the value 17, row 16 got the value 1697 (seems the macro only looks at the first two numbers of a value?)

[Richard Buttrey]

Hi,

I think you're ignoring the simplicity of Jimmy's solution.
Implicit in what you say above is that there are some 'missing' numbers from column A. In which case simply create a function to identify the missing numbers, e.g. list numbers 1:1000 or whatever somewhere and do an =MATCH() function to find those numbers in column A. When you've identified which numbers are missing simply add them to the bottom of column A and sort as Jimmy mentioned.

HTH

[pike]

I did say it was ruff ,but this will do the trick

Please Login or Register  to view this content.

[JimmyA]

Looks like sektor666 has just got the answer and gone!!

I love it when that happens. Makes it all worthwhile

[sektor666]

that's not true, i just got a priority assignment and had to complete that before trying your suggested solutions.
i'll try both in a minute and post the results
thanks for your suggestions!

Looks like sektor666 has just got the answer and gone!!

I love it when that happens. Makes it all worthwhile

[sektor666]

that's not true, i just got a priority assignment and had to complete that before trying your suggested solutions.
i'll try both in a minute and post the results
thanks for your suggestions!

ok, sorry for being a bother, but the macro returns an error (Runtime error 9, Subscript out of range) on line "b(p) = e"

also, i'm really not sure how the =MATCH() function can help me add the missing numbers to the A column? according to help files the function is meant for looking up the amount of data occurences in a range of cells?

[Richard Buttrey]

Hi,

Re the MATCH()

You have existing numbers 1, 5, 8,... whatever in column A with associated column B values.
You don't have say 2,3,4,6,7.. etc.

In a spare column say column G, list the numbers 1-1000 or whatever starting in G1. i.e. enter a 1 & 2, highlight those two cells, select the little handle at the bottom right hand corner and drag down for 998 rows.

Now in H1 enter the formula

Please Login or Register  to view this content.

and copy down to H1000.

You'll end up in column H with numbers and #N/As. Now filter column H for #N/As, then copy all the column G numbers (which will be 2,3,4,6,7 etc and paste them to the bottom of your column A numbers.
Now sort columns A&B using column A as the key.

All this takes far longer to write than actually do

HTH

[sektor666]

hmmm, that didn't seem to work...
after doing what you said, the first 6 rows of H1 were #N/A, #N/A, 1, 2, #N/A, 3.
and column A does start with a 3, so i can't have it in column H.
i scrolled through the list and there are at least several more occurences of numbers from column A in column H :/

could the reason be the rather large difference between some of the numbers? e.g. A54 = 143, but A55 = 390, that's 247 missing numbers

[pike]

quick fix

Please Login or Register  to view this content.

[shg]

Sektor, please don't quote whole posts. It's just clutter.

[pike]

edited last post for got

Please Login or Register  to view this content.

and changes to

Please Login or Register  to view this content.

[sektor666]

thanks for the fix, pike. however, i'm still getting the 'subscript out of range' error at 'b(p) = e'

[Richard Buttrey]

Hi,

I think you are missing the point. There SHOULD be numbers in column H, however those numbers represent the row number in col A in which the number from column G is being matched, not the column A number itself. Remember all you're trying to do is establish what numbers from column G are missing in column A.

Go on to complete the task of filtering on column H and copying to A and hopefully you'll see what I mean.

Rgds

[pike]

the whole code with explaination
The multiply the array by 1.5 the row count is to allow for the highest unknow row number

eg two numners
3 data
4000 data
this will fail as the array isnt redim that large i=2
2* 1,5 =3 to small an array

so if the array has 3000 numbers 1 to 4500 it will work
3000 * 1.5 =4500 this will work

so depending on the split
I will have to add some "ReDim Preserve" to expand the array if it needs to grow , but its late and that can wait till tomorrow. Unless you or someone else want to have a crack at it

Please Login or Register  to view this content.

catch tomorrow night or just turn tomorrow so tonight

[sektor666]

both solutions work!
problem SOLVED!
thanks a lot guys!