Move data in cells araound

[mikebres]

Hi All, I have some data in an unfriendly format. It's organized like this:

xxxyyyzzzaaabbbcccdddeeefff
ggghhhiiijjjkkklllmmmnnn
ooopppqqqrrr

I'm looking for a way to reorganize it so it its more like this:
xxx
yyy
zzz
aaa
bbb
etc.

I will be having to do this on a weekly basis. So I'm looking for a
repeatable way to do this.

Does anybody have a good suggestion on how I would accomplish this?

Thanks
Mike

 

[Gord Dibben]

Is the data all in one cell in the format you show?


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Hi All, I have some data in an unfriendly format. It's organized like this:

xxxyyyzzzaaabbbcccdddeeefff
ggghhhiiijjjkkklllmmmnnn
ooopppqqqrrr

I'm looking for a way to reorganize it so it its more like this:
xxx
yyy
zzz
aaa
bbb
etc.

I will be having to do this on a weekly basis. So I'm looking for a
repeatable way to do this.

Does anybody have a good suggestion on how I would accomplish this?

Thanks
Mike

If the data is all in one cell (e.g. A1), and if the "defining characteristic"
is that it is in three-character groups that need to be split out, then

A2: =MID(SUBSTITUTE($A$1,CHAR(10),""),(ROWS($1:1)-1)*3+1,3)

and then fill down as far as required.

If the data setup and/or requirements are different, you'll have to be more
specific.
--ron

 

[mikebres]

No, it's 18 cells across and three cells down. Also, it won't always be in
the same cells, but it will be the same deminsions 3x18.

I was ttrying to use a macro with arrays, but I couldn't get my head around
transferring the data from a (3,18) array to a (15,3) array.

Mike

 

[Ron Rosenfeld]

No, it's 18 cells across and three cells down. Also, it won't always be in
the same cells, but it will be the same deminsions 3x18.

I was ttrying to use a macro with arrays, but I couldn't get my head around
transferring the data from a (3,18) array to a (15,3) array.

Are you sure that's what you want to do?

A 3x18 array has 54 elements
A 15x3 array has 45 elements

How do you want to handle the nine element difference?
--ron

 

[mikebres]

I was just keeping the deminsions uniform, the source data doesn't fill all
the cells. The nine extra cells are blank, so I would have three blank lines
at the bottom.

However, if you have a better way I would certainly be interested...

Mike

 

[Ron Rosenfeld]

I was just keeping the deminsions uniform, the source data doesn't fill all
the cells. The nine extra cells are blank, so I would have three blank lines
at the bottom.

However, if you have a better way I would certainly be interested...

Mike

It is still not clear to me exactly what your layout is, or your desired
results.

In your original post, it appeared as if you wanted three letters in one cell.

From this latest post, it seems you want three cells.

If all you want to do is change an 18x3 array to a 3x18 array, merely

1. Select your original array
2. Edit/Copy
3. Select a destination cell
4. Edit/Paste Special/Transpose

If you want something different, you'll need to be more specific
--ron

 

[mikebres]

Okay,

The data is laid out in cells. One number to each cell. Each group of 3
numbers left to right are a set. A Total, Success, Percent set. They are 18
cells across and 3 cells down with the last nine cells on the 3rd row empty.
The representation below shows the actual data with the | as an indication of
where each cell starts and ends. I chopped off the last couple of cells in
the reprsentation below to avoid the word wrap.

62 | 55 | 89 | 118 | 103 | 87 | 61 | 56 | 92 | 78 | 63 | 81 | 59 | 48 | 81 |
34 |
91 | 79 | 87 | 55 | 52 | 95 | 63 | 57 | 90 | 81 | 69 | 85 | 98 | 90 | 92 |
106 |
48 | 46 | 96 | 92 | 82 | 89 | 1046 | 926 | 89 | | | | | | | | |

I'm looking for a way to get this data into nice organized columns of
Total, Success, and Percent like this.

|Tot | Suc| %Suc |
| 62 | 55 | 89 |
| 118 | 103 | 87 |
| 61 | 56 | 92 |
etc.

 

[kounoike]

Assuming your source data starts at A1, put the formula below into the first
Cell where you want to tranfer data.
then, copy it three cells left and again copy that range down to where you
want.
if your data start elsewhere, change the first arguments in OFFSET, in this
case $A$1, to the cell in your case.

=OFFSET($A$1,((3*ROW(A1)+COLUMN(A1)-4)/18),MOD((3*ROW(A1)+COLUMN(A1)-4),18))

if you prefer using a macro to a formula, try this one.
Assuming your source data start at A1 in Activesheet, and destination starts
at A2 in the worksheet named Sheet2.
if this is not your case, change the code Set start = Cells(1, "A") and Set
dst = Worksheets("Sheet2").Cells(2, "A") below according to your case.

Sub mytest()
Dim start As Range
Dim tmp As Range
Dim dst As Range
Const count = 3 'Number of Cells in one set
Const columncount = 18 'Number of columns in a row

'start is a first Cell where your first data in a group is populated
Set start = Cells(1, "A")
'dst is a first Cell where your data in a group is transfered
Set dst = Worksheets("Sheet2").Cells(2, "A")

Do While (start <> "")
Set tmp = start
For i = 1 To columncount / count
dst.Resize(1, count) = tmp.Resize(1, count).Value
Set tmp = tmp.Offset(0, count)
Set dst = dst.Offset(1, 0)
Next i
Set start = start.Offset(1, 0)
Loop
End Sub

keiji

 

[Ron Rosenfeld]

Okay,

The data is laid out in cells. One number to each cell. Each group of 3
numbers left to right are a set. A Total, Success, Percent set. They are 18
cells across and 3 cells down with the last nine cells on the 3rd row empty.
The representation below shows the actual data with the | as an indication of
where each cell starts and ends. I chopped off the last couple of cells in
the reprsentation below to avoid the word wrap.

62 | 55 | 89 | 118 | 103 | 87 | 61 | 56 | 92 | 78 | 63 | 81 | 59 | 48 | 81 |
34 |
91 | 79 | 87 | 55 | 52 | 95 | 63 | 57 | 90 | 81 | 69 | 85 | 98 | 90 | 92 |
106 |
48 | 46 | 96 | 92 | 82 | 89 | 1046 | 926 | 89 | | | | | | | | |

I'm looking for a way to get this data into nice organized columns of
Total, Success, and Percent like this.

|Tot | Suc| %Suc |
| 62 | 55 | 89 |
| 118 | 103 | 87 |
| 61 | 56 | 92 |
etc.

OK, that's much more clear than your initial post.

I am assuming that the data really IS 18 cells across, as I am only seeing 16
in what you've posted. But you can always change the formula below
appropriately.

1. I NAME'd the data table Tbl.
2. In some cell, enter this formula:

=INDEX(Tbl,INT((ROWS($1:1)-1)/18)+1,MOD(ROWS($1:1)-1,18)+1)

Fill down as far as required. You'll start to get #REF errors when you have
gone far enough. If that will be a problem, you can always do a TEST to
prevent it:

=IF(ROWS($1:1)>COUNTA(Tbl),"",
INDEX(Tbl,INT((ROWS($1:1)-1)/18)+1,MOD(ROWS($1:1)-1,18)+1))


--ron