How do I repeatedly transpose blocks of data?

I have blocks of data that are all in colomn A and the blocks are 27 rows in 
length.  I want to transpose this data into a database format.  I've written 
the formula to do 1 line but I need to be able to copy this formula down so 
that it moves on 27 rows instead of the 1 that it normally does when you copy 
down formula. There is over 1000 blocks of data to transpose. I'm using the 
2000 version.
0
LucyW (2)
8/1/2005 10:17:04 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
256 Views

Similar Articles

[PageSpeed] 18

If I read this correctly, you wish to move A1 to A27 to C1 to AC1, an
then repeat this process for each 27 rows so that A28 to A54 move to C
to AC2 etc.

One (slow) way to do this is to number column B (put 1 in B1, then hol
the ctrl-key and formula-drag this down the B column to the end of you
data), it should sequence equal to the row number.

In C1 put 
=INDIRECT("$A"&(($B1-1)*27+1))
and formula drag this to cell AC1, then increment the last numbers s
that cell D1 reads
=INDIRECT("$A"&(($B1-1)*27+2))
and on up to AC1 to read
=INDIRECT("$A"&(($B1-1)*27+27))

Select C1 to AC1 (highlight all 27 cells) and formula-drag down 1,00
rows (plus or minus, according to your estimate)

Then copy to a new sheet and paste-special, values, and remove column
A and B

Hope this helps


LucyW Wrote:
> I have blocks of data that are all in colomn A and the blocks are 2
> rows in
> length.  I want to transpose this data into a database format.  I'v
> written
> the formula to do 1 line but I need to be able to copy this formul
> down so
> that it moves on 27 rows instead of the 1 that it normally does whe
> you copy
> down formula. There is over 1000 blocks of data to transpose. I'm usin
> the
> 2000 version

--
Bryan Hesse
-----------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2105
View this thread: http://www.excelforum.com/showthread.php?threadid=39176

0
8/1/2005 12:48:12 PM
Have you tried to use the TRANSPOSE function. You can view how to use the 2 
transpose methods available in Excel on 
http://www.auditexcel.co.za/lookupandreference.html . 

"LucyW" wrote:

> I have blocks of data that are all in colomn A and the blocks are 27 rows in 
> length.  I want to transpose this data into a database format.  I've written 
> the formula to do 1 line but I need to be able to copy this formula down so 
> that it moves on 27 rows instead of the 1 that it normally does when you copy 
> down formula. There is over 1000 blocks of data to transpose. I'm using the 
> 2000 version.
0
AdrianM (30)
8/1/2005 1:07:02 PM
Thanks Bryan - that's exactly what I was looking for.

Just made about 20 hours of work whittle down to one.

Many thanks

Lucy

"Bryan Hessey" wrote:

> 
> If I read this correctly, you wish to move A1 to A27 to C1 to AC1, and
> then repeat this process for each 27 rows so that A28 to A54 move to C2
> to AC2 etc.
> 
> One (slow) way to do this is to number column B (put 1 in B1, then hold
> the ctrl-key and formula-drag this down the B column to the end of your
> data), it should sequence equal to the row number.
> 
> In C1 put 
> =INDIRECT("$A"&(($B1-1)*27+1))
> and formula drag this to cell AC1, then increment the last numbers so
> that cell D1 reads
> =INDIRECT("$A"&(($B1-1)*27+2))
> and on up to AC1 to read
> =INDIRECT("$A"&(($B1-1)*27+27))
> 
> Select C1 to AC1 (highlight all 27 cells) and formula-drag down 1,000
> rows (plus or minus, according to your estimate)
> 
> Then copy to a new sheet and paste-special, values, and remove columns
> A and B
> 
> Hope this helps
> 
> 
> LucyW Wrote: 
> > I have blocks of data that are all in colomn A and the blocks are 27
> > rows in
> > length.  I want to transpose this data into a database format.  I've
> > written
> > the formula to do 1 line but I need to be able to copy this formula
> > down so
> > that it moves on 27 rows instead of the 1 that it normally does when
> > you copy
> > down formula. There is over 1000 blocks of data to transpose. I'm using
> > the
> > 2000 version.
> 
> 
> -- 
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
> View this thread: http://www.excelforum.com/showthread.php?threadid=391764
> 
> 
0
LucyW (2)
8/1/2005 2:56:01 PM
Reply:

Similar Artilces:

Xml space and ms-data interpretation different in VS2005
I have a problem converting a program from Vb.net VS2003 to VS2005. I process a xml-file with an inline schema. In 2003 no problem. In 2005 I have 2 problems: 1) BIG space="preserve" problem. Suddenly the spaces are a problem during processing (The following text is not allowed in this context: ' '). Without the space="preserve" (or default) it works okay, but what to do now since I can't influence the dataformat. 2) SMALLER xml-msdata/IsDataSet problem. As long as there is a reference to the xml-msdata namespace in the file the VS2005 designers can't cor...

Sales Data .bck file & how to append it to newly created file
Through a series of events too distressing to go into here we seem to have lost a .bck file created from exisiting sales history when we needed to re-install RMS on our office server. What we do have is sales data for the past few weeks, and a .bck file from 3 months ago. The question is how we would go about integrating the 3 month old .bck file into the current data in RMS. Our RMS guy is not sure how to do it, or if it can be done. If integration is not possible, is there a way to substitute the older file (a year's sales) for the newer data (only a month)??? -- WineGuy This...

User data folder location change
Lots of post here on MS office user data folder, but still nothing on how to change the location of this folder. I do not want to see it in my ~/documents folder. It doesn't belong here. Either in ~/ or in ~/Library/ApplicationSupport. I agree that it would be better placed in the Library folder, but unfortunately we need to live with it in the Documents folder. You could send your suggestion to Microsoft so that they'll consider it for a future release. In any Office X application, go to Help -> Send Feedback. Otherwise, click on this direct link to enter Mac Product Feedback: &...

How to convert contents of column from numeric data type to text
you know how sometimes when you paste a value into a cell it reads like a number but Excel thinks its text and aligns it to the left and gives you a little dropdown menu with options such as keep this value as text or to convert it to numeric data. I have a whole column of mostly numbers (and excel is storing them as numeric data) and want to convert them to text. I don't have that menu option available since excel thinks they're numbers. Is there a way to convert numeric data to text? Thanks. -- moondaddy@noemail.noemail If you don't mind a helper column, then you c...

Data validation input message
Can anyone please offer any advice? ..... I am using data validation on a worksheet and would like to resize the message box that appears when the cell is selected. Can this be done in Excel either using an option or by using some VBA? Many thanks, DT. I'm not sure what message appears when the cell is *selected*. Are you referring to the error message that is triggered by an invalid entry? If so, AFAIK, there is no way to modify it. -- Vasant "DT" <dt0504@[cheatthespam]hotmail.com> wrote in message news:40e5e8d6$0$6441$cc9e4d1f@news-text.dial.pipex.com... > Can...

transpose unknown file
I need to create a macro where the user is first prompted to select th file path and then a range from the selected file is copied or inserte in to another workbook. I am guessing that I need to us "getopenfilename" and "transpose" in some combination but I have n idea how to do that. Any suggestions? -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Sub testme01() Dim myFileName As Variant Dim RngToCopy As Range Dim RngToPaste As Range Dim wkbk As Workbook Set RngToPaste = Nothing On Error Resum...

report on blocked messages
I'm blocking a few senders in and out via sender filtering and delivery restrictions on the SMTP internet mail connector. Is there a way to report on these blocks or have the messages redirected somewhere? I am sure SMTP virtual server logs contain such information. You can either extract it manually or with help of some tool. knightly wrote: > I'm blocking a few senders in and out via sender filtering and delivery > restrictions on the SMTP internet mail connector. Is there a way to report on > these blocks or have the messages redirected somewhere? > -- Text fr...

Import data for custom entity
I have created a custom entity called "CANDIDATES". The import utility apparently only allows me to import data to Contacts and Accounts. How do I go about importing data for my new Entity? I assume I need to script it directly in SQL? I tried, but couldn't get it to work. Any help appreciated. Thanks. you cannot use the Import tool for custom entities. you need to develop custom app using the SDK. you can't even use the Data Migration Framework for custom entities :) "Bill Wallace" <ww@braveheart.net> wrote in message news:OGObu8v7HHA.536@TK2MS...

Data to transfer from column to row #2
Dear All, I have a cell that contains like: PM-1234, PM-2345, PM-5689, PM-9999. I want to segregate these 4 tag into rows. i.e., PM-1234 PM-2345 PM-5689 PM-9999 Is there any excel guru out there to help in my problem??? Your help is greatly appreciated. Thank you, qwerty7 -- qwerty7 ----------------------------------------------------------------------- qwerty70's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3267 View this thread: http://www.excelforum.com/showthread.php?threadid=52474 If you highlight the four cells containing the data, then click <...

Edit table data automatically, with InStr
Okay I have my data being automatically imported to a table (TableNew). One of my fields is Called "File Name." For the new data imported, the layout of the information in this field is example: John.Pictures or Maryam.Videos. After the data is imported, I want the code to go through the data in this field and remove anything after the decimal point, including the decimal point. So the "File Name" for the examples above should be "John" or "Maryam". I know this involves the InStr however I am not sure how exactly to use it. Could someone provide the corr...

Data Migration Framework Tools Installation Problems
To whom it may concern: Recently our organization was preparing for an ACT! to CRM migration and had a slight problem getting the Data Migration Framework Tools installed for Microsoft CRM v1.2. We kept getting an error that said something to the effect of "The installation program was interrupted before setup could complete." Upon further deliberation, we discovered a extremely low tech reason for the error........the DMF has to be installed off of the CD and not from a MSI package copied to the hard drive. *slaps forhead* DOH! ...

imported customer data not showing up
After import was done I went into Rms but could not find the customers. What am i doing wrong. This is becoming a nightmare. Did you have errors when importing? Did you include a unique Account Number for each Customer? Did any customers show up? Rob "country" <country@discussions.microsoft.com> wrote in message news:74E7CD37-AD87-4C36-A5AE-6966D4147838@microsoft.com... > After import was done I went into Rms but could not find the customers. > What > am i doing wrong. This is becoming a nightmare. ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Choice of Data system
Hi, I'm a student and I'm currently developping a backup application in MFC (C++). My app basically consists of a Treeview and a listview control which holds all the files I want to back up and the ones that have already been backedup. My problem : which is the best solution for saving my data (treeview structure with files info ...). I think that loading the whole tree structure (and file list and info...) would take too much memory. I thought of using a database to store all my data. What do you think ? I need to be able to quiclky search for data, for example when the user select...

Can you show data in a data table but not plot it on the chart?
I need to be able to include a total column in a data table on the stacked chart I'm doing but not plot it on the chart itself. Is this possible and how? Armadillo, Unfortunately you can't add totals to a chart-generated data table. However, with a little formatting work you can create a data table with worksheets cells. Please see this site for an example: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=509 ---------------------------- Regards, John Mansfield http://www.pdbook.com "Armadillo" wrote: > I need to be able to include a total column...

Running macro in Access to extract data from Excel
Is there a way to create a macro that will perform these actions. 1.) Open an excel file 2.) Extract data from cells in the excel file and put them in text boxes on a form in Access 3.) Save the data on the form to a table 4.) Close the excel file On Mon, 10 Mar 2008 13:33:03 -0700, Ayo <Ayo@discussions.microsoft.com> wrote: >Is there a way to create a macro that will perform these actions. >1.) Open an excel file >2.) Extract data from cells in the excel file and put them in text boxes on >a form in Access >3.) Save the data on the form to a table >4.) Close the ...

Data Validation enter values with VBA
I currently have a Spreadsheet running a tipping competition which works well, however would like to add additional functionality to it. I have cells setup which are data validated to a list on the sheet and have four values to select from which are None, Team 1, Draw, Team 2 These come up in a drop down box. What I would like to be able to do is to have the data filled automatically in a group of cells dependent on the value in another cell. For example In Cell A10 i put in H and Cell F10 which has the data validated list always has the value None to start with the l...

Block one mail account only have receiving internal mail function.
Hello all, Is there any method to block a specific mail account to only have receiving internal mail function ? Need help soon. Thanks Try the deny "send as" option Under the user properties in ADUC>security tab>deny send as not sure if it will work,havn't tried it myself In news:2FF2CDB4-C104-4F9B-A99B-F83488A5B108@microsoft.com, Enid <Enid@discussions.microsoft.com> typed: > Hello all, > Is there any method to block a specific mail account to only have > receiving internal mail function ? Need help soon. Thanks See if http://www.msexchange.or...

updating chart source after transposing rows
Hi all I have a spreadsheet which has approx 30 different charts with it. I have been asked to find out if there is any way in which after transposing 3 rows of data into columns that the charts can automatically update (which would save me ages in resetting the source data locations) please? Any help greatly appreciated. ...

Avoid repeating UserId and Password prompt while send/Recv in Out.2K
Hai ! Avoid repeating UserId and Password prompt while sending/Recving in Outlook 2K Regards, Kishore ...

consolidating data for a report - HELP
Hi The spreadsheet shows people on up to 20 differant projects with each project have 2 colums of data i.e. BY and BZ. My example cuts it down to 4 columns and 7 rows as below i.e. A | B | BY | BZ Project Name Fred Rate 400 B 1 400 S 0.5 200 X ...

2 VS_VERSION_INFO blocks in rdpwd.sys
Hello and Happy Holidays to all... I need to read information from the VS_VERSION_INFO block without the use of Windows APIs (such as GetFileVersionInfo()). I've had this working for quite some time now, but I've recently run into a case where my results do not agree with the Windows Explorer Version Dialog. I get a miss on C:\WINDOWS\system32\drivers\rdpwd.sys from a fully-patched S03,SP1. In reading the file in a hexeditor, the file contains (at least) 2 VS_VERSION_INFO blocks. My results reflect one block while WinExplorer pulls information from the other. What are the rules r...

Replacing spaces with data from another cell
Hello, I'm trying to create some tracking URLs and I am was hoping that there was an easier way to do this. I have keywords that vary between 2 and 5 words long. I need to enter unique code before, after and in between each keyword. I.E. Apples Oranges Bananas Pears = code1applescode2orangescode2bananascode2pearscode3 . Is there an easy way I can create this with a formula? Thank you for your help, Assuming your text is in A1, try this: =3D"code1"&SUBSTITUTE(A1," ","code2")&"code3" and then copy down as requ...

statistical data exel templates
where can i find those? http://members.aol.com/johnp71/javasta2.html "marcelle" wrote: > where can i find those? ...

0x80040356...the source data is not in the required format
Hi I am importing "products" data into CRM 4.0 through a CSV file through Data Import wizard. I am getting following error message while importing the data "0x80040356 the source data is not in the required format" Sincerely Hussain Ali just a tips that you should try to import few rows (like 10) in order to grab the error. ------ Aamir Blog = http://mscrmsupport.wordpress.com/ On Feb 19, 4:24=A0am, "HussainAli" <huss...@discussions.microsoft.com> wrote: > Hi > > I am importing "products" data into CRM 4.0 through a CSV file...