I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?
I have a situation where I have source data formatted like this:
Document ID Document Name Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:
A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...
What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with
A1: A-dec International Inc. A2: A. Bellotti A3: A. DEPPELER S.A.
I'm having troubles with a spreadsheet and unfortunately don't know enough VBA to fix it. I have two columns, each with data like so (standard text) separated by a line break:
Object1 Object2 Object3 value1 value2 value3
And I need them separated like so:
Object1 Value1
Object2 Value2
Object3 Value3
with other items in the row from other columns applied to the new rows accordingly.
Any script/macro/tool (even non-Excel) to do this effectively (over 800 rows)?
I have 300 rows worth of data that looks similar to this, all organized in one column:
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Snider Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555 Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555 Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g: A2: aa,ab,ac
String Value aa 1 ab 1 ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
I'm trying to split the contents of a cell to a new row (with data from other columns on the row copying across) below the originating cells row. Most of the split cell macro posts I've seen split the contents to a new row at the bottom of a range, however I'm looking to split the contents to a new row directly beneath the original row where data will already exist. I don't want to copy over this data I just want to push the data down depending on the number of rows created.
The contents in the cell being split can vary (1:n), so the number of rows needed to be inserted depends on the count. The majority of the contents in the cell is separated by a line feed (Alt Enter) but some may be separated by a tab or comma.
Essentially what I'm trying to achieve is something from this A B C D
I have an address in one cell in excel. Street/City/State/Zip are all separated by two spaces like this: 123 Anywhere St Chicago IL 60607 United States
I want to break them out into separate columns. The "Text to Columns" won't work because there are no commas or dashes separating the data.
I need to split up the "master" table so that for all rows where DOL1 falls with a year range of e.g 6th April - 5th April (in any year) then this row is moved or copied into a new tab based on the year range.
i.e. The first row should appear in a new tab for the range 6th April 1978 - 5th April 1979. The marco should be ale to create and name new tabs. The tab name will be 78_79.
The second row should appear in the 87/88 tab.
I have several thousand rows of data with dates ranging from 1978 - 2012 so there should be tabs populated for every year from 78-2012.
We are an office furniture company and we want to hold a master list of all stock. The columns we require are:
Product ID Product Description Product Grade Product Price Sold
The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.
The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.
I have tried to split using KuTool and ASAP Tool but they dont work. They can only split base on #of row. Between each of the group of my data, there is a empty row that split them. I want to split every single row from A:H to a new workbook. Why A:H becuz from A1:A8, the A5 is empty. It has to be in range, specify by me, so if the condition is met as empty from A:H, then split from there and keep going on the whole spreadsheet.
I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.
I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.
The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".
The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?
As an extra point, I'd like to use column B to check for blank rows (not column A).
I am receiving a new report from an external system. Unfortunate the system delivers many information within one cell, and it is not possible to reconfigure. These information are separate by a semi-colon or a semi-colon + space. separate the information in separate cells?
I have Column D containing multiple values that I need to split. The structure is:
Text Text (XYZ) 123 AB C Text 456 C Text Text (RST) 7 A CD EF Text 22
I'd like is to split the cell using a macro rather than formulae so that the resulting cells can be filtered.
The output would be:
Text Text (XYZ)|123|AB|C Text|456|C Text Text (RST)|7|A|CD|EF Text|22|
So the first split would need to be when the number is reached, the second split after the number and then subsequent splits each time a space is encountered (although there may not be any values after the number!).
how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?
