I have a data in Coloumn "A":
12345678910
I need to Convert the Data in B1 Like
1;2;3;4;5;6;7;8;9;10
the No of Columns may increase, but i should get a data till where the data is in Coloumn "A" ends.
I have a few hundred cells that I would like to combine. Each cell contains a six digit code number. I would like to combine the cells with a semicolon and a space between each code number.
I can do this with the CONCATENATE function but it will take too long.
My worksheet looks like the one in the image, but it has about 2600 records in it.
Schermafbeelding 2012-11-23 om 10.20.45.png
I want to distract all the semicolons into new rows with the same values as they had when they were still in the semicolon cell.
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
View 6 Replies View RelatedI am trying to convert some data...
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series
Antidiarrheal Agents Application
Antidiarrheal Agents Generic Drug Name
Antidiarrheal Agents Strength
Antidiarrheal Agents Type
Antidiarrheal Agents Dosage Form
Antidiarrheal Agents Container Type
Antidiarrheal Agents Volume
Antidyskinetics Brand or Series
Antidyskinetics Application
Antidyskinetics Generic Drug Name
Antidyskinetics Strength
Antidyskinetics Type
Antidyskinetics Dosage Form
Antidyskinetics Container Type
Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I want to convert a bloack of rows to columns
1abc2xyz3mnq
4efg5klm6rst
required format
1abc
4efg
2xyz
5klm
3mnq
6rst
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name
Column C: Sales Rep Number
Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
Row 1: invoice ABCD-1234 Sales Rep 1: 55%
Row 2: invoice ABCD-1234 Sales Rep 2: 25%
Row 3: invoice ABCD-1234 Sales Rep 3: 20%
I want the output as follows:
Row1: invoice ABCD-1234 Sales Rep 1: 55% - Sales Rep 2: 25% - Sales Rep 3: 20%
I have attached a sample workbook with two sheets; one containing the raw data and the second, which shows the desired output.
I have some mails in a colum and i would like to put with a formula into a cell.
For example, in column I have:
mail1@hotmail.com
mail2@hotmail.com
mail3@hotmail.com
mail4@hotmail.com
mail5@hotmail.com
mail6@hotmail.com
And into anything cell i would like to put with the coma:
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows:
Company Name - 2nd row
Address - 3 row.
In this case, I want to combile them as one entry Separated by a comma
& placed in one column
Eg: For 3rd one in sample,
Haifa Marble & Tile
69 Garfield St
Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it
into one column separated by comma.
Solution to convert data columns to rows in excel.
View 4 Replies View RelatedI have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.
I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.
Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.
I'm currently faced with a spreadsheet that has data formatted like this:
A
1 RandomRowofData1
2 RandomRowofData2
3 RandomRowofData3
4 RandomRowofData4
5 RandomRowofData5
6 RandomRowofData6
7 RandomRowofData7
8 RandomRowofData8
9 RandomRowofData9
Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words)....
I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.
How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):
GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I
I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):
1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls
I have data in excel sheet in the below format:
Existing view.png
How to write a VBA code or Macro to get it in below format:
Required View.png
Timestamp column is the unique key.
I currently have a spreadsheet that I had to convert from multiple rows to columns:
[URL]
Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
For the spreadsheet below I need code to determine what rows to add for the subtotal for each week for each person listed. The rows will vary each time.
Week: WK1
Visit
Date
OMR
Pay Item
UM
Rate
Start
Finish
Time
Subtotal
10/25/13
GA
RG
H
0.00
35
0.00
Totals for WK1: 0 Visit / 2 Admin
[Code] ....
I have a worksheet that I use to track my clients and their meal selections over a five day week. Col A is their name, B is their shift (am or pm) C is their table number, D is whether they will attend that day (yes or no) and E is their meal choice (a b or c) Columns D & E repeat the same information for each day Mon-Fri.
I would like to have a print button so that on a daily basis I can print the clients name and thier meal selection (only those those who have a yes for that day) separating AM shift from PM and then grouping them by their table number rather than alphabetical order.
I have a data sheet and I usually copy manually the values from different columns. I would like to paste them into another worksheet so that when I paste the values in Column A, I should not surpass Row100, and if so then the code should automatically shift to column B and start pasting. This should apply to all the columns till column F. Once column F is full till row100 then the procedure should start again from column A.
View 4 Replies View RelatedIs there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
I used the code in the link for "Create Worksheet Index" you referenced and it works great. Is there a way to have the Index and the "back to Index" links appear in separate stationary windows on the left side of the spreadsheets?
View 5 Replies View Relatedi have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View RelatedWhen I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
I open a txt file in Excel and when I have filter and modify the data I save it to csv-format.
The file include a Header with information from A-AL. I have information in the first 17 columns, A-Q and the rest of the columns are emty, R-AL.
The thing is when I look at my saved csv file my semicolon stops after row 15?! The semicolon represent each empty column from R-AL so I want this on all of hte rows.
tell me how to separate one column into 2 if there is ; in between.
View 2 Replies View RelatedMy Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies View RelatedI have many rows with dates I want to add a semicolon to. I do not want to type in each one, so I looking for a formula or something that will do this automatically.
I've tried formula, =A1&";" but this changes the format.
3/17/201341350;
I have two lists of over 10,000 text messages each. Each text is in the first cell of the column, with a few random rows being skipped here and there. Within each cell is a lot of information. The information is divided by semicolons, though. So, it's something like. 123;ContactName;+123456790;Date Time;Message
I have two files like this, one incoming and one outgoing. I need a way to not only split the text into different cells (each piece in a separete column), but splice the two files together. I realize if I could section the text into the parts, I could just sort it, but I figured it's better to tell more than less, for the sake there might be an even simpler way of doing it.
I have a 28 or 30 columns excel with categories of products (color, size, price, etc...) and I need to have and extra column that has all the previous categories separated by semicolon ";"
Values could be numbers or words, mostly words
Price | color | size |...etc
50 | green | 24 |...etc
generated new column
50;green;24;... etc
If a column is empty then go to the next column, no zero value or two ";;"
I have 20.000 products, so 20.000 rows
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........