Move multiple rows to Coulmns

Problem:

I took data from field which are like this

18-11	16-11	4-11	31-13	1-6	7-10	34-11	5-16
3	_	3	_	3	3	1	2
26-16	4-14	26-1	18-9	27-15	15-7	28-13	26-14
3	3	3	5	7	1	5	1

but I want convert it to

18-11	3
16-11	_
4-11	3
31-13	_
1-6	3
7-10	3
34-11	1
5-16	2
26-16	3
4-14	3
26-1	3
18-9	5
27-15	7
15-7	1
28-13	5
26-14	1

 

So, here is the macro

Sub alternateRowsToCol()
    Application.ScreenUpdating = False
    Dim row, col As Integer
    row = Sheets("Sheet3").UsedRange.Rows.Count
    col = Sheets("Sheet3").UsedRange.Columns.Count
    
    
    For i = 1 To row Step 2
    Sheets("Sheet3").Activate
  
    Sheets("Sheet3").Range("A" & CStr(i) & ":X" & CStr(i + 1)).Select
    ' X here is because I have 24 rows but in example just 8
    Selection.Copy
    Sheets("Sheet4").Activate
  
    Sheets("sheet4").Range("A" & CStr(24 * ((i - 1) / 2) + 1)).Select
    ' 24 here is because I have 24 rows but in example just 8
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
  
    Next i
    Application.ScreenUpdating = True
   
End Sub

Insert n rows after each row in EXCEL

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 2
For r = r To 1 Step -1
Worksheets(4).Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

split a word with

Have you ever needed to split a word with certain character, yes I do, Mostly with _ or -

here you go, an excel macro

Function split1(text) 

split1 = Split(text, "_")(0) 

End Function 

Function split2(text) 

split1 = Split(text, "_")(1) 

End Function

INDEX and MATCH in EXCEL

So, I needed to replace a number and its related value. So easy, the formula is

=INDEX($I$2:$I$7,MATCH(B2,$H$2:$H$7,0))

Replace words in File (Python)

#batch renamer for contig and genBank Submission
#written by ananta acharya, PBGG, UGA
#May 28, 2010
#This program reads a .csv or .txt file with names to be replaced in each line separated with comma, for example
#oldname, newname
#oldname2, newname2
#Then it replaces the oldnames in provided ace file with newnames
#to start, u should give path of the acefile and replace file
#It reports the number of lines that were replaced
import os.path
import os
def OKtoRead(inputfile):
    """Checks whether the input file is readable"""
    
    if not os.path.exists(inputfile):
        print "File does not Exist"
        return False
        
    elif not os.path.isfile(inputfile):
        print "Is not a file"
        return False
    return True
def OKtoWrite(outputfile):
    """Checks whether the input file is writable"""
 
    if not os.path.exists(os.path.dirname(outputfile)):
        print "Path Does Not Exist"
        return False
        
    elif os.path.isdir(outputfile):
        print "Is not a file but folder"
        return False
    elif os.path.exists(outputfile):
        print "File already exists, Can not Overwite or Merge"
        return False
    else:
        return True
    
    
def makeDict(txtFile):
    
    if OKtoRead(txtFile):
        txtFile=open(txtFile)
        renameDict={}
        for line in txtFile:
            aLine=line.strip() 
           
            sets=aLine.split(",")
            
            if len(aLine)!=0:
                
                renameDict[sets[0]]=sets[1]
                
             
    txtFile.close()
    return renameDict
def replaceNames(renameDict, aceFile):
    aceFileNew=os.path.split(aceFile)[0]+"/renamed"+os.path.split(aceFile)[1]
    
    #if os.path.exists(aceFileNew):
     #   os.remove(aceFileNew)
    if OKtoRead(aceFile):
        aceFile=open(aceFile,"r")
        newFile=open(aceFileNew, "a")
        renameCount=0
        lineCount=0
        for line in aceFile:
            lineCount+=1
            for key, val in renameDict.iteritems():
                if key in line:
                    eachCount=line.count(key)
                    renameCount+=1
                    newline=line.replace(key, val)
                    print "renaming in line %s, %s items found to replace" %(lineCount, eachCount)
                    break
                else:
                    newline=line        
            newFile.write(newline)
            
    aceFile.close()
    newFile.close()
    print "number of replaced line=%s" %renameCount
    print "File written in %s" %aceFileNew
        
def main():
    aceFile=raw_input("File location for .ace: (give full location, or relative starting ../: ")    
    while not OKtoRead(aceFile):
        aceFile=raw_input("File location for .ace: (give full location, or relative starting ../: ")
    renameFile=raw_input("File location for replacement , formatted as .txt or .csv , names separated with comma in each line: (give full location, or relative starting ../: ")    
    while not OKtoRead(renameFile):
        renameFile=raw_input("File location for replacement , formatted as .txt or .csv , names separated with comma in each line: (give full location, or relative starting ../: ")
    
    dictt=makeDict(renameFile)
    replaceNames(dictt,aceFile)
main()