Cut blocks of data and move cells up

[Legacy 287389]

Hi good people!,

Please help me with either a formula or vba, whichever you reckon will work best.

I have in cell A2 a number, lets say 10101. There are multiple numbers of the same value going down in column A, so lets say we have 55 "10101" numbers, then the number changes to the next number which will be 10102, and again there are lets say 36 of them, and then again a new number, 10103, and also a couple of those. These blocks of numbers could be up to 25 blocks, no more. in columns B and C next to these numbers are other info. What I would like is the following:

If I typed 10101 in cell G1, I would like the whole 10101 block to kind of cut out of the original position, and appear in columns G to I, starting at row 2 and populating downwards. All info in columns A to C which are underneath the created gap, must now shift up.

if I typed 10102 into cell G1, the 10101 block in columns G to I, must first be inserted back into it's original position in columns A to C, and the 10102 block of data must then appear in columns G to I, with 10103 block of data, then moving up to fill the gap.

Any and all help will be accepted with the utmost of respect and appreciation for your time. Thank you very much!

 

[Special-K99]

Why don't you do this instead?
Leave the original data intact.

in G2
=IFERROR(INDEX($A$2:$C$1000,SMALL(IF(($A$2:$A$1000=$G$1),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),COLUMN()-6),"")
Array formula, use Ctrl-Shift-Enter
copy across to I2

in J2
=IFERROR(INDEX($A$2:$C$1000,SMALL(IF(($A$2:$A$1000<>$G$1),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),COLUMN()-9),"")
Array formula, use Ctrl-Shift-Enter
copy across to L2

copy G2:L2 down for as many rows as there are in column A.

Columns G H I contain data matching G1
Columns J K L contain data not matching G1

This removes all the needless copying/inserting/deleting in your original description.

Bear in mind this may become slow depending how many rows of data you have in column A

 

[Legacy 287389]

Hi Special-K99,

Okay, I don't find that it works well, but we are close...meaning that if I type 10101 in G1, only the first row of the group 10101 appears in columns G-I and all other data still in J-L, with the exception of the first row.

 

[Special-K99]

Did you do this?

"copy G2:L2 down for as many rows as there are in column A."

 

[Legacy 287389]

I found the reason...my background error checking was disabled, so I enabled it, converted column A to numbers and it worked beautifully!!!..I am forever grateful...thank you very much Special-K99

 

[Special-K99]

You're welcome, it may slow down as rows increase bear in mind.

 

[Legacy 287389]

Yes, I have come to realize that, 14320 rows takes about 1 minute...but it gets the job done!!..