Previous Topic Next Topic

One column multiple rows (data in sets)

Closed
Olivia Alder - May 21, 2010 at 06:47 PM
 lkc - Jan 9, 2012 at 04:08 PM
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

Answer 1 / 2
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 22, 2010 at 05:30 PM
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
Answer 2 / 2
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 22, 2010 at 03:21 AM
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.
1
Olivia Alder
May 22, 2010 at 11:08 AM
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.
0
lkc
Jan 9, 2012 at 04:08 PM
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
0