Move data in cells araound

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
0
mikebres (18)
3/5/2008 1:01:01 AM
excel 39879 articles. 2 followers. Follow

9 Replies
622 Views

Similar Articles

[PageSpeed] 18

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


Gord Dibben  MS Excel MVP

On Tue, 4 Mar 2008 17:01:01 -0800, mikebres <mikebres@discussions.microsoft.com>
wrote:

>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

0
Gord
3/5/2008 1:07:35 AM
On Tue, 4 Mar 2008 17:01:01 -0800, mikebres
<mikebres@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3122)
3/5/2008 3:12:33 AM
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

"Gord Dibben" wrote:

> Is the data all in one cell in the format you show?
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Tue, 4 Mar 2008 17:01:01 -0800, mikebres <mikebres@discussions.microsoft.com>
> wrote:
> 
> >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
> 
> 
0
mikebres (18)
3/5/2008 5:08:01 PM
On Wed, 5 Mar 2008 09:08:01 -0800, mikebres
<mikebres@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3122)
3/5/2008 8:17:47 PM
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" wrote:

> On Wed, 5 Mar 2008 09:08:01 -0800, mikebres
> <mikebres@discussions.microsoft.com> wrote:
> 
> >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
> 
0
mikebres (18)
3/5/2008 8:45:02 PM
On Wed, 5 Mar 2008 12:45:02 -0800, mikebres
<mikebres@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3122)
3/5/2008 9:15:18 PM
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.


0
mikebres (18)
3/5/2008 10:17:01 PM
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

"mikebres" <mikebres@discussions.microsoft.com> wrote in message 
news:E4D215B1-2FD0-4EB6-AF43-62DE4484786E@microsoft.com...
> 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.
>
> 

0
kounoike1 (60)
3/6/2008 7:21:39 AM
On Wed, 5 Mar 2008 14:17:01 -0800, mikebres
<mikebres@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3122)
3/6/2008 7:32:30 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Outlook rule to move read message from Blackberry
I have Outlook configured with a Blackberry server and want to set up a rule to mange messages marked as read: For example. 1. I'm away from the office, Outlook is closed. 2. I read an email on my Blackberry, which marks the message "read" 3. When I return to the office and open Outlook I want to: a. Apply a rule to my inbox that states: If message is marked as read, move to folder "read messages" b. All unread messages will remain in the inbox. I'm a heavy user of rules but I can't find a way to identify "read" messages. Any thoughts? view360@gm...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

Disappearing data in sync'd forms
I have a small sized text box in a form called frmMain. This text box may or may not contain a large amount of text. If the text box does contain alot of text I want to open up a new form called frmLargeText that contains a larger text box to allow the user to easily see and edit the large amount of text. I also need the two forms to stay in sync. i.e. if the user moves to a new record then both forms move to the same record. I thought I could do this by setting the recordset of frmLargeText to equal the recordset of frmMain as follows: Dim frm as Form_frmLargeText Dim rst As DAO.Rec...

Calculating Subsets of Data
I have data for records that can span between 1 to 16 months.. I want to be able to calculate the average activity for the most recent six months in a query and be able to display that in a report. I can create the calculation in the query for the entire duration of months for each record but am stuck on how to have it choose only the most recent six months of data to do the calculation and understand that the six months may really only be between 1 to 6 months of data.. Any suggestions would be greatly appreciated..My skill level is somewhere between meatball surgeon and ki...

Moving exchange 5.5 to a new server exchange 2003
Good day all... how do I move exchange 5.5 to a brand new exchange server 2003. The new server will be in a new organization. Any info would be greatly appreciated. Thanks, Ray http://www.microsoft.com/usa/webcasts/ondemand/2340.asp http://support.microsoft.com/default.aspx?scid=fh;en- us;exch2003 Patrick Rouse Microsoft MVP - Terminal Server >-----Original Message----- >Good day all... how do I move exchange 5.5 to a brand new >exchange server 2003. The new server will be in a new >organization. Any info would be greatly appreciated. > >Thanks, >Ray > >. ...