One column multiple rows (data in sets) [Closed]

Report
-
 lkc -
Hello,

I'd be grateful for any suggestions...
So what I have is

gene A
1
2
3
4

gene B
1
2
3
4

and what I would like is

geneA geneB
1 1
2 2
3 3
4 4

I have over 1000 gene categories so I wanted to avoid cutting and pasting.

Many thanks

Livi81



2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
Try this Olivia. It will take some time as you got just too many rows.

Sub MoveSelection()
Dim lMaxRows  As Long
Dim iCol As Integer
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    iCol = 2
    
    If (lMaxRows / 22 > Columns.Count - 1) Then
        MsgBox ("Data cannot fit in available columns. It needs " & lMaxRows / 22 & " columns where as only " & Columns.Count & " are available")
        Exit Sub
    End If
    
    Do While lMaxRows >= 22
        iCol = iCol + 1
        
        Range(Cells(1, iCol), Cells(22, iCol)) = Range(Cells(23, 2), Cells(44, 2)).Value
        Rows("23:44").Delete
        
        lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    Loop
End Sub
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
Are you saying that you have all the data in one column and you would like to move it to columns.
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
Hi rizvisa1,
Thanks for your help. Yes all my data is in one column and I would like to move it to mulitple columns, but not simply transpose it, as its in sets.

http://wikisend.com/download/888004/EXAMPLE FILE.xlsx

Any suggestions welcome.
I have a similar problem, except my data stretches across the rows and I would like to break it down under one "Class" column but still keep the data with the person's name.

First Name, Last Name, ID, Class One, C1 Instructor, Class Two, C2 Instructor, Class Three, C3I

Is there a way to place class 2, C2I, and class 3 with C3I on the next couple of lines under the students' respective names throughout my 1000+ line spreadsheet?

Thank you.

lkc

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!