I have a file that I need to do a text to column separated by comma and then transpose results to a single column. See exaple below
File
AreaZone
0886518
1801315
1801413
1801524, 25
1804214, 16, 18
Results:
AreaZone
0886518
1801315
1801413
1801524
1801525
1804214
1804216
1804218
I'm trying to do something which I can't manage with traditional formulas and a macro might be required.
I have the following table:
Code:
Header1Header2Header3Header4Header5
1.00 6.00 11.0016.0021.00
2.00 7.00 12.0017.0022.00
3.00 8.00 13.0018.0023.00
4.00 9.00 14.0019.0024.00
5.00 10.0015.0020.0025.00
What I would need to do is take all column values and transpose is to rows, copying the header for every set, like:
ColumnA ColumnB
Header1 1.00
Header2 6.00
Header3 11.00
Header4 16.00
Header5 21.00
[Code] ...
What macro would convert this:
Excel 2007AB112234Sheet2
To this:
Excel 2007A51627384Sheet2
I need a macro please that works with a table of any size.
I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached)
Raw data in columns A,B,C,D,E as below
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
Converted should be
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2
I want to add a Punctation mark (comma), like this: ,
and also want to add punctation mark (colon), like this: :
In this moment I have below macro:
Public Sub CombineCells
Dim Combined As String
Combined = ""
For Each Cell In Selection
Combined = Combined & Cell.Value & ":"
Next Cell
Selection.Cells(1, 4).Value = Combined
End Sub
the effect shoud be like this:
before:
--A
1-C
2-D
3-E
4-F
Etc.
after transposed:
--D
1-C:D,E:F Etc.
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.
This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)
I simply have an entire column with random things in it that I would like to add "www." to the beginning and ".com" to the end. I am very new to excel and I am sure there is an easier way than going through it manually and entering it.
View 6 Replies View RelatedI am trying to get a VBA code to import multiple text files into an excel worksheet. The text files contain about 5,000 words each listed in a single column. I found this thread Import Multiple Text Files and the code listed there:
Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "c: est" '<- change to actual folder path
delim = vbTab '<- delimiter (assuming Tab delimited)
fn = Dir(myDir & "*.txt")
Do While fn <> ""
Redim b(1 To Rows.Count, 1 To 1)
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)..........................
I am trying to convert text to rows but in the same column Take the following example:
Column A:
1-2-3
ab-cde-fg
54-ty-12345
the
waht i am looking is convert it to the following format: (the - is the delimiter)
Column A
1
2
3
ab
cde
fg
54
ty
12345
i have found the following VBA code on those forums but i don't know how to edit it to suit my needs
Sub Txt_To_Rows()
Dim arrText() As String
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Dim j As Integer
Dim x As Integer
Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
j = 2...........................
From a table like the one here, how can I generate a list (without spaces) of all the names only? Perhaps filtering by "Mr","Miss","Dr" or something? The result I'm after would be a column on a new sheet that ran:
Mr James
Mrs Milly
Dr McAllister
Miss Aujard
Mr Barker
Mrs Stanley
I need to split in Excel some thousands of data rows containing text and numbers information of different character length Data to be extracted ( parsed) is stored in a single column( the first one) and needed results look like this:
Data to be extracted
Needed results
Address
Name,Surname
Phone no.
Green Street no 16 Smart town Schmit Anders +3900098787867
[Code] .......
Data is composed by addresses, name of persons and phone numbers. Person's name is has two or three parts, address and phone numbers length is different ( 9 to 12 characters) I need to complete this difficult task (for me, obviously, ) in Excel using a combination of functions and/or multiple operations.
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
Is there any method to speed up a for each loop to count text in a cell with multiple criterias in a single column. This is on example:
Code:
For each rr in r
If rr = "a" And rr.Font.Strikethrough = False Or rr = "B" _
And rr.Font.Strikethrough = False Then
a = a + 1
end if
next
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies View RelatedI'm trying to come up with a single formula to create a single column list from a table with blanks.
a
b
c
d
e
f
g
To
a
b
c
d
e
f
g
I know I've done this before but having trouble visualizing today.
I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.
The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.
This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).
For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units.
In points, the difference is 420 vs. 386.25, or 33.75 points.
Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.
Then (10-1) margins allowances time 3.75 points resolves the difference.
Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design.
For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10).
Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.
I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
I am creating a tool that is populating information off of another excel document and presenting the information in a different format then the data originally appears. Basically I am unsure how to create a formula, for example, in cell A1, that as I drag it down (A2, A3, etc) the column letter in the formula advances but the row number remains the same. In another words as the formula moves into cell A2, the "Sheet1!D3" changes to "Sheet1!E3", where as normally it would advance to "Sheet1!D4". I just started back up in excel, im sure this is way easier then I am making it seem but I have been unable to come up with a solution.
View 4 Replies View RelatedI have a file which is divided into 2011, 2012, 2013 and 2014 years in column A. I want these data in different column according to year. For example, Column E(2011), Column F(2012), Column G(2013) and Column H(2014).
Attaching sample a file with sample data to be converted.
It is also to be in consideration that data in column B and C should not be repeated and all year data should come in front of it.
I want this by coding or formula only. I dont want to use pivot table for this.
Expecting Result.jpg
Macro for a repetitive task. I have data in every 3rd column (max of 11 columns) that I want to extract and append the data in column A in sheet 2. The data contains some blank cells and I'd like to remove them also.
I've attached an example in this workbook : tractdata.xlsx
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
View 8 Replies View RelatedI have a contiguous multi column, multi row numeric range that I want to copy (and sort in ascending order) into a single column on another sheet in the same workbook. What VBA code could achieve this, or alternatively can this be achieve via formulas?
View 3 Replies View RelatedI have a sheet with a layout similar to the following:
Network Location | Visits
Company A | (empty cell)
% Change | 8%
Company B | (empty cell)
% Change | 5%
Is there a simple way (I'm using Excel 2007) to make it appear like so:
Network Location | % Change
Company A | 8%
Company B | 5%
Is there a way to transpose or swap a column or row of data. e.g. A column of numbers going from 1 - 10, swap them around so it goes 10 - 1 in the same place?
View 7 Replies View RelatedI need to transpose column data (Sheet called "Recpt") into rows (sheet called "Formula")
Please refer to attached excel file,sheet "Formula". I have manually entered formula for 12/1/2013. Need to add formula for the rest of the sheet. Since the data is on every 4th column, I am sure it is feasible to copy the formula by adding 4th columns.
i am looking to convert data in columns to rows without using transpose but by formula. and i can drag it across to the right to keep getting new data.
i see posts mention about using functions like index, match? or other functions to do it?
i have attached the excel.
I've got a workbook called MyBook1 which has 12 groups of 5 columns representing each calendar month. I am looking to create a macro (one for each month) which will be executed from another workbook. This macro will pull a column of data from a workbook called Data1 and paste into a row in the MyBook1 workbook.
Using the macro recorder, I've got the following code that works great but was trying to shorten the code and get it to work from another workbook but haven't been successful. I believe all that needs to be done is create a variable for the source/destination workbooks then set the selections and do the copy/paste but I'm having a hard time finding example macros which I can learn from.
Working code below for January, for Febuary the Data1 range is D2:D6, and the destination is 5 columns over making it M3. Then just follow the code below as a template.
[Code].....
I have code below that I need to take a copied range and Transpose the range from a column to a row.
Code:
Sub Search_Notes_Main()
Application.ScreenUpdating = False
Sheets("Analyst Main Template").Select
[Code].....
The following data is in column A:
Adam
Mobile 19171234854
Work 19171234854
Adele
Mobile 9171234854
Home 2121234567
Adrian Simpson
Mobile 19171234854
I would like to move the data to rows with the appropriate headers:
NameMobileWorkHome
Adam1917123485419171234854
Adele9171234854 2121234567
Adrian Simpson19171234854
As you can see, some of the phone numbers start with 1 and some don't. The listings may or may not include mobile, work, and home phones.