need to transpose 3 or 4 columns to one column

The matrix below represents what I need to do in Excel. I need to
transpose the data from row 1 of C1, C2, C3 and C4  to rows 1-4 of C5
then repeat for each additional row of C1-C4. I think I might need a
macro but I have no idea how to  write it.  Any help would be
appreciated.  My data set is too large to utilize cut and paste
special. Thank you for your responses.

Rufus



C1	C2	C3	C4	C5
5	4	3	5	5
4	3	4	5	4
5	4	3	2	3
				5
				4
				3
				4
				5
				5
				4
				3
				2

0
RufusC (3)
7/22/2005 2:48:18 PM
excel 39879 articles. 2 followers. Follow

2 Replies
594 Views

Similar Articles

[PageSpeed] 53

Rufus,

If your example table is entered in cells A1:D4, enter this into cell E2, and copy down until it 
returns #REF errors.

=INDEX($A$1:$D$4,INT((ROW()-1)/4)+2,IF(MOD((ROW()-1),4)=0,4,MOD((ROW()-1),4)))

HTH,
Bernie
MS Excel MVP


"Rufus" <RufusC@gmail.com> wrote in message 
news:1122043697.974837.104960@g43g2000cwa.googlegroups.com...
> The matrix below represents what I need to do in Excel. I need to
> transpose the data from row 1 of C1, C2, C3 and C4  to rows 1-4 of C5
> then repeat for each additional row of C1-C4. I think I might need a
> macro but I have no idea how to  write it.  Any help would be
> appreciated.  My data set is too large to utilize cut and paste
> special. Thank you for your responses.
>
> Rufus
>
>
>
> C1 C2 C3 C4 C5
> 5 4 3 5 5
> 4 3 4 5 4
> 5 4 3 2 3
> 5
> 4
> 3
> 4
> 5
> 5
> 4
> 3
> 2
> 


0
Bernie
7/22/2005 3:50:41 PM
You can use the Transpose function (as apposed to the Copy Paste Special 
version). Look on http://www.auditexcel.co.za/lookupandreference.html to see 
how.

"Rufus" wrote:

> The matrix below represents what I need to do in Excel. I need to
> transpose the data from row 1 of C1, C2, C3 and C4  to rows 1-4 of C5
> then repeat for each additional row of C1-C4. I think I might need a
> macro but I have no idea how to  write it.  Any help would be
> appreciated.  My data set is too large to utilize cut and paste
> special. Thank you for your responses.
> 
> Rufus
> 
> 
> 
> C1	C2	C3	C4	C5
> 5	4	3	5	5
> 4	3	4	5	4
> 5	4	3	2	3
> 				5
> 				4
> 				3
> 				4
> 				5
> 				5
> 				4
> 				3
> 				2
> 
> 
0
AdrianM (30)
7/23/2005 6:33:03 AM
Reply:

Similar Artilces:

PUBLIC CONTACTS #4
I created a publix Contact list for all my users and gave them rights to create but not delet.Also I gave myself fun right . When we creat a new contact and try to save it I get an error: "the item could not be saved to this folder. The folder has been deleted or moved, or you do not have permission. Do you want to save a copy of it in the default folder for the item?" Can anyone help me with this proble ? Thank you!! Mike wrote: > I created a publix Contact list for all my users and gave them rights to > create but not delet.Also I gave myself fun right . > When we...

splitting one column into two columns ... not what you think
i have fixed column widths that can't be changed; nor can any other columns be added to the worksheet ... i've got data in one column that represents "results" ... within the results column though, i need two columns (starting directly below the "results" cell, one that reads "in range" and that other that reads "out of range" ... so: if i'm on [column a] [cell 1] i want: "results" ... in [column a] [cell 2] i want: "in range" AND "out of range" with a line down the middle. "text to column" is ...

illegal operation #4
Does anybody know the reason why I get the following message when I'm closing outlook express? "This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor" The details of the message are as follows: MSIMN caused an invalid page fault in module KERNEL32.DLL at 017f:bff9db61. Registers: EAX=c00309c4 CS=017f EIP=bff9db61 EFLGS=00010216 EBX=0335fe28 SS=0187 ESP=0331ff18 EBP=033201b4 ECX=00000000 DS=0187 ESI=00000000 FS=64bf EDX=bff76855 ES=0187 EDI=bff79198 GS=0000 Bytes at CS:EIP: 53 8b 15 e4 9c fc bf 56 89 4d e4...

RPC over HTTP problem #3
Hi, All! My network configuration: DC1, DC2 and MX (MS Exchange 2003, sp1). All of them Windows Server 2003. What was done: In the registry on dc1 and dc2 was created a new key: "NSPI Interface protocol sequences" with value: ncacn_http:6004. MX was promoted to be a GC. Installed RPC over HTTP windows component. MX was changed to be RPC-HTTP back-end server. On the MX Default Web Site was installed cerificate from the local authority running on DC2. On the RPC virtual directory anonymous access and integrated windows authentication were disabled. In the registry of MX the key HK...

help installing exchange 2000 in one of 3 sites
I have a domain cotoso.com installed and configured as AD for 3 companies interlinked together. and + a total of 3 DC in 3 different sites that are connected by VPN. i have exchange installed in each site connected on the same domain contoso.Local(+ E-mail -->contoso.com) + hosting their own companie domain name such as x.com or y.co or z.com for each company. I have a routing link configured + 3 administrative groups in exchange system manger for each company. one DC in one site that also has exchange 2000 installed on it has to be changed because of poor performance. now i'm ...

Finding duplicates in column
Hi, I have Excel 2002 and have 6000 emails addresses in a column. How can I find if there are any duplicates in that column? Thanks rock Assuming the data is in column A, add a formula in column B of =IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","") Cpy that down, then you can filter column B for Duplicate Sorry to be so newby Bob, but when you say 'copy' down, what exactly do you mean? I have entered the formula in B1 but.. Thanks rock Bob Phillips wrote: > Assuming the data is in column A, add a formula in column B of > > =IF(COUNTI...

Outlook Notes #4
How do you change the look of Notes from full-screen to a smaller size. There are no drag handles or "top right hand corner" options. Sounds like you dragged it too big or maximized it. Can you double-click the blue bar at the top? <-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-> Hope this helps! Anne Troy (better known as Dreamboat) Author: Dreamboat on Word Email: Dreamboat*at*Piersontech.com Web: www.TheOfficeExperts.com "David" <anonymous@discussions.microsoft.com> wrote in message news:170d701c420ee$0960a350$a001280a@...

3-Color Scale Vlookup for Current Month/Previous/Pre-Previous
Is it possible to Conditionally Format a cell so that the colors are based off of VLOOKUP's? What I mean is I have a table on another tab and it has by month how complete the build is. I want the conditional format to color the percent of the current month red, percent of previous month yellow and pre-previous month green. I tried putting the formula =("41227-1",BuildData,18,FALSE) as the current, = ("41227-1",BuildData,17,FALSE) as the previous, and = ("41227-1",BuildData,16,FALSE) as the pre-previous. However, it uses only the cells value to colo...

Password Protection #4
I have a password protected sheet. For one of the columns, I have a validation rule and have a list of values that the column can take. I put a password for the sheet and now no can change the sheet except for the column where I have the validation rule. When I click on that column, the small arrow appears and the drop down box appears and I can select a different value and change the cell. All other columns are locked. Cant we protect a column that has a validation rule with a "List" as option? Not that I'm aware. But doesn't the Data|Validation provide the sa...

To write living will do I need special format
I just need to change and update a living trust You should consult someone with the appropriate legal knowledge in the jurisdiction in which you are domiciled. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "belladonna" <belladonna@discussions.microsoft.com> wrote in message news:F25A64CB-355F-44E9-A005-16AA61DA15A7@microsoft.com... > I just need to change and update a living trust ...

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

Printing Multiple Copies #4
Hi all - I am using Vista Business and Publisher 2007 from the Office 2007 Small Business package. No matter what type of publication or template I use I can only print single copies. Most recently I tried to print 30 copies of a single page flyer and had to do it a single copy at a time. If I go to File|Print and set the number of copies to 30, I still get a single copy only. Does anyone have any thoughts? Thanks -- Andrew Aitchison (amhsn@nospam.sympatico.ca) Is your printer driver current? What is the default in the printing preferences in the control panel printer folder? ...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

How to forward emails (unchanged) from one account to another
Assume I manage two different profiles and eMail accounts with my Outlook. How can I adjust in one of them that all arriving eMails should automatically be forwarded (unchanged !!) to the other account ? Sven "Sven Claasen" wrote: > Assume I manage two different profiles and eMail accounts with my Outlook. > > How can I adjust in one of them that all arriving eMails should automatically be forwarded (unchanged !!) > to the other account ? > > Sven > Automatically forward messages to another e-mail account - Outlook - Microsoft Office Online: http://of...

Excel problem #3
I am attaching an excel file where i have a problem In the file are 2 sheets, Main & second I want to get data from second sheet to the main sheet by a formula by which the amount in the total column will be posted in the second sheet falling under various dates. I have done for 6 sept 2003 by way of example I do not know any formula by which i can do this automatically Please help me Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=444742 --- Message posted from http://www.ExcelForum.com/ Hi one way: ...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

CRM 4.0 Outlook RPC pver http
hi, A customer already has SBS with CRM server running. Some employees have Outlook configured using RPC over http. These users can use CRM using the web client and need access thru outlook. Is RPC over http supported in CRM 4.0 ? What are the issues involved in getting Outlook to work with CRM 4.0 ? I am yet to review the implementation guide for this situation, any other blog or guide I need to refer ? Thanks in advance. On Aug 14, 1:53=A0am, ShawnK <Sha...@discussions.microsoft.com> wrote: > hi, > > A customer already has SBS with CRM server running. Some employees...

How can I choose alternate rows in a column?
My requirement is to be able to create a column whose elements consist of difference between adjacent elements in a column, say column A. If I can choose alternate elements and create 2 new columns then I can just subtract the 2 columns easily. Huh? "pnair" <pnair@discussions.microsoft.com> wrote in message news:D47AD012-084B-49C6-8672-5067E8455D9E@microsoft.com... > My requirement is to be able to create a column whose elements consist of > difference between adjacent elements in a column, say column A. If I can > choose alternate elements and create 2 new colum...

Column styles doesn't appear on the worksheet
Hello, one of my customers has changed the windows xp designs to its own. From this moment excel doesn't show the font color and the background color of a cell in the worksheet. Only in the print preview you can see the color settings of the cells. I'm not shure if this problem belongs to the changing of the windows xp designs, but from this moment it did occur. This occurs on new excel-documents and on existing ones. With another user account on the same machine the problem doesn't occur. I have reinstalled Office 2003 and even deleted user registry entries for Office...

suggestion needed !!!!!!!!!!
In my company the want to calculate phone calls done by each sales person at the end of the day. Please give me suggestion how to implement this. Mahain, A quick solution would be 1) Create an advance find view based on the criteria you are looking for ie. Activity of the type phone call, Status = completed 2) Export the list to Excel (Dynamic pivot table/worksheet) 3) If pivot : Drag and drop relavent fields 3) If worksheet: filter by date and use sum. The advantage of dynamic excel report is it updates itself when ever you open the sheet. Hope this is helpful Venkat http://venkatbendalam...

Search #3
Why does Outlook 2003 automatically switch to seach mode within about five seconds after selecting one of the other folders (i.e., Contacts, Tasks, Calendar or Notes)? Pop-ups are also displayed above the search field as well and would like to remove this from Outlook. (After all it is my computer and I do not want to share with unwanted soliciters). This does not happen on any of my installations. Please be more specific with when and how this happens - detail the steps to reproduce the situation as I am unable to do so based on your post. --� Milly Staples [MVP - Outlook] Post all repl...

Integration Manager need a registration key for test company?
Hi all, Can anyone tell if I need the registration key for IM if I am doing some testings on Test company (World Online)? Also, can I specify the company database in IM for the data to import to? Please advise. Thanks You do not need a reg key for the Two Company, and if you are using the standard adapter the data will integrate into the Company that is open in Great Plains. You can Specify which database you want to integrate into by using the SQL Optimizer. You do not need great Plains open for this adapter. "Terry" wrote: > Hi all, > > Can anyone tell if I nee...

Multiple Account Download from One Financial Instituition
Hi, I have at one brokerage mutliple accounts (individual, joint, 401K, roll-over etc). In Money they are all separate accounts. When I connect with the brokerage I am only able to connect to one type of account (in this case retirement). How can I download multiple accounts from a brokerage (when not all have the same URL). Please email me your reply. Thanks Sandeep ...

Bank Deposit #3
We would like to suggest being able to create multiple bank deposits from the Bank Deposit Entry window. ---------------- 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" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=5f3f4cb3-e7f4-4d76-...

Hide columns if there are no entry's in column
Hi everyone, I have a workbook with multiple sheets. One sheet is a overview from all the sheets and had all dates in it. Is there a VBA to hide columns when there are no entry's in it? The code has to work when I open the sheet "overview" Hope someone can help me with it! Thanks in advanced! Regards Berry Berry, If you have a row that when blank would indicate which columns to hide, you could use On Error Resume Next Rows("1:1").SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True HTH, Bernie MS Excel MVP <blommerse@saz.nl> wrote in message news:118...