Quick Question Regarding Marco - Copy and paste nth entry into new Column

[mortonm]

I have some data output from an instrument, it is in a text file, tab deliminted.

The data, when output from the instrument provides a block of data 1000 rows by 10 columns.

My problem is that I ONLY want every eleventh piece of data, and the rest to me is garbage.

The data should be in 11 columns, but was output from the source as 10, so the data I want is not in a regular pattern of rows or columns, but every 11th piece.

Could you help me develop an easy macro to accomplish this?

many thanks

 

[ColinKJ]

Hi mortonm,

Put this code in a new WB:

Sub Every11thItem()
R = Sheets(1).Range("A65536").End(xlUp).Row
x = 0: y = 0
For a = 1 To R
    For b = 1 To 10
        x = x + 1
        If (x / 11) = Int(x / 11) Then
            y = y + 1
            ThisWorkbook.ActiveSheet.Cells(y, 1) = Cells(a, b)
        End If
    Next b
Next a
End Sub

Then open your .txt file using XL.

With the .txt WB the active WB, run the code. You should get every 11th item of data in your new WB.

 

[crimson_b1ade]

see below if your file is a text file. item in red you will have to update according to your situation:

Sub ImportEleventhElement()
 
Dim r As Long
Dim s As Long
 
r = 1
 
Open "C:\MyFile.txt" For Input As #1
 
Do While Not EOF(1)
   
    Line Input #1, txt
    
    For i = 1 To Len(txt)
    
        If Mid(txt, i, 1) = Chr(9) Then b = b + 1
        
        If b = 10 Then
            
            s = i - 1
        
            Do Until Mid(txt, s, 1) = Chr(9) Or s = 1
                
                s = s - 1
            
            Loop
                                              
            Cells(r, 1) = Mid(txt, s, i - s)
            r = r + 1
            b = 0
        
        End If
    
    Next i
 
Loop
 
Close #1
 
End Sub

 

[mortonm]

see below if your file is a text file. item in red you will have to update according to your situation:

Sub ImportEleventhElement()
 
Dim r As Long
Dim s As Long
 
r = 1
 
Open "C:\MyFile.txt" For Input As #1
 
Do While Not EOF(1)
   
    Line Input #1, txt
    
    For i = 1 To Len(txt)
    
        If Mid(txt, i, 1) = Chr(9) Then b = b + 1
        
        If b = 10 Then
            
            s = i - 1
        
            Do Until Mid(txt, s, 1) = Chr(9) Or s = 1
                
                s = s - 1
            
            Loop
                                              
            Cells(r, 1) = Mid(txt, s, i - s)
            r = r + 1
            b = 0
        
        End If
    
    Next i
 
Loop
 
Close #1
 
End Sub

This works perfectly, however it doesnt seem to grab the right numbers? where does this algorithm start? on the very first data entry, or the 11th?

It also seems to get messed up when it comes to the end of the row, and does 12 skips instead of 11, so it works, but once the sequence jumps down a row in the matrix, it skips 12 data points not 11

 

[crimson_b1ade]

it starts on the 11th.

can you perhaps provide a sample file with fake data (use a free file sharing site and post link here). Also, provide the expected results based on the sample file.

 

[mortonm]

https://spreadsheets.google.com/ccc...zZKVC14M0VZSzZCY3ZoSkE&hl=en&authkey=CO2vz9oN

There is a sample file with some fake data. I highlighted those whcih I would like to keep. As you can see as you work towards the end of the row, eventually it will skip and entire row. this is where with the previous code it was taking the 12th number, and then back to every 11.

Thank you

 

[mortonm]

bump

anymore info?

Thanks

 

[ColinKJ]

Hi mortonm,

If you got back to my post 21st April, I just ran that code on your example data, and it works fine:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=64 align=right x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>44</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>55</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>66</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>77</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>88</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>99</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>110</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>121</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>132</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>143</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>154</TD></TR></TBODY></TABLE>

This is the results I get in Column A.

Those are the numbers you highlighted in your example data.

 

[mortonm]

I must not have seen that! sorry

I have updated the sample data file to the first 50 rows of data, exactly as it looks when imported from the raw data.

I have highlighted again the data that i need (the first piece is actually the 5th in, then every 11).

I dont quite seem to get exactly what I want.

Does yours pull from the very first entry or? As you can see there is a bit of info at the top about the time, could this be affecting it?

many thanks

Mike

 

[mortonm]

Okay,

so it works beautifully, the only difference I would like is to start on the fifth item in the first row, and not the first

I.E. Element (5,1) not (1,1)