split column into 4 columns

I need some assitance making a huge column into 4 columns. Here is the 
scenerio:
Col A has any given rows of values / text. and I need to split into 4 
columns But this is where it gets tricky.
a1 =a
a2=b
a3=c
a4=d
a5=e
a6=f
a7=g
etc...
I need b1=a, c1=b, d1=c, e1=d
b2=e, c2=f, d2=g, etc..
What is the best way to do this?

0
Jack4963 (284)
11/15/2005 7:16:14 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
389 Views

Similar Articles

[PageSpeed] 1

Put this in B1:
=OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)
drag across to fill B1:e1

and drag down until you run out of data.



Jack wrote:
> 
> I need some assitance making a huge column into 4 columns. Here is the
> scenerio:
> Col A has any given rows of values / text. and I need to split into 4
> columns But this is where it gets tricky.
> a1 =a
> a2=b
> a3=c
> a4=d
> a5=e
> a6=f
> a7=g
> etc...
> I need b1=a, c1=b, d1=c, e1=d
> b2=e, c2=f, d2=g, etc..
> What is the best way to do this?

-- 

Dave Peterson
0
petersod (12004)
11/15/2005 7:53:20 PM
Dave,
One Word ---> PERFECT!
Thank you!
One additional question? How would I incorporate an if statement so that the 
'0's dont show up if there isnt data?


"Dave Peterson" wrote:

> Put this in B1:
> =OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)
> drag across to fill B1:e1
> 
> and drag down until you run out of data.
> 
> 
> 
> Jack wrote:
> > 
> > I need some assitance making a huge column into 4 columns. Here is the
> > scenerio:
> > Col A has any given rows of values / text. and I need to split into 4
> > columns But this is where it gets tricky.
> > a1 =a
> > a2=b
> > a3=c
> > a4=d
> > a5=e
> > a6=f
> > a7=g
> > etc...
> > I need b1=a, c1=b, d1=c, e1=d
> > b2=e, c2=f, d2=g, etc..
> > What is the best way to do this?
> 
> -- 
> 
> Dave Peterson
> 
0
Jack4963 (284)
11/15/2005 8:10:04 PM
How about using a custom format like

General;-General;

otherwise

=IF(OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)="","",OFFSET($A$1,4*(ROW()-1)+COLU
MN()-2,0))


-- 

Regards,

Peo Sjoblom


"Jack" <Jack@discussions.microsoft.com> wrote in message
news:964EF472-C1D0-4612-A0F8-53B467DE48EE@microsoft.com...
> Dave,
> One Word ---> PERFECT!
> Thank you!
> One additional question? How would I incorporate an if statement so that
the
> '0's dont show up if there isnt data?
>
>
> "Dave Peterson" wrote:
>
> > Put this in B1:
> > =OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)
> > drag across to fill B1:e1
> >
> > and drag down until you run out of data.
> >
> >
> >
> > Jack wrote:
> > >
> > > I need some assitance making a huge column into 4 columns. Here is the
> > > scenerio:
> > > Col A has any given rows of values / text. and I need to split into 4
> > > columns But this is where it gets tricky.
> > > a1 =a
> > > a2=b
> > > a3=c
> > > a4=d
> > > a5=e
> > > a6=f
> > > a7=g
> > > etc...
> > > I need b1=a, c1=b, d1=c, e1=d
> > > b2=e, c2=f, d2=g, etc..
> > > What is the best way to do this?
> >
> > -- 
> >
> > Dave Peterson
> >


0
terre081 (3244)
11/15/2005 8:27:41 PM
Reply:

Similar Artilces:

BCP Computed Column
Hi. I have a table with a computed column. When I try to import data into table through BCP it generates error due to computed column. So how to handle a computed column while importing data into table through bcp from a text file. Regards, Muhammad Bilal You need to use a format file that tells BCP to skip that column. See BOL for info on bcp format files. Tom "Muhammad Bilal" <MuhammadBilal@discussions.microsoft.com> wrote in message news:5AD65421-C599-4773-B56F-24841FE05D5A@microsoft.com... > Hi. > > I have a table with a computed column...

data labels from second column
Hi Column A is list of names (Bob, Sue, etc); column B is how much they collected (58, 12, etc); Column C is the date they did it on - so record 1 says Bob collected 58 on 10/07/07, for instance. I want to create a chart with the date for the x axis, amount collected as the y axis and data labels at each point within the graph giving the collector's name. So at X=12/07/07, y=58 I want it to say Bob within the chart area. Any help much appreciated. Regards Chandler On Mon, 3 Sep 2007, in microsoft.public.excel.charting, Chandler <Chandler@discussions.microsoft.com> said: &...

CRM 4.0 DESKTOP client for Outlook don't load all options (?)
Today I found some problem in CRM Desktop Outlook client...when I try to create e-mail quick campaign I did't find option "e-mail via mail merge" and the same option for list. In CRM menu (in Outlook) under Otions I didn't found "synchronization" tab. After restart Outlook for few hours all was ok, but after I observed the same situation, restart and all is ok... Is some solution of this problem... ...

Excel formula help #4
Hello! Can somone help me with this formula 2004:21-0-1-1-11 in this cell i wan't to get out "21" 2005:2-0-0-0-1 in this cell i wan't to get out "2" My result from the formula is "-21" and ":2-" is there a formula that i can use on both and get the result "21" and "2"? I want to add the together at the end and get the result "23" but with : and - i only get error. -- johhny ------------------------------------------------------------------------ johhny's Profile: http://www.excelforum.com/member.php?act...

How to stop tracking a Contact in CRM 4.0
How do I stop tracking an Outlook Contact that has been set to "Track in CRM" Hi Ken, The "Track in CRM" for contacts turns to "View in CRM" when a matching contact record is created in the CRM database. If you no longer want that contact in CRM then you can delete it, then it will say "Track in CRM" again. "Ken Florian" wrote: > How do I stop tracking an Outlook Contact that has been set to "Track in CRM" ...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Outlook vs. webaccess crm 4.0
Hi 1) Could someone specify the pros and cons (especially the difference in the "functionality" options) for the end users - if they use the Outlook client or if they use a Webbrowser? 2) What are most common ? thanks for any feedback... It's true you can do everything on the web version or the Outlook version. The biggest feature of CRM is the fact you can everything inside of Outlook and not have to go a different application. Outlook: 1 - Easier concept for end users (especially Sales folk) to grasp. "you don't need to learn a new application - it's ju...

Invalid page fault #4
When I start up Outlook a message displays this program has performed an Illegal Operation. Further explanation describes " Outlook caused an invalid page fault in clthood.dll @0197:61c05319". Just started doing this. When I try to reinstall Outlook, I get a message Unable to run setup. Any suggestions? Hi, Are you sure that the name of the file is correct ? Is it clthood.dll ??? Please verify .. Hope this helps !!! With Regards, Sudharson.AN "Mark" <anonymous@discussions.microsoft.com> wrote in message news:4cf201c3ffcf$168c6a40$a001280a@phx.gbl... > ...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

customizations import from 3.0 to 4.0
Is is possible to import customizations into a 4.0 system that were exported from a 3.0 install? You will need to upgrade 3.0 implementation to 4.0 by running MS CRM 4.0 setup. Please go through Microsoft Dynamics CRM 4.0 Installing Guide. You can download this guide from folowing URL http://www.microsoft.com/downloads/details.aspx?FamilyId=1CEB5E01-DE9F-48C0-8CE2-51633EBF4714&displaylang=en I don't think that there is any supported way through which you can import 3.0 Customization file to 4.0 implementation. Cheers, Rohit "Leo_B" wrote: > Is is possible to im...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

Payroll posting accounts #4
I am trying to set up information in the payroll posting accounts, when I am in the Employer´s Tax Expense window I can not fill the SUTA & FUTA code. It displays: "This is not a valid tax witholding code". What can I do to solve it? -- Jorge R. HI Jorge, Just give any dummy account as you are not calucting the SUTA and FUTA then system won't do anything but is require so please full fill all the posting account setup requirement before you proceed. "Jorge" wrote: > I am trying to set up information in the payroll posting accounts, when I am > ...

Outlook 2003 with Business Contact Manager #4
It there anyway to get the outlook business contact manager information to sync with pocket pc 2003 outlook Try asking in microsoft.public.pocketpc.activesync as the folks who hang out there have all kinds of good links to third party programs. Outlook itself cannot do this. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Glen asked: | It there anyway to get the outlook business contact | ma...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

Two lines and pne column
I am trying to modify one of the custom charts to show two line and one column rather than two columns and one line. can anyone help. Ian - Forget the custom type. Make a chart with all three series as lines, select the one series, choose Chart Type from the Chart menu, and select the Column style you want. Voila, your first custom combination chart. There's more about Combination Charts on my web site: http://peltiertech.com/Excel/Charts/ComboCharts.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech....

CRM 4 Advance Finds with Windows 7
I have for some time been using CRM4 with windows 7 (RC and RTM) and have found one problem. Just wondering if anyone else has seen this and found a resolution yet. When in an advance find it will hang when selecting a pick list. For example. Open advanced find, Select Activities and use saved view [new] In the details select ActivityStatus as the Field, select equals and then click the three dots to chose the values. Select any values then click OK. At this point the page still says 'enter value' and alot of the page can not be clicked on. This will always happen on a new adv...

Conversions #4
I need to know how to convert a number with decimals (10.75) to display in feet and inches (10'-9") in an Excel workbook. Check out http://www.mvps.org/dmcritchie/excel/formula.htm#carpentry In article <47BCBD75-0C72-45DF-98EF-3C0E5125E503@microsoft.com>, "Curious" <Curious@discussions.microsoft.com> wrote: > I need to know how to convert a number with decimals (10.75) to display > in feet and inches (10'-9") in an Excel workbook. ...

How do I create 4 smaller documents on an 8X11 (gang)?
I have a doc that's currently 8X11; how do I replicate the document at 1/4 scale and gang it four times on the page? Open file, Resize the page area to 4�"x5�". Make everything fit and the way you like it. Now when you print the file you will get 4 copies per sheet. -- Don ------ Vancouver, USA "WhidbeyJack" <WhidbeyJack@discussions.microsoft.com> wrote in message news:FCAAE5CA-9E36-4C2F-923C-66C5F5DA527B@microsoft.com... >I have a doc that's currently 8X11; how do I replicate the document at 1/4 > scale and gang it four times on the page? ...

How can I pull out position 4 in a string?
Column A contains a string MyString aaaaaa bbbbbb cccccc dddddd eeemee fffmff gggmgg I want the value of B1 to be the value of A1 unless the 4th character in A1 is a "m". So column B would be B1=aaaaaa B2=bbbbbb B3=cccccc B4=dddddd B5 = empty cell B6 = empty cell B7 = empty cell What's the simplest way to do this? =IF(MID(A1,4,1)="m","",A1) -- Regards, Peo Sjoblom "Stephanie" <nowhere@microsoft.com> wrote in message news:OjT2P9s2DHA.2528@TK2MSFTNGP10.phx.gbl... > Column A contains a string > > MyString > aaaaaa > b...

One column into three
We have a table with about 40 columns and 120 Rows and we need to split each column into 3 columns only for certain rows - I'm guessing that we can't spilt a column only for certain rows as this would mess-up the entire column/row naming convention - so is there a way to automate the spliting of a column into 3 rows (i.e column A becomes A,B & C) and then merging each trio of cells (i.e. A1, B1, & C1) into one cell in the row and retaining the data that was in A1 and moving the data that was in B1 into D1 - for all 120 rows and then repeating the process for each colum...

Printing a Report 2" X 4"
I am using a DB where you select members that have signed up for and art event and print labels using a Dymo printer. The label contains member info, art info, and calculations for sales tax and totals. My problem is that if I make a Label report the calculations do not work the same a making a report. How do I size a report for a 2" X 4" report, which is the Dymo label I want to use, or how do I make calculations work on a label report? Thanks for any help you can provide. -- Doug Small The Dymo printer should have software that creates the 2"x4" label s...

How to print 4 panels on 8.5" x 14" paper
I have been trying to print a document that I made on a 4 panel layout. I changed the paper size to 8.5" x14" and I changed the layout to landscape. The document does not print out correctly. Can anyone help me? Please define "does not print out properly". Just because you change the paper size does not mean that your design will automatically rescale itself to fit in the new size. -- JoAnn Paules MVP Microsoft [Publisher] "Katie" <Katie@discussions.microsoft.com> wrote in message news:03F70C7F-D27B-4122-908C-20A926F0A6CA@microsoft.com... &g...

FRx
Is there any way to import values to a column of FRx 6.7 report from excel/doc file? Thanks in Advance, Arun. You can't "import" data but you can include data from a spreadsheet in an FRx report. See Chapter 4 of the FRx Report Designer User's Guide. -- Charles Allen, MVP "Arun" wrote: > Is there any way to import values to a column of FRx 6.7 report from > excel/doc file? > > Thanks in Advance, > Arun. ...

Pivot table help #4
Hi, I'm using an excel spreadsheet and I have a list of customer numbers in column A and a list of account numbers in column B. Many customers have more than 1 account number. What I would like to do is change the layout so that I have each customer number listed only once and have all of the account numbers corresponding to the customer number in the same row (so for example I would have customer number in A:1, account number 1 in B:1, account number 2 in B:2, account number 3 in B:3 ect) . Is there a way to do this? Is it possible to do this in a pvt table? Thanks. SNAKECOLS, How...

CRM 4.0 Data Import
I am looking to import Account and Contact data into CRM 4.0 by going to New Import I have imported the Account Information with a data map not problem. I then wanted to import the Accounts associated Contact information by going through the same way. When the data map defaulted to Automatic the Contacts imported correctly. However if I created a data map to import the Contact information using the Parent Customer attribute to create a relationship it fails to import returning the Error on the records "The source data is not in the required format". This was the same import ...