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
Before
XYZ1
XYZ2
XYZ3
XYZ4
XYZ5
XYZ6
XYZ7
After
XXXXXXXYYYYYYYZZZZZZZ1234567
[Code] .....
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 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 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 am trying to transpose 1 column into 3 columns:
Example:
1
2
3
4
5
6
7
8
9
ect...
into
123
456
789
ect...
Here is the code I have:...................
This is a sample of the attached workbook.
Project
Task
Current Phase
Start Date
Planned Finish
Status
[Code]...
What I am trying to do is break up the "Phase" column into several columns, where each phase would have its own column. Something like this:
Phases
->
->
->
->
->
Project
Task
01.
02.
[Code]...
It is very important that the output of the data goes on a new sheet, and that the records maintain integrity.
I am looking for a macro that works like the ASAP Utility (Transpose data from one column to several columns in steps). To elaborate the work done by the macro it should transpose the values in a column to the number of steps that is user defined (Using InputBox) that is if there are 103 values in the column and the user enters the number of steps as 24 then the macro should transpose the data up to 24 columns and the rest in the next row up to 24 columns and so on unless the complete data is transposed.
For more clarity refer the attached excel sheet or the "Transpose data from one column to several columns in steps" utility of ASAP Utility.
I'm trying to create a macro that transposes data from columns to rows.
My source data is laid out so Column A and B contain item identifiers, and then the header for Column C to Column S contain dates (March, April, May, etc) and the row data below contains quantities for each month. This is on Sheet1.
I need my end data (on Sheet2) to have the item detail in Column A and B, the quantity in Column C and the Date in Column D. If there is a date that has no quantity then it should be skipped.
Sample Source Data (Pipes added for clarity, they aren't in the actual data)
Part |Description |June 4 |June 11 |June 18
A | PartA | 5 | | 12
(Please note June 11 has no quantity)
Sample Destination Data:
A | Part A | 5 | June 4
A | Part A | 12| June 18
Below is the start of the code, obviously I have a ways to go before it's fully functional but hopefully you get the idea how I'm trying to attempt this. Where I'm having issues right now is referencing columns by number. Is there a way to do this so I can do Column = Column + 1 to advance columns to the right? Or any better way to address this all together?
HTML Code:
Sub ConvertToRows()
Dim ReviewRow, ReviewRowEnd, PasteRow, ColumnNumber As Integer
ReviewRow = 2
[Code]....
I have 100 columns in 1 worksheet, each column has 200 rows, I want to combine all these columns into a single column.
Therefore, Column 101 or a new worksheet Column 1, will have 20000 names.
My starting point is a big data set that needed to be validated by several users. I split the data set into smaller sets that were sent out. All users have filled in the same column called "Input"
What I would like to be able to do is to paste their input into a single column on my original data set and keep the name of the input next to it. Is it possible to paste without overlapping prexisting data ? Something of the likes of F5 ("Select blank cells/text") ?
I've attached a simple file with dummy data to explain myself.
I am having data in two columns (A & B ). Now I want to get the data in the single column(column C) . Here column A(Name) & Column B(Mobile No). it is something like below.
Here is my input data:
I want to get output results like below in the column C
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 14 Replies View RelatedCartonNo1
ProductNo1
LotNo1
LotNo2
CartonNo2
ProductNo1
LotNo3
LotNo4
In the above (repeating for say, 50 or more cartons), is there a way with a macro(I know nothing about) or pivot table(see macro) to get the following format/result?
CartonNo1
ProductNo1
LotNo1
CartonNo1
ProductNo1
LotNo2
[code].....
How can you move data from multiple columns into a single column? I have attached a short example of the data I receive. (In reality, the data could be hundreds of columns and/or hundreds of rows, but this is representative of what I might receive). It does not need to be sorted and including the header row in its own column is optional. I do not know VBA all that well but can muddle through it if someone can give me an example of what the code should look like.
View 5 Replies View Relatedin moving data from multiple columns to a single column. I have attached a sample image from an excel file which details the requirement. The first column contains a qualifier, that should remain constant when the data from columns B to the end move to a single column "B". The number of columns for each row is different, however, there is a maximum number, say 25. As mentioned in the image, when the data from columns B to the end is moved in to column B, column A is retained fixed, to the original value, and the original data below it is pushed below. Any pointers to how this can be achieved by VBA or without VBA?
View 6 Replies View RelatedI have 300 rows worth of data that looks similar to this, all organized in one column:
John Q. Smith
Programmer
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
Organizer
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
Accountant
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555
As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
I saw this thread from June Cut & Paste Macro: Move Address From Column To Row and I think this what I would like to have my macro do. I have one column which is copied and pasted as text in excel and there is several blank rows.
I attached the spreadsheet example - it has 40 lenders, with the top row being the lender name (A1), followed by address(A2), city-state (A3), two blank rows(A4-A5), lender type (A6), approved date (A7), one blank row (A8), lender specialty (A9), telephone (A10) , e-mail address(A11) and two blanks rows (A12-A13) and A14 starts over again. For this example there should be 8 columns and 40 rows (lender name, address, etc...). As I mentioned I think the previous thread's macro would work with some minor tweaks. I couldn't figure out the best way to handle the blank rows and or remove the blanks and what to add or take out of the macro code.
I have a set of data that I need to change the "layout" of. I've had similar situations before, but this one is just killing me. Basically, my data is for item pricing. It is represented as
Item, QTY, Price
A,1,1.25
A,10,1.1
A,25,1
A,100,0.9
B,1,1.25
B,10,1.1
B,25,1
B,100,0.9
C,1,1.25
C,10,1.1
C,25,1
C,100,0.9
Item D,Qty1,P1
Item D,Q2,P2
Item D,Q3,P3
Item D,Q4,P4
However, I need it in the following format:
A,1,10,25,100,1.25,1.1,1,0.9
B,1,10,25,100,1.25,1.1,1,0.9
C,1,10,25,100,1.25,1.1,1,0.9
Item DQ1Q2Q3Q4P1P2P3P4
As a note: there is a maximum of 4 Price/QTY breaks, so the script can be hard coded for that. When I tried this, I had it looking at the Item column, finding out how many breaks there are for a specific item and then doing a loop to extract the qty and price to a single row in the format shown above. It worked for the first 2 items, but then the loop got throw off. I will see if I can reproduce the code for that.
I am trying to reformat the attached doc into one single column, It's a race listing of overseas marathons and I need it to look like this
Event Name
Event Date
City
Country
Entry fee
Website
in date order, one event after the other in a single column so I can flow it into a word doc ready to be designed. I need to apply in Excel (I am using 2007) to get this doc into the required format?!!
I'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.
Here's the history...
I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.
Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?
I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.
Data Looks like:
54654
31233
42343
51234
66968
43252
54657
63253
[code]....
Would like to look like:
54654
66968
79282
91596
68185
31233
[code]....
Currently we are transposing data in multiple cells from horizontal to vertical & vice versa.
But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.
Is there any VBA function or public function to perform the this action?
Example:
From
A 1Dog; Lion; Parrot; Bee; Snail
To
A 7Dog8Lion9Parrot10Bee
11Snail
Like wise i will have to do the same action for the following
A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7
How a single-cell formula to check that 2 transpose arrays are equal.
For example, A1:A5 are {1,2,3,4,5}
AND
B3:B8 are {1,2,3,4,5}
Is there an array formula in C3 for example, that will check (i.e. say TRUE) if corresponding ranges are true i.e. check in this cell that A1=B3, A2=B4,...A5=B8.
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.
I need to combine around 20 columns into one single column, then I also need to insert open and close html tag for each column moved. See attachment example or information below
BULLET 1, BULLET 2, BULLET 3, BULLET 4, BULLET 5 ====> DESCRIPTION:
DESCRIPTION COLUMN
<ul>
<li>BULLET 1</li>
<li>BULLET 2</li>
<li>BULLET 3</li>
<li>BULLET 4</li>
<li>BULLET 5</li>
</ul>
I am a macro newbie and I think this is beyond me.
I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.
So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.
There must be a way to do this that is easier than searching the internet all afternoon again
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have a worksheet where I would like to transpose the 3 columns into 1 row.
I would to change
ID
NUMBER
DATE
[Code]....
into
950 9.8 01/01/1992 950 6.34 01/01/2002 950 5.43 01/06/2002 950 6.76 01/09/2002 950 7.44 01/01/2003 etc...
This worksheet has 5413 rows with different ID's and it is attached : Columns to row.xlsx