Move all data from selected rows, into the top most selected row.

[Travis Kunnen]

Hi All

Another problem.

Im seeking a function or macro to move all the data from a selected series of rows, into the top most row of the selected area.
Example below. There are 12 rows of Romboutsia (84 to 95), and all rows will (/should) have 1 data point. Im looking to move all the data from the selected rows, into the top most row for this group. So the data from C87, needs to move into C84, D90 into D84, E93 into E84, etc etc...
There will never be two sets of data in the rows, for the same selected grouping of names from column A.
Then I can repeat the process for the next set, the Ruminococcus, and so on and so forth....

Macro Top 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
84	Romboutsia	0,0872162
85	Romboutsia					0,02813
86	Romboutsia									0,03553
87	Romboutsia		0,077430923
88	Romboutsia						0,21361
89	Romboutsia										0,11849
90	Romboutsia			0,047901507
91	Romboutsia							0,032
92	Romboutsia											0,01558
93	Romboutsia				0,02124862
94	Romboutsia								0,02868
95	Romboutsia												0,0124
96	Ruminococcus	0,01897662
97	Ruminococcus		0,014345097
98	Ruminococcus			0,014001448
99	Ruminococcus							0,0106
Layer 1

 

[rpaulson]

Travis, are you having issue or is it working as needed?

 

[Travis Kunnen]

@rpaulson ... the solution provided by you works 100%..

I was replying to @jolivanes for their solution that they asked me to try.
It is within the same thread...

 

[Sanjeev1976]

You can check the below formula approach as well :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
84	Romboutsia	0.087216
85	Romboutsia					0.028128
86	Romboutsia									0.035531
87	Romboutsia		0.077431
88	Romboutsia						0.213611
89	Romboutsia										0.118493
90	Romboutsia			0.047902
91	Romboutsia							0.031996
92	Romboutsia											0.015576
93	Romboutsia				0.021249
94	Romboutsia								0.028684
95	Romboutsia												0.012396
96	Ruminococcus	0.018977
97	Ruminococcus		0.014345
98	Ruminococcus			0.014001
99	Ruminococcus							0.010598
100
101	Romboutsia	0.087216	0.077431	0.047902	0.021249	0.028128	0.213611	0.031996	0.028684	0.035531	0.118493	0.015576	0.012396
102	Ruminococcus	0.018977	0.014345	0.014001				0.010598
Sheet1

Cell Formulas
Range	Formula
A101:A102	A101	=UNIQUE(A84:A99)
B101:M102	B101	=XLOOKUP($A101,FILTER($A$84:$A$99,B$84:B$99<>""),FILTER(B$84:B$99,B$84:B$99<>""),"")
Dynamic array formulas.

 

[jolivanes]

Thank you very much for trying it on a real life situation.
As you mentioned, rpaulson has it covered for you. I'll have a look at the approach I took in case there is only a single entry.

 

[jolivanes]

If you insert this line

If .Find(dataArr(j), , , 1, , 1).Address <> .Find(dataArr(j), , , 1, , 2).Address Then

after this line

With ActiveSheet.Columns(1)

and insert

End If

like so

        .Offset(1).Resize(.Rows.Count - 1).Delete Shift:=xlUp
    End With
End If    '<----- Insert here
End With

it eliminates the single entry problem.

 

[Travis Kunnen]

Hello @jolivanes
Please see below image. The left image is the after macro used from rpaulson (after I removed rows manually), and the right one is yours.
Yours worked for the first few genus' but then coped wrong data starting at row 12.
So the Alkalibacterium data in cell B12 belongs to Alkaliflexus in B13. But the Alkalibacterium in cells E, F, G 12 are correct.
And the Alkaliflexus data in C13 belongs to Alkaliphilus in D16.
The current data in D13 (0.00087822) belongs to Alkalitalea 5 rows below.
I didnt go through the whole data set, but this is within the 1st screen.

 

[jolivanes]

The downside of my suggestion is the use of SpecialCells, empty cells in this case.
If a cell looks to be empty but has an apostrophe or a non printable character in it, it does not work as intended.
You can, if you want to, check if cells are really empty but you have a perfect macro from rpaulson so I don't know if you want to go that route.

 

[Travis Kunnen]

I will check. But the data set I use is from an online data base, so all empty cell should be empty.
The macro from rpaulson works well, but I still do have to manually delete the cless from where data was copied from, into the upper row.
Still... manually deleting rows is a HUGE time saver compared to manually moving all of the data.

 

[jolivanes]

Maybe I am missing something. If you spot it, let us know

 

[jolivanes]

Are the values in Column A all in blocks of the same values or are they scattered?