Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Convert Space-delimited Data To Columns


I have a software package that requires serial number effectively data to be entered in a particular format.

As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic.

The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,

The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.

I would like the data to end up in two separate columns as shown in example 2.


Example 1 (Starting format)
* indicates space
- indicates a range, this needs to be separated into two separate columns

2252*2254*2256*2257*2259*2272*2274-2276*2278*2280*2282*2284*2286-2641*2643-2681*2683-2712*2714-2717*2719*2721*2724*2726*2727*2729* 2733*2735 *2738*2739*2746

Example 2 (Finished format ready to be paste into software package
2252
2254
2256
2257
2259
2272
2274 2276...............................


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Open Txt Files Delimited, Space
On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:

"This file is not in a recognizable format" - I click OK
"Delimited or Fixed Width (default)" - I change to Delimited
"Delimiters listed with Tab as default" - I change to Space

I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.

Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.

I am using Excel 2000.

View Replies!   View Related
Covert Space Delimited String To Array
I have two columns of data, one for Position, and one for description.

The positions are concatenated and separated by spaces. E.g. C1 C2 C3 for each Description. I need to create a separate row for each position

So I need to convert:

Pos Decscription
C1 C2 C3 Cap 100n 10% X7R 0805

to:

Pos Decscription
C1 Cap 100n 10% X7R 0805
C2 Cap 100n 10% X7R 0805
C3 Cap 100n 10% X7R 0805

Or in other words split a space delimited string into an array, so that I can loop through the array, and add new rows

View Replies!   View Related
Separate Space$ Delimited String Into Multiple Cells
I am trying to split a string into separate cells. I have managed to generate the formula for the description and first dlr value in the string but I am have trouble figuring out how to build functions for the rest of the string.

Example attached.

View Replies!   View Related
Remove The Extra White Space Between Each Comma Delimited Digit
This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345

My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:

"A B C 12345"

So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?

View Replies!   View Related
Transpose Data From Rows To Columns, Export To A Tab Delimited File
I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.

There are no spaces between entries.

View Replies!   View Related
Convert To Tab Delimited
I attached a txt file of a report we get out of our accounting system. It is not tab deliminated, when I open it in excel, each whole row is contained in 1 cell instead of being broke up into columns. Im not sure how to convert it, im sure it possible using vb or something?

View Replies!   View Related
Convert .xls To Pipe-delimited .txt File
I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:

A|123|
B|456|

currently there is no PIPE following the last character (3 or 6) and i am getting this:

A|123
B|456

I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record. Here's the ...

View Replies!   View Related
Convert Comma Delimited Cell To An Array
{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))}
I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.


I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.


{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.


View Replies!   View Related
Convert A Column Into Comma Delimited List
I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.

What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.

Example:

The list (starting in A1) looks like this:
Dog
Cat
House
Car
Boat
Mom
Dad

but I need:
Dog,Cat,House,Car,Boat,Mom,Dad

If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.

View Replies!   View Related
Transpose Or Convert Rows Of Data Into Columns Of Data:
When 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)?

View Replies!   View Related
Convert Data From Rows To Columns
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.

View Replies!   View Related
Convert Columns To Rows With DATA
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 Replies!   View Related
Convert Data From Columns To Rows
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.

View Replies!   View Related
Convert The Each Item [data] Above Into Separate Columns
I have a 45 page spreadsheet with over 3500 contacts and the data is currently listed in individual cells as seen below:

John Smith
Director of Business Travel Sales
ABC Hotel
1200 Market St.
Philadelphia, PA 19107
Phone (215) 555-1234
Fax (215) 555-4321
jsmith@abchotels.com
www.abchotels.com

I want to convert the each item [data] above into separate columns so I can then save it as a CSV file and then export th data into an email list; but I have no idea how do to this.


View Replies!   View Related
Splitting Comma Delimited Numbers Into New Columns
I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View Replies!   View Related
Convert One Column Of Data To 257 Columns Of Data
I have exported a text file from a universe database and need to import it into excel.

Below is an eg of one section. The text file contains approx 257 of these, all one below the other.

Between each set of dotted lines is one user profile (not data all shown in eg) .

Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user.

A simple import places all data into one very long column.

I need a way to place each user in there own column.

I have user names as column headings the labels of the values below as row headings. I cannot change this.

Manually entering the data took a very long time and i need to update this workbook regularly.

In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon.
This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user.

Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column.

The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated.

Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book.

Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated.

One EG user profile (not shown, but all the colons line up) ....

View Replies!   View Related
Create Individual Comma Delimited Cells From Delimited Cell
I need to create a comma delimited list based on variable start and end values for each row.

StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008

I'm not a VBA expert, or I would have created a Do While or For Each loop.

View Replies!   View Related
Convert Text Delimited File To .xls File Using Vba
how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.

Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.

Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.

To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.

View Replies!   View Related
Convert Csv Files To Tab Delimited Files
I would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user

View Replies!   View Related
Delimited Data In Worksheet
I have a worksheet that has around 2000 rows each containing data as shown in the attached sample sheet. I want to replace all the squares (is this some form of delimiting or seperating character inserted as the data was imported?) with a space and leave the other data intact.

I've tried find & replace to do this without success, although editing each individual cell works. Please let me know as I need a way to do this for all cells at once as it would take an age to do each cell indivudually.

View Replies!   View Related
Tab Delimited Data From Web Page
I am trying to use VBA to manage invoice generation via an online service. The service is set up in such a way that the process cannot be made completely automatic, but I am trying to reduce it to as few manual steps as possible. Hence I am opening the Web Browser control in an Excel userform to streamline and guide the user's process. The invoice is generated as a Tab-delimited TXT file. I don't want to save the file to disk, and selecting 'Open' opens the file as a web page, rather than in Notepad or Excel.

One way or another this data needs to be transferred to a hidden Excel worksheet for further processing. So I am trying to devise a way to copy the data from the web page and paste it onto the Excel worksheet. Here is the relevant code from within the Web Browser's class module:

If URL Like "https://www.fedex.com/FedExMMA/downloadcenter.do*" Then
Application.Wait Now + TimeValue("00:00:01")
SendKeys "^a^c", False
Application.Wait Now + TimeValue("00:00:01")
ActiveWorkbook.Sheets("Sheet3").Paste Destination:=ActiveWorkbook.Sheets("Sheet3"). Range("A1")
End If

The "^a^c" (control-A, control-C) part works correctly; if I interrupt the code at that point I can open Notepad and directly paste the invoice data. But pasting into Excel fails in a way that has me baffled. Instead of the invoice it pastes the previous contents of the clipboard; for instance the invoice number that I copy and paste in an earlier step. I can even run the code as written above, see the invoice number pasted into Excel, and directly open Notepad, type Control-V, and have the actual invoice pasted into Notepad! It is as if Excel's paste function accesses different clipboard data from other applications. So my brief question is: How do I get the data into Excel? The longer question is: Is there some step I have missed that specifies to Excel what clipboard data to paste?

View Replies!   View Related
Linefeed-delimited Data Extraction
I'm trying to determine a way to populate a listbox with the contents of a few cells full of vblf-delimited information.

It's almost working properly, but placing the first entry in the target cells last in the listbox and have no idea why it's behaving this way.

View Replies!   View Related
Parse Delimited PDF Data Into Worksheet
I have a PDF document with an income statement that I'm trying to bring into excel. When I copy it, it puts all of the information in 1 column like this: NET INCOME 183,450 (237,660) 421,110 177 (1,461,273) (1,769,800) 308,526 17
CAPITAL EXPENDITURES Land Improvements 0 0 0 35,000 0 (35,000) Building Improvements 7,500 0 (7,500) 243,129 626,500 383,371 61. I cannot use text to columns, because the CATEGORIES are different in length (3 to 4 to 5 words at times).

I can seperate the numbers from the words, but I can't seperate the numbers from one another. Is there any way that I can do this via a text fuction?

*Example*
0 0 0 35,000 0 (35,000)
7,500 0 (7,500) 243,129 626,500 383,371 61
29,881 310,460 280,579 90 380,580 1,556,576 1,175,996 76
0 83,248 83,248 100 76,765 574,345 497,580 87

View Replies!   View Related
Convert The Data Shown In Table 1 To Table 2 Without Rearranging The Columns And Rows
Is 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

View Replies!   View Related
Remove All Text Left Of Space And The Space
I have two words of differing character lengths separated by a space.

How can I remove the first word... essentially, all the charcters to the left of the space AND the space itself?

View Replies!   View Related
Locate Text String After First Space And Before Last Space
I have been working on different formulas to return the text string between the first and last space and have been unsuccessful. Is this possible?

I have tried several combos or Left and Right, I have been able to get the values after the first space, and the values before the last space, but not between the spaces.

String: Y60
~C CULT NUCLEUS 3X2 SPRING WST BK XL

Desired results: D60
CULT NUCLEUS 3X2 SPRING WST BK

View Replies!   View Related
Space In The Data Of A Cell
I have a column of data that should be showing as currency but will not format that way because there is a space at the end of the cents. I tried doing a CtrL+H and replace the space with nothing but the space remains.

View Replies!   View Related
Data List With A Space
I have a list of date and any of the entries that have less than 6 digits have spaces after the last digit. How can I remove the spaces without having to delete each one manually?

View Replies!   View Related
COUNTA Not To Counting Space Bar Space
I have the following formula that works fine until someone uses the space bar to clear a cells contents

=COUNTA($D11:$AI11)

When the space bar is used to clear a cells contents the COUNTA statements includes the space in the count. How do I count the number of cells with content and exclude the space bar space in a cell?

View Replies!   View Related
"Pull Up" Columns To Clear Empty Space Below Titles
I have a spreadsheet as attached where there are titles in row 3, but I want to go along each row and pull up all the data below to the immediate row below the title. This is different in each column but you will see what I mean. I do want this to be VBA and the real spreadsheet has many columns so it needs to cycle along I guess until Row 3 is empty?

View Replies!   View Related
Insert A 'space' Between Data In A Cell...?
I'm trying to insert a 'space' after the second character/number in a cell.

I.e 123456 should become 12 3456

I have tried =LEFT(A1,2)&" " but this just deletes all data after the space. I've also tried =MID(A1,2)&" " but this makes excel very angry.....

View Replies!   View Related
Export Data To Text: Space After Commas
I would like to export some data from excel to a text file. An example of text in excel is as below. which each number is in a separate cell.
1
12
123
1234

I tried to exported it as csv format and open it with text editor. it become
1,,,
1,2,,
1,2,3,
1,2,3,4
,,,,
,,,,
,,,,

however I would like the text file looks like this with a space between each comma.
1
1, 2
1, 2, 3
1, 2, 3, 4

What the best way to do it?

View Replies!   View Related
Convert Row To Multiple Columns
I am work a worksheet that contain the data like that
A B
NUM1 Jene,Joly
NUM2 Jene,Selo,Diff
MUM3 Tino,Selo
………………
this worksheet has over 1,000 data.
i want get the result in sheet2 as below:
A B
NUM1 Jene
NUM1 Joly
NUM2 Jene
………………
i write the code
[PHP]Sub test()
Dim i As Integer
Dim arr() As String
Application. ScreenUpdating = False
On Error Resume Next
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arr = Split(Cells(i,2), ",")
For J = 0 To UBound(arr)
With Worksheets(2)
LASTROW = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Cells(LASTROW, 1) = Cells(i, 1)
.Cells(LASTROW, 2) = arr(J)
End With
Next J
Next i
Erase arr
Application.ScreenUpdating = True
End Sub
[/PHP]

the defult of this code is that it will take long time to get the result.so,i want to know how to make the code run fast.

View Replies!   View Related
Convert Table Into Three Columns
I am trying to convert a table into three columns so that I can use the data in a vlookup.

View Replies!   View Related
Find Difference Between Cells Data Are Separated By Comma And Space
Table A: I have data separated by coma & space in cells A1:A4

A: Data Table
*A110, 20, 30, 40, 50, 60
211, 21, 31, 41, 51, 70
316, 19, 37, 48, 59, 81
418, 23, 33, 42, 57, 78

Table B: I want to calculate difference between cells A1:A2, A2:A3 and so on…
And if it is possible as shown in cell B2:B4

B: Result Table
*AB110, 20, 30, 40, 50, 60

211, 21, 31, 41, 51, 70
+1, +1, +1, +1, +1, +1
316, 19, 37, 48, 59, 81
+5, -2, +6, +7, +9, +11
418, 23, 33, 42, 57, 78
+2, +4, -4, -6, -2, -3

Separating cells A1:A4 data in separate cells can do this.

But if it is possible solution can be done as shown by VBA will be greater.


View Replies!   View Related
TXT File- Convert Using Text To Columns
I have a .txt file which i need to convert using text to columns in excel, obviously this is simple, however my .txt file is 325000+ rows of data

Is there anyway I can Excel can cope with this amount of data, I know that my row limitation is 65536, can i spread the data across multiple sheet tabs?

View Replies!   View Related
Convert Columns To Rows With Formulas
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:

View Replies!   View Related
Text To Columns And Convert Them To Decimal
I got following Hex data in text format, I need change them to decimal,

0324 00BF 0000 0000 030C 00C6 0000 0000
0305 00C7 0000 0000 02FE 300F 040B 4753
4D4D 5352 4E4D 5343 4E31 0030 0F0A 0101

firstly I did txt to columns, then I found some data changed to scientific format which is not I wanted as I could not convert it using function HEX2DEC.

032400BF00000000030C00C600000000
030500C70000000002FE300F040B4753
4D4D53524E4D53434.00E+3100300F0A0101

if I set column data format to text when I doing Text to Columns operation then all data will be text, so I still can not convert them to decimal.

View Replies!   View Related
Convert Address List To Multiple Columns
I'm looking for a way to convert an old church address list that is formatted in a single column. There are no blank rows, but the amount of rows for each entry can vary. For instance, there could be as little as 3 rows of data (church name, address, city/state/zip) or more if email addresses and websites are provided.

The data is currently in the format below (notice how one entry has a website while the other does not).

First Church
102 Main Street
Dallas, TX 12345
email@whatever.com
Second Church
500 Second Street
Austin, TX 12376
email2@whatever.com
http://www.boguswebsite.com

So I'm looking for the data to be formatted like the following:

First Church 102 Main Street Dallas, TX 12345 email@whatever.com
Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com

I was able to find the following code from a Google search, but it can't dynamically adjust the range.

Sub x()
Dim rng As Range

Set rng = Range("A1").Resize(5)
Do Until IsEmpty(rng.Cells(1, 1))
rng.Copy
Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
Set rng = rng.Offset(5)
Loop

End Sub

I think what I need to make this code work is a way to dynamically adjust the range so that it can determine when to move to the next row of data. Static ranges break the process due to the amount of data being provided not being uniform. What I was thinking is that I could use the word "church" as a start point and end point in a loop so the script knows when to jump to the next row and begin copying the proper number of columns. I'm just not sure how to accomplish this in vba.

View Replies!   View Related
Convert Single Column To Multiple Columns
How can I sort a single column of data to multiple columns of data. e.g. I want to convert ~1800 rows of data from 1 column to 3 or 4 columns.

View Replies!   View Related
Convert Text To Columns For All The Sheets In A Workbook
I have a workbook with 40 sheets, I want to convert column A using Text to Columns I have recored the method as below

View Replies!   View Related
Convert Multiple Columns To Single Rows
I 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.

View Replies!   View Related
Convert 2 Column List To Multiple Related Columns
I have a long list of 2 columns containing data as follows:

col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3

which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....

View Replies!   View Related
Convert Text File To Structured/Sorted Columns
I am very new to VBA and have found a task out of my programming abilities using VBA.

I need code that will take a text file that begins as this shown in textfile.jpg

and ends up like the following attached picture of the results spreadsheet group.jpg

View Replies!   View Related
Convert Multiple Rows To Columns And Add Column Headers
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.

View Replies!   View Related
Convert/Transpose Multiple Groups Of Rows Across 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

View Replies!   View Related
Convert Long Address List To Multiple Columns
I've been given a long list of mailing addresses in Excel in formatted as a single column, each address using three rows with no blank rows. Example:

John Doe
123 Maple St. #2
Cambridge, MA 02139
Jane Smith
321 Elm St. Apt 24
Austin, TX 34557
etc.
etc.

I need to get this data into the form ...

John Doe 123 Maple St. #2 Cambridge MA 02139
Jane Smith 321 Elem St. Apt. 24 Austin TX 34557

I would think this has been done, but I've searched the web and this forum without success.

View Replies!   View Related
Convert Column Of Mixed Data Into Unique Data
I need to convert a column of data which will have repeated values into a column that turns that data into data with all unique values. Below is an example.

Column1Column2
AppleApple_1
OrangeOrange_1
PearPear_1
AppleApple_2
PearPear_2
PearPear_3
AppleApple_3
OrangeOrange_2
OrangeOrange_3

I had a solution until I wanted to turn this range into a list. The solution was.

=If(ISBLANK(A2),"",A2&"_"& COUNTIF($A$2:A2,A2))

When this data is in the list the <A2> in the range stops incrimenting from row to row and simply turns into the last row of the list. I have an example spread sheet upon request.

View Replies!   View Related
Subtracting 2 Columns Of Data From Each Other When Labels In Separate Columns Match
I have four columns of data, as follows:

label 1, value 1, label 2, value 2

I need to create a formula in the fith column that for each line will tell excel to:

look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.

I have tried doing this with SUMIF but am getting nowhere fast....

View Replies!   View Related
Enter And Highlight Data In Columns Based On Other Columns
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column

"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.

OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved