Split Range Data For CSV File
Jun 5, 2008
I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)
I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)
I need to have the following columns in the CSV file populated using the Excel spreadsheet
Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)
Column B: Will need the phone number
Column C: Will need the fax number
Column D: Will need to have the email address
Column E: Will need to have the date only
Column F: Will need to have the Order Number
Column G: Will need to have the Customer name
Column H: Will need to have the Customer telephone number
Column I: Will need to have the Customer alternative telephone number
**Column J: Will need to have the 1st line of the address (after First Address in xls doc)
I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need (see following macro)
Sub CreateCSV1()
Dim wbMyBook As Workbook, iAddPos As Integer
Application. ScreenUpdating = False
Range("B2:B11").Copy
Workbooks.Add
Range("A1").PasteSpecial (xlPasteValues), Transpose:=True
Range("A1") = Left(Range("A1"), InStr(Range("A1"), " ="))
iAddPos = InStr(Range("J1"), "TEGKON:")
I've attached the xls document (the one that is generated and the CSV file which is the format I'm trying to achieve.)
**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank ( null) value in the cell
View 4 Replies
ADVERTISEMENT
Jul 31, 2006
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.
View 9 Replies
View Related
Feb 16, 2008
Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).
For example (Quater 1 is the document name)
R:ManagementJohn2008SalesSectionAQuater1
I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.
For example
R:ManagementJohn2008SalesSectionA
View 9 Replies
View Related
Dec 15, 2008
QB03654338AGLQLD210/06/2007 0:0014/02/2008 0:00
QB03654338AGLQLD215/02/2008 0:0010/04/2008 0:00
QB03654338AGLQLD219/08/2008 0:0031/12/9999 0:00
to this:
QB03654338AGLQLD210/06/2007 0:0014/02/2008 0:0015/02/2008 0:0010/04/2008 0:0019/08/2008 0:0031/12/9999 0:00
So adding it to all to one line if it is the same ID number "QB03654338". See the attached file.
View 2 Replies
View Related
Feb 14, 2014
A have a CSV file which contains a string of data that has a repeating pattern e.g
1234561234560N12343159753 00Company 123 Mr Smith 9874561236540N12343159753 00Company 432 Mr Jones 654987159753680N12343159753 00Company 431 Mrs A Bruce
The first long string of numbers are bank details (Sort Code, Account Number) followed by our company bank details then company name, then a reference number then the name of the account holder.
There are over 20000 records all in one long string of text and all contained within cell A1. There are also a few occasions when there is no space between the name of the account holder and the start of the next persons bank details.
What I need is for there to be a split after each account holder's name and for each persons details to be on a new row e.g
1234561234560N12343159753 00Company 123 Mr Smith
9874561236540N12343159753 00Company 432 Mr Jones
6549871597530N12343159753 00Company 431 Mrs A Bruce
Ideally I would also like the first string of information to be split in to columns so that it reads as so -
123456 123456 0N12343 159753 00Company 123 Mr Smith
987456 123654 0N12343 159753 00Company 432 Mr Jones
654987 159753 0N12343 159753 00Company 431 Mrs A Bruce
The first collumn would contain the first 6 digits, the 2nd would always contain the next 8 etc. I have tried using the Text to Columns function but it gives me seperate columns for initals etc when I would like the full name and title in one column.
View 5 Replies
View Related
Mar 31, 2012
Below code is for splliting text file into multiple excel worksheet. I want to modify it to splitting text file into different msword documents in folder. Is it possible to change the code
Sub LargeFileImport()
'Dimension Variables
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")
[Code] ........
View 3 Replies
View Related
Dec 17, 2013
I have an excel spreadsheet that has over 10 thousand rows and is 40+ Mb in size. It is giving me timeout errors when trying to import it into Joomla 2.5. Therefore, I need a way that I can easily split the file into 5 smaller files. Here are the column headers: titlemetadescmetakeyaliascategory_pathstateintrotextaccess
I tried doing it manually but is said the cell sizes were different...
View 3 Replies
View Related
May 16, 2011
I have a file, with this dummy data
Fruit Country QTy
Apple India 100
Apple USA 200
Orange India 150
Grapes Aus 100
Grapes India 100
I need to macro, wherby it will create 3 files namely apple, orange,grapes and store the relevant rows.
Hence I need a macro to split file into different files based on different values in a column and accordingly name it .
View 9 Replies
View Related
Dec 12, 2009
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: ...
View 9 Replies
View Related
Nov 25, 2009
I have a macro that first refreshes a specific data range that imports data from a text file as such:
View 2 Replies
View Related
Jun 13, 2013
I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.
Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.
VB:
Sub Create_Subfiles()
Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String
[Code] .....
View 3 Replies
View Related
Jan 10, 2014
I need to write a macro to split an excel file into separate workbooks and automatically email our project managers.
View 1 Replies
View Related
Sep 26, 2007
I have a variable list. Each column will be 250 digits and numbers of rows will be variable. I want a macro to import a text file from a specific folder into a sheet without split it to columns. So we will work only in column A
Then macro will find B1002 wording in A1. if it is exist, it will copy the next 36 digits after B1002 wording if not then it will search A2 row. The next step will be to search and find another wording "B1001" if it finds it will replace copied 36 digits text. If it can not find B1001 wording it will go to upper row and search B1001 wording here and paste the text. This will go on till row shows #END. This means it reached the end of the list. And then macro will save this file as text file to another folder.
So macro will go to beginning to open other file in the folder and this will go on till last file in the folder.
This is the logic of the macro. Here is the sample of what I want.
Original Data: ....
View 9 Replies
View Related
Dec 22, 2008
I was multiplying 2 ranges in an formula to get a cell's value. The first was a set of user inputs and the second was an array of constants. Recently I needed to add another field in the middle of the user inputs which subsequently broke the range.
View 4 Replies
View Related
Jan 7, 2010
Kindly see mentioned below table, I want to know that how should a macro split range in col a as per its count, if in each box contains qty 500, as result shown in cols d, e, and f which I did manually by writing Level, qty and box#.
View 12 Replies
View Related
Aug 17, 2006
I need to split the = Now () in Cell Range F1 ( custom format is ddmmyyy) then seperate or split each so,
in Cell Range H1 is dd
in Cell Range I1 is mm
in Cell range J1 is yyyyy
Is this a formula or a macro function ?
I was able to use the information contained, therefore a similar question I had
was not required to be posted twice. This place ROCKS just by reading other posts.
View 2 Replies
View Related
May 18, 2014
THE FORMULA - IT FINDS THE LAST-OCCURRING NUMBER IN A ROW, AND ADDS 1 TO IT IF A CELL LOWER DOWN IN THE PRESENT COLUMN CONTAINS TEXT:
Here is a formula to find the previous occurrence of a number in Row 1, and to ADD 1 to it if in the same column (Column Z) as the present cell (Z1), there is NOT a blank in the specified cell (Z5)
=IF(ISBLANK(Z5),"",LOOKUP(10^100,$C1:Y1)+1)
This is quite nifty. Basically if Z5 says "go!"
then Z1 will find the last number in Row 1
- let's imagine F1 contains "300" as the last number so far in Row 1 -
and add 1 to it
- in this case Z1 will thus say "301"
THE NEXT CELL:
With each next cell in Row 1, the formula moves up an increment, except where specifically told to freeze ($). Thus Cell AA1, the next cell in Row 1 after Z1, will have the formula:
=IF(ISBLANK(AA5),"",LOOKUP(10^100,$C1:Z1)+1)
THE PROBLEM:
I don't want to extend Row 1 beyond Column Z. I want my spreadsheet to be more compact. So l want to continue the formula on another row. Let this be Row 43.
So, how do l set the formula to LOOKUP the last-occurring number in Row 1 between Column A - Column Z (i.e. $C1:$Z1) but then - if no number is found in that range - continue the search in Row 43 between Column C - Column F for example (i.e. $C43:F43)?
And how many times can l introduce a new row into the range within which to find the last-occurring number?
View 10 Replies
View Related
Apr 24, 2012
How do i split some text in range if it does not have a delimiter? I would like to have a comma after each letter/number or have the option to split to individual cells
I know how to use the vba split function with a delimiter but cant figure this out.
I.e susanna
S,u,s,a,n,n,a.
View 4 Replies
View Related
Mar 21, 2013
I'm trying to split an amount within a given month:
Customer
From
To
Total number booked
Total amount booked
A
3 March 2013
5 May 2013
458
£5,000
Therefore, assuming that the number and amount booked is done evenly on a daily basis and considering the fact that in March, the period is 3 March - 31 March, while in May it is 1 May - 5 May, is it possible to have a formula that calculates the amount by month?
I have been using =D2/(DAYS360(B2,C2)) to find out the daily number booked, then multiplying this by the days in a given month to find out the monthly value, but is there a more elegant way of doing this where I can define the month in another sheet (Col. B), to return the following in Col. C and D?
Customer
Month
Monthly number booked
Monthly amount booked
A
March 2013
207
£2,258.06
A
April 2013
214
£2,338.71
A
May 2013
30
£322.58
View 2 Replies
View Related
Sep 28, 2008
i have a main.xls file and two data file dat1.xls and dat2.xls
mail named file have ar - br- cr- dr- er -fr sheets
dat1 named file have ar-br-cr sheets
dat2 named file have dr-er-fr sheets
and all this files data source is
colomn source a - fd
row source 29-4000
i want to make two commandbutton to main file first for dat1 second for dat2 file and i need a code to use at this buttons to make
when dat1 and dat2 close
main file user when click first button
copy dat1 file ar sheet colomn source a - fd row source 29-4000 cells to main file ar sheet colomn source a - fd row source 29-4000 cells
copy dat1 file br sheet colomn source a - fd row source 29-4000 cells to main file br sheet colomn source a - fd row source 29-4000 cells
copy dat1 file cr sheet colomn source a - fd row source 29-4000 cells to main file cr sheet colomn source a - fd row source 29-4000 cells
View 9 Replies
View Related
Jun 15, 2006
I am trying to make Excel pull data out of a closed file based on a date range. The closed file is a log where people input data and enter the day they are doing it. I need to pull this data in my file and make it print into my worksheets so I can pass a report on to others with only the data they request from the log.
The range would be for 7 days and would have to be able to choose a few columns off this report.
View 9 Replies
View Related
Aug 28, 2009
The following code takes a large range of data (currently 20K+ rows) and breaks it out into separate worksheets. This takes a while, and I have been trying to insert a progress bar into this macro, but the progress bar goes in reverse (from 100 to 0) but the userform will not unload at the end.
Sub MoveCells()
Dim objBook As Workbook
Dim objSheet As Worksheet
Dim lngRowSpace As Long
Dim strName As String
Dim lngTimeRow As Long
Dim lngStartRow As Long
Dim lngInteration As Long
Dim strDataSheet As String
Dim boolError As Boolean
Dim counter As Integer
Dim PctDone As Single
View 9 Replies
View Related
Mar 25, 2008
I'm trying to separate a cell containing:
TSB w/6.5% NaCl - NS 29002TSB w/6.5% NaCl - NS 29003TSB w/6.5% NaCl - NS 29004
into a 2x3 table, in another sheet, like this:
TSB w/ 6.5% NaCl NS 29002
TSB w/ 6.5% NaCl NS 29003
TSB w/ 6.5% NaCl NS 29004
TSB..... in left column and NS...... in right column. This example includes 3 items as shown above but the single cell could contain more or less than 3 items
View 7 Replies
View Related
May 20, 2008
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
10/05/0809:14:00 AM
10/05/0810:18:00 AM
10/05/0810:42:00 AM
View 10 Replies
View Related
Mar 6, 2008
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
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".
#########################################
Part 2:
####################################################
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".
####################################################
View 6 Replies
View Related
Jan 23, 2012
I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.
sample data...
Start Date
End Date
Old Value
New Value
08/03/2010
18/06/2010
16758.2
16758.1
[Code] .......
I need to break down the total number of days per month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
24
30
31
18
[Code] ......
As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...
I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.
(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).
View 8 Replies
View Related
Mar 15, 2005
I'm trying to take a large text file and break into smaller text size files. I want to open the data file, and parse into 5000 line smaller files.
Here is what I have based off of some MS KB
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim CounterMax As Double
Dim sPath As String
Dim FileCounter As Double
'Output File
View 9 Replies
View Related
Jul 12, 2012
I have a set of data that has cost associated with it that is broken up into several categories repeatedly. The subcategories are NOT defined to a specific set of subcategories i.e. they can be anything. The subcategories may also be alphanumeric and have spaces/other special characters in them. What I want to do is to be able to pick the cost and slot it in the appropriate category in the main sheet.
The exact number of rows and columns in the data is not fixed so it would be great if this is editable in the VBA code depending on how many rows and columns the final data set has.
An example is attached. In the example I have put all the cost as 1 for simplicity but in the actual document all the costs are different values. Also in the example I did not put any category as alphanumeric or with spaces/special characters but in the actual data there may be.
The logic I can think of is to first identify all unique values in the array, insert them as new rows, then add up the cost values (this is just a suggestion).
Book1.xlsx
View 9 Replies
View Related
Feb 19, 2014
I have a blank file and a data file for current year with formulas.
So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.
My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.
Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.
View 3 Replies
View Related
Nov 28, 2013
I am trying to find away to split up data that is entered as text into a cell,and split that text data into 3 columns. Sample text enters in a cell can be:
10-3 or 1-3-1 or 12-11-0 or 1-6 or 8-8.
As you can see, the text entry can have two or three numbers in it. I would like to split this data so it looks like this: 10-3 would be cell A5=10, cell A6=3 and cell A7 is blank.
Another example would be 1-3-1 would be split A5=1, A6=3 and A7=1.
I know about the text to column function, but that is not the case as I want the data to split automatically with a formula.
View 3 Replies
View Related
