how to split data into columns and arrange the resulting data

I have data in cells A2, A3 and A4 as below:
1, 2, 4, 5, 7, 8	
1, 2, 3, 4	
2, 3, 4	

I want to split into columns (which I did using Data > Text to Columns 
option).  

But I also want the data to "automatically" fall under appropriate heading 
(1, 2, 3, 4, 5, 6, 7, 8) which are in respecive cells A1:H1.  

Thank you in advance

0
Jack4963 (284)
11/11/2005 7:21:07 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
648 Views

Similar Articles

[PageSpeed] 47

How about an alternative:

With your data in A2:Axx, put this formula in B2:
=","&SUBSTITUTE(A2," ","")&","

Then B2 will look like:
,1,2,4,5,7,8,

Then put this in C2 and drag across as far as you need.
=IF(COUNTIF($B2,"*,"&COLUMN()-2&",*")>0,COLUMN()-2,"")

Select B2 through L2 (as far right as you went).
Drag down.

If you want values, select that range
Edit|copy
edit|paste special|values

And delete columns A:B if you want.


jack wrote:
> 
> I have data in cells A2, A3 and A4 as below:
> 1, 2, 4, 5, 7, 8
> 1, 2, 3, 4
> 2, 3, 4
> 
> I want to split into columns (which I did using Data > Text to Columns
> option).
> 
> But I also want the data to "automatically" fall under appropriate heading
> (1, 2, 3, 4, 5, 6, 7, 8) which are in respecive cells A1:H1.
> 
> Thank you in advance

-- 

Dave Peterson
0
petersod (12005)
11/11/2005 11:20:46 PM
Reply:

Similar Artilces:

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

EDB Data recover
One of our client had a very bad HDD crash. A specialized society had only recover the EDB file from our client's 5.5 server. After many try, we still could'nt access the data. Our client want to recover the data of the different mailboxes. We've installed a server with the same data but the server refuse to start. edbutil say that the database is safe. How could we recover or extract the data from the edb ? If you only have the EDB file left, you could always consider PowerControls: http://www.ontrack.co.uk/powercontrols/ -- Neil Hobson Exchange MVP For Exchange new...

range of data from different sheets
I'm having trouble creating a graph because the data is spread on different sheets. I've got a new sheet for each month and I'd like to produce a trend graph. Do I have to bring each set of numbers onto the same sheet, or is there a way to have a range of data spread over various sheets? I hope that makes sense! For any given series, for any given set of values (x or y) the data must be on the same sheet. Other than that, you can have data on multiple sheets. So, for one series you could have the series name on one sheet, the x- values on a 2nd sheet, and the y-values on a...

Data Collection entries in General Ledger
If entries are created through ADC and processed through GL, the Batch ID is populated as ADC(plus date). Once posted, we can go into Account Transactions in Smartlist and the Batch ID is populated in the "Originating Source" field and when drilling down on a transaction, the Batch ID is retained just like before it is posted. When we need to make corrections to these entries we go into Data Collection (through MFG WIP) and back out the amounts and add back the correct entries. Processing through GL results in the Batch ID created as the User ID that made the correction. P...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

Importing Data In A Macro
I need to use Access a little like an ETL tool, and want to run a macro that imports data from an ODBC source (Sybase in this case) and then uses insert and update queries in Access for manipulation. The macro will be scheduled using the Windows Scheduler. Anyway, I am trying to use the "Transfer Database" action in the macro. The only thing I don't understand, is how do I keep from importing the entire table from Sybase, i.e. I want to import using a query, looking at the Access database, getting a maximum existing date and then just importing anything newer than that. The Syba...

Data Validation 05-08-10
I’m doing data validation on a group of cells. At the moment all the names of the employees appear on the drop down list for those cells, but what I need is to compare those names to a day-off list where the people who are off on that day appear. If their names appear on the day-off list then they should not appear on my data validation cells. In other words I have colA-colG with the names of the week, Mon-Sun. I have 30 rows. The first 15 rows are for my data validation and they are drop downs of the people who are working on that day. The source for those rows comes from a ...

How do I save data with different colored fonts in EXCEL 2003?
I have a rather large spreadsheet with some data set to different colors that mean different thigs to those of us that use it. For some reason, these color changes are not being saved when I save my spreadsheet. When I open my spreadsheet again, the colors are gone. Which version of Excel are you using? Are you sure that you're saving the spreadsheet as Excel (and not as some other file type which Excel can read)? Is the resultant file keeping a suffix of ".xls"? If not, then the data is being saved but not the formatting specifications. Next time you save the ...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Free & Busy Data messed up.. Plz Help!!
We have exchange v5.5. Recently one of the exchange admins was in the public folders messing around with permissions. He said he didn't change anything but something had to have changed because now all free and busy data, public address book, tasks, notes, etc are not accessible to new users. Any time we create a new email account they can't view there calendar (says it doesn't exist), nothing shows up in the global address list. Anytime they receive an email to accept a meeting it says unable to open item. I've done the usual things like reset the server. I loo...

Migrating Data from Act 2005 to CRM 3.0
Is it possible for me to export from Act 2005 to Outlook and then, export into CRM 3.0. Will this all me to keep the notes in Act 2005. ...

Return vlookup result but only for the first hit
Hi all, I hope someone can help with this one - I think I'm overly complicating my problem here - hoping there's a simple answer - it's a tricky one to describe though.... Here's the overview. 3 rooms can be booked for courses where the customer will use minutes in subsequent visits. When making a booking, a "customer" tab is used to identify the price of the couse and gives the customer a unique number e.g. 5023Tony Summers (concatenated for a lookup) In the diary planner tab, I have a number of columns for each room - the front end for the user. My pr...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Select ID,Data FROM [Data$]"
I'm going thru C# code that connects to an excel object. I've never seen a slq construct like this. Would someone please decipher the "[Data$]" part? Thank you. "Select ID,Data FROM [Data$]" (used in a constructor like: new OleDbDataAdapter(query, connection)) On May 9, 9:28=A0am, cate <catebekens...@yahoo.com> wrote: > I'm going thru C# code that =A0connects to an excel object. =A0I've never > seen a slq construct like this. =A0Would someone please decipher the > "[Data$]" part? =A0Thank you. > > "Select...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

Data Validation Lists
I have been searching the web all day, and I am not sure how to approach this problem. Here is a basic worksheet: ID Name Serial # 1 Joe ck9033n2389d 2 Joe 349-283fjrjh55 3 Bob c90320j34n5kjdf 4 Joe kgho95injhs 5 Bob 2353gdf745 Step 1: I have a drop down list with only one instance of each of the "Name" variable. Step 2: Upon selecting that name, I would like another drop down box with all the serial numbers associated with that person. Step 3: Then, based on the serial number, display stats in the same row for that i...

company split
Hi all, hoping to get some advice on this, the company i work for consists of two divisions who are effectively looking to split, and do there own thing. But initially will continue to share resources such as IT. Is it possible in exchange to allow them both to have there own email addresses such as name@company1 and name@company2 and to hide these so that relevant individuals only see the names of users relevent to there division/company in their address book. Effectively what i need to be able to do is run 2 seperate companies from one exchange box. bearing in mind that both compani...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

Columns in Money 2004
Is there any way to change the default columns in the register view ? I really dont want the flagged column, and would like to include a column for categories. Is it possible to do this ? In microsoft.public.money, VerticalCobra wrote: >Is there any way to change the default columns in the register view ? I >really dont want the flagged column, and would like to include a column for >categories. Is it possible to do this ? No. Try Ctrl+T to toggle to multi-line display and back. ...