A spin on the old TRANSPOSE question

A "search" turns up dozens of repetitions of the same question: "How d
I convert a column of data into a row of data?"  Easy: Copy, Past
Special, Transpose.  But how about for a column of repeating data?

E.g.:

A
B
C
D
A
B
C
D
A
B
C
D and so on, to be converted to:

A B C D
A B C D
A B C D, continuing.

I recorded a quick macro to copy and paste special (transpose), but i
kept recopying the original copied data.  Manually copying and pastin
is going to take way too long.  Is there a formulaic method of doin
this?

Thanks!:

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/24/2003 2:16:28 PM
excel 39879 articles. 2 followers. Follow

1 Replies
633 Views

Similar Articles

[PageSpeed] 31

If your data is in A1:Axxx, then you can put this formula in B1 and drag across
to E1:

=INDEX($A:$A,(ROW()-1)*4+COLUMN()-1)

Then drag down until you run out of data.

Then copy|paste special|values and delete column A.



Drewski wrote:
> 
> A "search" turns up dozens of repetitions of the same question: "How do
> I convert a column of data into a row of data?"  Easy: Copy, Paste
> Special, Transpose.  But how about for a column of repeating data?
> 
> E.g.:
> 
> A
> B
> C
> D
> A
> B
> C
> D
> A
> B
> C
> D and so on, to be converted to:
> 
> A B C D
> A B C D
> A B C D, continuing.
> 
> I recorded a quick macro to copy and paste special (transpose), but it
> kept recopying the original copied data.  Manually copying and pasting
> is going to take way too long.  Is there a formulaic method of doing
> this?
> 
> Thanks!:D
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/24/2003 2:35:32 PM
Reply:

Similar Artilces:

Move old emails or emails with large attachments ?
Hello ! I need help in figuring out how to edit the code below (which works) to move SENT items to a separate personal folder called out here. I want to move emails that were sent 90 days ago or older to the personal folder. I also want to move emails that contain attachments that have a size greater than 5 MB. This code will run when called by the user from a Module. I have not figured out yet how to code an IF statement for Outlook yet much less one that requires an OR operator as well. I am using Outlook 2007. Can anyone provide direction for me in this ? thanks ! ...

2008 SBS questions... 11-16-09
Ok, spent most of the day setting up my first 2008 SBS server. So far, so good, except for a couple of problems. 1) It seems that DHCP clients can't get out to the internet. According to ipconfig on the client, they are getting all the right numbers & I have no problem getting to internet from the server. What am I missing? 2) I have a Panasonic DP-C354 network printer that I have shared out, added the XP 32-bit drivers to it & I can't get the clients to print. The printer appears to install ok, but when trying to pring test page, it clears the client with no error mes...

Sales license and Cases question
Hi there! I have a User with a single Sales license. It seems that he can see the Cases under an Account and he can receive activities regarding Cases, but cannot Complete them. Does it makes sense? How "far" a Sales user can go with Cases? Thanks a lot, Elena Verify the roles >-----Original Message----- >Hi there! >I have a User with a single Sales license. It seems that he can see the >Cases under an Account and he can receive activities regarding Cases, but >cannot Complete them. Does it makes sense? How "far" a Sales user can go >with Cases? ...

Questions re: RPC over HTTPS
Hi, I have a few questions about RPC over HTTPS. We're running Windows 2003 Standard SP1 with Exchange Standard SP2 on a FE / BE configuration. The FE services OWA on port 443. 1. Can RPC over HTTPS be configured on the same FE Server? I'm assuming that it can but would need to use a different port. Our Firewall is using port 10443 for VPN access and OWA port 443. Consequently, can OWA or RPC over HTTPS be moved easily and without issues to a different port? If so, are there any recommendations for alternate ports? 2. Can we use the same Web Certificate for RPC/HTTPS t...

Split Window question
User is working on a Workbook. They need to reference another workbook. I wish to split the screen and display this new book alongside the original workbook in such a way that they can scroll through the new sheet (or change sheets) but still keep the original workbook displayed alongside. I'm going to call this new book from addin code, so if you could also show me how to size the Windows I'd be very grateful. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/...

Sort Question
Can you sort a cell by color? What version of excel are you using? dwake wrote: > > Can you sort a cell by color? -- Dave Peterson EXCEL 2007 Data / Sort & Filter group / Sort / Sort window should launch / in the Sort On field select Cell Color If my comments have helped please hit Yes. Thanks. "dwake" wrote: > Can you sort a cell by color? Not naturally, but see here for workaround: http://www.ozgrid.com/VBA/Sort.htm -- Best Regards, Luke M "dwake" <dwake@discussions.microsoft.com> wrote in message ne...

Calculating How Old Someone Will Be in a certain year
I have a list of dates (birthdays & Anniversaries) in the dd/mm/yyyy format down a column, with the years in the format yyyy across the first row. I want a formula that will calculate the age of the person or anniversary by using just the year part of the date and comparing it with the year across the first row of the spreadsheet. TIA Take a look at the picture: http://img509.imageshack.us/img509/346/nonamer.png Micky והמשך/י, נא, לקרוא את השורה הבאה: *********** אם תגובתי עזרה לחץ/י, נא, על <כן> בפס האופקי התחתון! *********** מיכאל אבידן מנהל פורום "אופיס"...

hard slope question
let see if you can answer me this question..... I have 4 columns COLUMN A: Dates (1/15/2009) COLUMN B: Time (1:35:24 AM) COLUMN C: Dates combined with time as format " dd/mm/yyyy hh/mm " and shown as number: ( 40035.01 ) this would be the "X-axis data" COLUMN D: " Y-Axis Data " Excell sheet "1" A B C D 1/15/2009 1:35:24 AM 40035.01 1234 1/15/2009 2:15:20 AM 40035.11 2345 1/15/2009 3:24:31 AM 40035.26 2456 1/16/2009 1:15:20 AM ...

A spin on the old TRANSPOSE question
A "search" turns up dozens of repetitions of the same question: "How d I convert a column of data into a row of data?" Easy: Copy, Past Special, Transpose. But how about for a column of repeating data? E.g.: A B C D A B C D A B C D and so on, to be converted to: A B C D A B C D A B C D, continuing. I recorded a quick macro to copy and paste special (transpose), but i kept recopying the original copied data. Manually copying and pastin is going to take way too long. Is there a formulaic method of doin this? Thanks!: ----------------------------------------------- ...

Outlook Reminder Question
In Outlook 2003 when I open an appointment that has a contact associated with it, I am unable to click to open the associated contact listed next to the "Contacts" button at the bottom, as the contact is grayed out. Is there a way to fix this? ...

REPOST!!! 2 quick questions
Hello All, I have two questions. I currently have a 2003 Exchange Server on my network. The first question I have is this. We use a public folder for an e-mail box for a sales e- mail address. Users can go in a check to see if there are any messages thru out the day. I have cleared the "Maintain per user read info" box, but when someone checks the e-mail and reads it, no one else sees it as read. Second question is this. I have a user with Windows XP Pro and Outlook 2003. We use public folder calendars for appts. for our executive staff. When this user posts an appt, it is...

DVD drive spinning up
How can I prevent the DVD drive pinning up each time when NOT in use?? In configuration I have already disabled all auto run options! Or is the only remedy taking out the disk each time? -- regards, |\ /| | \/ |@rk \../ \/os On 5/28/2010 10:17 AM, Linea Recta wrote: > How can I prevent the DVD drive pinning up each time when NOT in use?? > In configuration I have already disabled all auto run options! > Or is the only remedy taking out the disk each time? Well it spins up even without a disc in the drive actually. It is just quieter and it realizes pret...

Question on Mailbox Limits
If mailbox limits are set for users at, let's say 200MB, what happens to mail trying to be delivered to a user who is at or above the 200MB? We have users with ridiculous mailbox sizes, and I'm trying to force people to archive or delete so we don't hit the 16GB limit (again). If a user is already above the limit, I want to be sure they won't lose any e-mail, or that the system won't just delete e-mail that's above the 200.... Thanks- Rob if you prohibit send and receive at 200MB, mail will be rejected when the mailbox reaches 200MB... "Rob" <99nightha...

Sorting question #3
Excel 2003 I am working on a spreadsheet at work and would like to be able to change the sorting order easily. There are two columns I'd like to switch around. Column 1 is a list of part designators. They can begin with 1W, 2W, or 3W and are followed by up to a 3 digit number. When I try to sort now, it puts 1W10 before 1W2. I *cannot* change the designator to 1W002. Is there a way to format that column so that it will yield 1W2 before 1W10 (followed by 2Wnnn and 3Wnnn). Column 2 are part numbers that are based on part drawing numbers. The drawing numbers are generally an 8-digit ...

VB to see if file is present and linked table question.
Hi All, We presently have 3 databases that the user open's exclusively, then backs them up. Then they also, have to disconnect linked tables, then do some reports then relink them. They're doing this manually. I know it can be done with a push of a button. I'm creating another database just to run the backups and linked table My questions are: 1. Doing a backup is nothing more then copying the file to the backup folder, but I need to make sure it is not open by someone prior. I can look to see if the .ldb is present. How would I accomplish this? And is ...

old file transfers
I have an old system running on 3.1, with business files on it I want to transferee these files to my other computer , it has xp system running. is there a patch I can download to my xp system which will let me open old excel documents If your xp computer has Excel installed, you should be able to open the old Excel file. If not, you can download an Excel Viewer from the Microsoft web site, that allows you to view and print files: http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA-4110-8595-4A960D3E1C7C&displaylang=EN J B wrote: > I have an old system ru...

Email addresses are old or wrong
My Contacts are all up to date, and the Addressbook is correct, yet when I send an email, Outlook often finds some out of date email address to use. I have done registry searches for the defunct addresses, and I do not know where Outlook is finding these. They are not in Contacts or Addressbook. Anybody have a clue? rename or move your .NK2 file -- ~jason k ________________________________________________________________ please reply in group. emails directly to me will be printed and laughed at in the breakroom remember to check http://www.technet.com and http://www.google.com _______...

Case sensitivity question/problem
Hey there, I have 2 modules that open the exact same recordset, but with different queries, yet the queries are basically the exact same. Anyway, my first module runs perfect. The second one errors on duplicate value being inserted and I debugged and found out why: If fld.Name = "PromoId" Then If IsNull(fld.Value) Then Promo = "" Else Promo = fld.Value End If End If This is the exact pice of code taken from both modules, no differences. PromoID, with an uppercase D is the PK f...

Attachment question
Does leaving attachments with many sent and received messages slow down Outlook 2007? I do have an archiving time set. regards, "nobody" Not directly, but a larger pst-file could slow down the performance of Outlook. How much it will affect you depends on your hardware but in general people will not notice any slowdowns unless the pst-file grows larger as 5GB. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real Wor...

2 questions about IIS on Home Premium
Dear All, Months ago, I create a vitural directory and put it in a new Application Pool using IIS in my Vista Home Premium. But I mistoken it is a useless directory and delete the virtual directory a week ago. Now, I cannot delete the application pool. It give a message "Cannot delete application pool, there are still one application inside" (something like this). What is the icon on my default web site means. The icon is an earth with a little question mark at the lower right corner. The web works fine. I just what to know what does it means Thank you! Best ...

Removing old meeting requests/appointments
Hello, I recently came on board at a company and have been tasked to remove old entries. There is a user who had scheduled a recurring meeting using a meeting room resource. I need to find a way to remove that recurring meeting and free up the resource. Is this possible? thanks! Sean If I remember correctly a meeting room that is a resource has an associated user account that has delegated permissions to its calendar. Log in as that user account and you should be able to remove the recurring appointment. "Sean Branam" <sean@branams.com> wrote in message news:u1OpiZ...

CString format question
Hi, the answer to this is probably pretty straight-forward, but I can't find it anywhere, but I'm trying to do a more dynamic version of CString.Format Currently I have the line : num->Format( _T("%.4f"), value ); Which works fine, so if value is 0.0001932562365 it will set num to be 0.0001, so that's all good. What I want however, if for the 4 in _T("%.4f") to be dynamic, so that I can pass an integer into a method to say "format the string to nPrecision amount of decimal places", so the question is, how do I build this string up, what should ...

Migrate from Exchange 2000 old old server to Exchange 2003 on new server.
I haven't been able to find detail instructions on doing the upgrade I have to do. I have an old server with Windows 2000 AD and Exchange 2000. I want to migrate to a new server running Windows 2003 and Exchange 2003 and remove the old server entirely. I have already added the Windwos 2003 server to the domain and that is working fine. I need to find out how to add the Exchange 2003 to the new server as part of the Exchange 2000 site and migrate all the data and then remove the 2000 server. All I can find on Microsoft/Google is how to do this with Exchange 5.5 and upgrading Exchange 2...

Basic VSTO questions
I am an absolute beginner with VSTO and Excel. I am using VS 2008 Express. I've been able to execute a few very basic steps by creating a reference to the Microsoft Excel 12.0 Object Library COM object. That allowed me to start an Excel app and at least view the workbook object. My question is this. I want to do some basic cell operations. It seems that I do not understand the Excel object model. I assumed that if I created the Excel App, then I would be able to access the sub-ordinate Workbooks collection and then the Worksheets collection and eventually the cells on the wor...

fragmentation question
The partition where my IS lives is showing a high level of fragmentation. According to the Win2k defrag utility I have 49% total fragmentation and 99% file fragmentation. The largest number of fragments are on the backup file and on the priv1.stm file, over 900 each. This seems excessive, right? From what I read it may not help to defrag using the ESEutil because it's a disk problem. My question is if I shutdown the Exchange services and move the the whole Exchange data directory off the disk and then back, can I restart svcs and be good to go? I could also run the Win2k defrag utilit...