Split Cell Data In Multiple Rows
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 ?
View Complete Thread with Replies
Related Forum Messages:
Split Cell Into Multiple Rows
I have a datasheet that has more than 80,000 rows, Current format is:
Coulmn A - Column B
prod1 - <option selected>abc</option><option>def</option><option>ghi</option>
I want it converted into:
Coulmn A - Column B
prod1 - selected
prod1 - abc
prod1 - def
prod1 - ghi
Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter
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.
Split Single Column Into Multiple Columns And Rows
I have 300 rows worth of data that looks similar to this, all organized in one column:
John Q. Smith
2111 NW 13th St
Anywhereville, USA, 55555
Joe P. Snider
5645 NW 45th St
Anywhereville, USA, 55555
6454 NW 34th St
Anywhereville, USA, 55555
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.
Split Data In A Cell And Get The Value For The Split Data From A Different Table
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:
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.
Split Data In Multiple CSV Files Based On Condition
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.
Split Data Within A Cell?
My data within a cell has 3 values separated by commas i.e. abc, def, ghi. I want to separate these values into their own cells. Is there a formula or command that can do this? of course I can go into each cell and cut and paste the values onto another cell, but I have thousands of cells that need to be reformatted.
Split Data From One Cell...
I'm working on a datasheet that needs some tweaks...
In column A, I have these data
how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits.
I am thinking using Left and Right formula, does that sound right?
Split Cell Data Into Two Columns
I am trying to split data from a cell into two columns. I have tried the LEFT and RIGHT functions but as the data held is not always the same number of characters this does not work for the LEFT function. The only constant thing is that the last character will always be in the second column. An example of data would be:
and I need to be split into 2 columns as follows:
Column 1 Column2
Split Data From 1 Cell Into 2 Cells
I have a list of first names and surnames, the first name and surname are in one cell together but seperated by a commer.
For example in A1 would be Steve, Jones
What I want to do is have this split into two new cells. So in A2 I would have his first name and in A3 his surname. Formula to do this is what I cant get to work.
Split Data In Single Cell To Other Cells
What I got going on is a program I use for reporting is shooting out information.. while most of the information it sends to an excel workbook is sectioned by cell, there is a good chunk of information that's dumped into 1 cell. Is there a macro, or something, I can do to separate the information in this single cell, into separate cells? Auto Merged Post Until 24 Hrs Passes;Here is an example of the report. You'll notice a bunch of information in the 'Description' section. What I want to be able to do is run a macro that will seperate the 'Item Number', 'URL', 'Serial Number', 'Model Number', and 'Details' information into seperate cells.
Split Multiple Names
I have and issue where I have name in one cell and need to separate them into 2 cells. Trouble is it is a combination of single names, husband and wife, and partners with different surnames. Sample of data is below:
Belinda Smith & Grant Jones
Ken & Marie Smith
Louie & Betty Johnson
Naomi Thomsoon & Craig Brown
Narele & Barry Day
Nicholas & Julie Smithson
S M Bole & Co
Danielle & JohnSimpson
Split A Column Into Multiple Columns
The spreadsheet contains over 21,000 rows of data, and one of the columns (D I think) contains data as in the two examples below.
What she wants is to split this column at the semi-colons ( and have the column header as the "field" name.
Unfortunately not all the cells have the same number of "fields" as you can see. Some don't have an "addressLineTwo" while others also have "stateprovince".
Is it possible to split the column so each "field" goes into it's own column?
Please note that if a "field" is missing there is not two semi-colons to indicate an empty field. I'm also fairly certain that, between them the two examples below show all possible fields.
addressLineOne:Road Belen Staana;addressLineTwo:Costado Oeste;city:SAN ANTONIO DE BELEN;highRate:194;latitude:9.97631;longitude:-84.20038;postal4005
addressLineOne:1766 Homestead Drive;airportCode:ROA;city:HOT SPRINGS;highRate:500;latitude:37.99662;longitude:-79.83079;postal24445;Rating:52;stateprovince:US
Didn't there used to be a "Split" function that split text over two cells? I'm sure I used it years ago, but can't find any mention of it in Excel 2003.
Split Workbook Into Multiple Workbooks
I'm attempting to take a Workbook that contains 1000 Worksheets and split it into 2 separate WB's of 500 WS's each ... basically half it. This could also apply to a WB that contains less than 1000 WS's, but it is highly unlikely it will ever contain more than 1000 WS's.
I'd like each new WB created to be saved in the original path with the original filename followed by a "1", "2", "3" or "a", "b", "c", etc. For example:
Original Filename = ImpactAnalysis.xls
New Filename 1 = ImpactAnalysis (1).xls or ImpactAnalysis (a).xls
New Filename 2 = ImpactAnalysis (2).xls or ImpactAnalysis (b).xls
I've located 2 modules in the forum that are a good start, but each creates a separate WB for each WS, which is a bit cumbersome with up to 1000 WS's in the original WB.
Dim sht As Worksheet
Dim w As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Worksheets
Set neww = Workbooks.Add
With neww .....................
Split Worksheet Into Multiple Worksheets
This code splits a worksheet into multiple sheets (based on sales person in column A). But when it does it, it deletes the sheets and then re-creates them.
However this messes with my formulas I have linked to the split sheets and turns them into #REF! errors.
Prevent Text Being Split Down Rows On Import
I have a large sheet that I am having some problems with. The information comes from our database. In column A I have a client code and other information over to column D. In columns E I have dates. These dates have comments that are associated with them entered in column F. The problem is that sometimes these comments wrap into the next cell below. I have done this in the past. But the problem is that in column G there are dates of when the action took place, the issue is that there might be 1 or 10 of these dates per client. And then in Column H there are comments as well that spill into the next cell below.
What I need is to have everything in the first line, the client code over to column E, Combining of the cells in column F that were broken over multiple cells, then in Column G the first date and its combined actions to the right. The next row might have no client code but might have another date in Column G and comments in column H.
This is really hard to explain what I am looking for exactly. I have attached a small file that has an example of what might be downloaded and then a sheet of what I need it to look like when it is done.
Split Address Blocks Across Multiple Cells
I have cells of block addresses that are delimited (tab I believe - there's a
small square that appears).
I need to split the addresses out into separate columns for each line of address. I tried using the Text to Columns wizard as suggested by Excel Help, but ended up with only the first line of address being posted in the cell to the right (the rest of the address was nowhere to be seen).
I'm sure I've done this before but can't for the life of me remember how I did it.
Split Multi-tab Spreadsheet Into Multiple Spreasheets.
I have a spreadsheet with 40 differents tabs. As an example the first 5 tabs are named as follows:
tab1 name = apples
tab2 name = oranges
tab3 name = beans
tab4 name = pears
tab5 name = grapes
I need to create 40 different workbooks. To continue my example, I need 5 new workbooks as follows
workbook1 name = apples
workbook2 name = oranges
workbook3 name = beans
workbook4 name = pears
workbook5 name = grapes
For each workbook, it only contains the data for that tab.
Split Function: Multiple Spaces Between Two Words
I am trying to parse a formatted text file. I am using the SPLIT function as
arr() = split(cline," ")
where cline is an input line. if the line input(cline) has multiple spaces between two words, how would it split that.
abcd defg fefdcs fasdfasdsa
would the output of above be
arr(0) = abcd
arr(1) = defg
arr(2) = fefdcs
arr(3) = fasdfasdsa
Split Worksheet, Seperated By Blank Rows, Into Several Worksheets
how to split one large worksheet into several worksheets using VBA. The only criteria I have to go on in the large worksheet is the existence of blank rows between each chunk of data I want on it's own worksheet.
Please see the attached zip file. There are 39 chunks of similar (but varying in number of rows) data that are separated by 3 blank rows, so I want my workbook be split into 39 new worksheets. My goal is to get the data into list/table format so I can work with it more easily and export into Access etc.
Also, just out of curiosity, is it possible to convert a HTM file into CSV? The attached data starts life as a HTM file that I just convert to XLS
Split Large File Into Multiple Smaller Files
On this forum, a script is provided to break a large Excel file into smaller 500-line files:
Split Worksheet Into Multiple Files By Every Nth Row
I copied the code and pasted it into the VBA editor in Excel 2007, but when I run the macro, it generates an error message:
Runtime error 91: Object variable or With block variable not set.
The line the debugger identifies as the one with the error is this one:
For lLoop = 1 To rLastCell.Row Step 500
Here is the full code from the previous thread: ...
Comparing Data In Multiple Rows
I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).
I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is
(donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).
How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.
Merging Multiple Rows Of Data
I have several worksheets in a single workbook.
I have the following
employee paymethod earningcode earningtype rate hours amount
jane doe H CR R 5.00 80 400.00
jane doe H CO O 7.50 2 15.00
jane doe H HL R 5.00 8 40.00
jane doe H SE R 10.00 10 100.00
john smith H CR R 6.00 10 60.00
I need to have
jane doe CR 5.00 80 CO 7.5 15.00 HL 5.00 8 SE 10.00 100.00
I will have multiple employees. Not all employees will have all 4 earning codes (some will only have one, some will have two, and some will have three).
Sort Data Into Multiple Rows
I'm trying to merge 2 spreadsheets togeather but befor I can do this I need to resolve the following issue - my Excel worksheet has 2 columns with data that looks like this
I need it to change to this
Combine Data From Multiple Rows
so basically, here's an example of what I have. The spaces in between represent different cells:
Name Corporation Type Group
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3
What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3
In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.
Import CSV Into Worksheet Split Into Columns Then Copy Rows By Criteria
I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post.
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum .................
I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.
Extracting Data From One Massive Worksheet To Split To Smaller Sheets Of Data
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
Data From Multiple Rows Into Single Column
I am trying to take data from multiple rows and columns (matrix) and reformat it all to fit in a single column.
It would be great if I could select the cells I want to reformat into the single column as the number of rows and columns containing the original data is not always the same (ie 20x20, 21x35, 56x200) etc...
Identifying The Same Transaction With Multiple Rows Of Data
I currently have a database that is similar to the example I've pasted below:
As you can see the Date and Time are repeated for several rows. This is how the data I receive comes through as A,B and C refer to a single transaction and D & E refer to another transaction.
Ideally what I would like to acheive is:
Where Excel can compare the date and times of each row and look for matching rows above and below it and then fill in a column next to it indicating that x number of rows are linked to a single transaction - preferably labelling them in some order to I can tell how many transactions there are.
Consolidate Multiple Rows Of Data Into Single Row
I imagine my goal could be achieved via some scripting code, but, alas, I don't do vbs. Here's the situation:
I have a spreadsheet generated by another office which lists individuals' names and information about participation in various programs. Each line lists name, address, program title and amount. The next line may be the same name, address with a different program and amount. Each person may have 4-5 entries. I want to make single entries for each individual, with colums showing the programs and amounts.
ID Name Address Program Amount
1 Bob home A 25
1 Bob home B 37
2 Dave home A 22
2 Dave home B 10
2 Dave home C 21
ID Name Address Program A Program B Program C
1 Bob home 25 37 0
2 Dave home 22 10 21
Hopefully this makes it a bit more clear. The only way I know how to accomplish this is the "old fashioned" long hand approach of cut & paste. There are several thousand individuals, with (currently) tens of thousands of rows.
Macro To Sort Multiple Rows Of Data
I have a report that I drop down into Excel that is a transaction report for securities. The data for each security is in two rows. I would like to write a macro that would sort each two-line group by a certain cell in the group. If I could make the macro request the number of rows and columns in each range and the cell address of the cell to sort by, I would be able to use this macro for many different reports. Also, there has to be some way of telling the macro where to begin and where to end. I have some experience with macros although generally I "Frankenstein".
Merge Column Data Over Multiple Rows
I know how to merge data in a CSV (see attachment) from 2 columns into a single cell using =A1&" | "&B2 so that A1 (Safety Products) plus B1 (Fire Protection) becomes Safety Products | Fire Protection in cell C1, but how can I do this across multiple rows so that each pair of names is combined in each row? The job I'm working with is a product CSV file that has 6370 lines so I don't want to do them one line at a time!
Organize Data From Multiple Rows Into Single Row
I would like to automate, due to how long it would take to manually organize the data myself. I have all of the raw data needed organized into 3 columns. The problem is that some of the related data is divided up into multiple rows, based on ID number. What I would like to do is take the related CPT codes and respective descriptions, from the same ID number, and place them on the same row. I am going to include some pics and even a small chunk of the source data. Here is a pic of the raw data:
And here is the first 3 already done:
The number of CPT codes per ID number range from 2 - 5 I believe, maybe 6. Not sure if this has any bearing on how it is done.
Non Consecutive Rows Sort Data With Multiple Ranges
Once again, I'm seeking some further assistance to an issue that I have yet to resolve.
I have several rows of data which I'd like to be able to sort by the info in column "A".
The data rows are not all consecutive. The range for which the data resides are:
Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.
Copy Multiple Rows Data To Single Column
transposing and sorting data into multiple columns.
Column 1 for example will have the Parent's Name. Column 2 will have the Children.
If Mark has 3 children, X Y and Z
Jim has two children, A and B
then I want Column 1 to display Mark and Jims Name and column two should display all the children
Here is how I want the data:
Column1 Column 2
Please see attachment.
Sum With Multiple Criteria A Number Of Rows And Columns From Original Data
I have 6 columns of data laid out as follows,
columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
Split 1st & Last Names & Split Addresses After 1st Comma
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".