Transposing data

[jacqui]

I have a datafile where
column 6 contains balances and column 1 contains a
reference to each of those balances. The reference is a
four digit code but is prefixed with a number and sorted
in numeric order, ie 1AFL, 2BPB, 3PLB, 4ABM etc. There
are several rows (of balances) that relate to each
reference.
(There are other details in columns 2 to 5 but these are
not relevant in this example).
Can anyone kindly provide some syntax that will:
for each group of balances ie all those that begin with
ref 1 in column 1, grab column 6 and move it to the next
available column, then repeat for all those with ref 2 in
column 1, grab column 6 and copy to the next available
column.
What I'm essentially trying to do is transpose a file
where all the data is held in rows (3000 in total) and I'm
wanting to sort sections of it and paste it to the next
available column.
Does this make any sense?
Many thanks
Jacqui

 

[Tom Ogilvy]

if you do it for each reference, won't you move all of column 6 to a new
column?

 

[jacqui]

No because I would like to copy blocks of data to the next
spare column based on when the reference changes. I will
be using autofilter as well but I'm ok with coding that.
What I'm trying to do is mark a section of data by using
the reference as an identifier. There might be 100 rows
associated with reference 1AFL, so I'd like to grab the
corresponding balances in column 6 that relate to that
reference. Then I'd like to identify the next block of
data that begins with a 2 series reference and isolate
those balances and cut/paste them across to the next spare
column and so on. The references work through from 1 to
8. They contain alpha characters as mentioned previously
but the one thing that keeps them in sequential order is
this first character being a number. Would it be easier
if I sent you a simple file?
Many thanks
Jacqui

 

[Tom Ogilvy]

Sub AABTester1()
Dim i As Long, j As Long, k As Long
Dim rng As Range, cell As Range
Dim sChar As String

For i = 1 To 256
If Application.CountA(Columns(i)) = 0 Then
j = i
Exit For
End If
Next
For i = 1 To 8
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
sChar = Left(cell, 1)
If IsNumeric(sChar) Then
k = CLng(sChar)
If k = i Then
Cells(cell.Row, j).Value = Cells(cell.Row, 6).Value
' cells(cell.row,6).ClearContents
End If
End If
Next
Next
End Sub

should be a start. Test it on a copy of your data.

 

[jacqui]

Tom,

Thanks for this, just one slight design flaw which I
didn't mention. In the loop where For i = 1 to 8, when i
= 2 to 8, I would like the values in the column to sit
beside the values from the previous repetition, ie start
at row 2 again, not appear in the row opposite the
reference number, ie 2FPB starts on row 49 and the macro
has inserted the values in the empty column beside this in
column 6 row 49. Whereas what I'd intended is column 7,
row 2. Likewise when i = 3, the values goto column 8 row
2 again and so on.
Does this make sense?
Many thanks
Jacqui

 

[Tom Ogilvy]

Sub AABTester1()
Dim i As Long, j As Long, k As Long
Dim rng As Range, cell As Range
Dim sChar As String

For i = 1 To 256
If Application.CountA(Columns(i)) = 0 Then
j = i - 1
Exit For
End If
Next
For i = 1 To 8
j = j + 1
rw = 2
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
sChar = Left(cell, 1)
If IsNumeric(sChar) Then
k = CLng(sChar)
If k = i Then
Cells(rw, j).Value = Cells(cell.Row, 6).Value
' cells(cell.row,6).ClearContents
rw = rw + 1
End If
End If
Next
Next
End Sub

Should do what you describe.

 

[jacqui]

Tom

Thank you for your help. Much appreciated.

Jacqui