Skip one row when value changes in another column

Unclear (to me).

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B	C	D	E	F
12	1			0
13	1			0
14	2				Skip here
15	2			0
16	2			0
17	3			0	Why no skip here?
18
19				0

Sheet: ?????1

You cannot move manually entered data with a formula - this will probably need VBA.

There are two of "1", then three of "2" and one of "3", so skips are after two, three and one of 0's.

Clear as mud - sorry! Someone will get it, but it can't be done with a formula if those 0s are already in place. Please confirm that they have been entered manually and not using a formula.

0's are entered manually

That's a shame. This would have generated them for you:

=LET(u,UNIQUE(B3:B8),c,COUNTIF(B3:B8,u),DROP(TEXTSPLIT(TEXTJOIN(" ",,REPT(0&" ",c)),," "),-1))

I shall move the thread to the VBA section, as it can't be done with a formula.

PLEASE don't quote unnecessarily! It's just clutter.

please try

Formula:

Please Login or Register  to view this content.

@Hans - the problem is that the OP wants the manually entered data to move, so a formula won't suit. This has been moved to the VBA section.

My formula does pick up the manually entered data and reorganize it, in the way requested.

But that's not what is required as far as I now understand it - the original manually entered data needs to be rearranged. Both your formula and mine produce results in a different column.

@Ali, The difference between your formula and my formula is that your formula does not use the manually entered data and always produces zeros and empty cells and
my formula does use the manually entered data and produces the same data with empty cells in between.

I am aware of that, but neither formula moves the manually created data!

Neither you nor I have provided a solution to the problem. That's why it's now in the VBA section - can't be done with a formula.

neither formula moves the manually created data

My formula does put the manually entered data in a different place.

I look forward to the OP's response.

Sigh! Yes, I know it does.

I look forward to the OP's response, too, but I still think he wants the manually entered data to move, not be copied and rearranged elsewhere.

Suggestions seen, but no response ...

Some feedback would be appreciated.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.