I am trying to combine data from multiple columns into one column. The way my data is set up there shouldn't be any columns that have data in the same row. See below for my example.
[IMG][/IMG]
sample1.xlsx
Sample file attached. How do I combine the data in columns B-E into column A? Obviously in my sample file you'd use a simple cut/paste but my actual file has hundreds of columns and rows so cut/paste isn't an option. I need to move all the data into column A.
I'm trying to combine multiple columns into 1 column in Excel. I've searched a few places but most of them are tailored for a specific sheet and my VBA skills are elementary
My table looks like this (there could be more than 5 columns... it's somewhat dynamic based on the data given). Note that some columns could have no data, and the size may be different, some have 100 rows of data, while others have 5.
Firm A
Firm B
Firm C
Firm D
Firm E
[Code]...
I want to combine these columns into
Consolidated
A
B
B
C
D
E
[Code]...
Is it possible to do this with an Excel formula? If not, a VBA code
My workbook contains information about investigations. Each investigation gets a number and a line in the workbook. Many infos are stored: dates, subject, ...
A dynamic table contains all those informations, which allows me to manipulate data pretty easily.
Issue: For one of the characteristics of the investigation (let's say names), several results might have to be entered. For example, one investigation can have 3 different names, the next one only 2 and another one can have 6.
This causes a problem regarding the manipulation of the data.
Right now I have 10 columns "name 1" "name 2" .... and only those who need to be filled are filled. I can't however efficiently manipulate the data. For example, George could have been entered in "name 1" for the investigation 1 and in "name 2" for another investigation. Using a pivottable, to track George I'd have to select him in all 10 columns.
It seems to me the best choice would be to somehow index the names into a single column with each name linked to its investigation number..
With the index function I have 2 issues:
- It starts by going down the 1st column, then goes to 2nd, ... where I'd want all names of 1 row then names of 2nd row.
Solved this with this formula:
I turned my "names" columns in a ranged name "Names"
but - When fields are empty I get 0s. When I try to remove them with an if statement, it'll just give me empty cells. I'd prefer to skip the empty cells.
Also, I don't know how to 'link' them to my investigation number...
I have this (dynamic) table:
investigation number name 1 name 2 name 3
001 George Paul
002 Chris George Suzy
And I want something like this:
Investigation number Names
001 George
001 Paul
002 Chris
002 George
002 Suzy
I have attached a spreadsheet. I have a data set with ID's in column A and data in columns to the right. If an ID has only one entry then there will only be a value in column B if an ID has 2 entries then there will be a value in Columns B and C, if they have 3.......... And so on....
In my attached file I have created a macro that filters each column and copies the values to another sheet. If an ID had 5 entries (B-F) I would want to have 5 rows for that ID. The attached example macro does what I want but the real data has over 50 columns. Is there a more efficient way of doing this?
Context: My workbook contains information about investigations. Each investigation gets a number and a line in the workbook. Many infos are stored: dates, subject, ...
A dynamic table contains all those informations, which allows me to manipulate data pretty easily.
Issue: For one of the characteristics of the investigation (let's say names), several results might have to be entered. For example, one investigation can have 3 different names, the next one only 2 and another one can have 6.
This causes a problem regarding the manipulation of the data.
Right now I have 10 columns "name 1" "name 2" .... and only those who need to be filled are filled. I can't however efficiently manipulate the data. For example, George could have been entered in "name 1" for the investigation 1 and in "name 2" for another investigation. Using a pivottable, to track George I'd have to select him in all 10 columns.
I could maybe
- Write all names in 1 cell, separated by something and use somemorething to virtually split that
- Write each name in a row below, with the other characteristics copy-pasted (although then if I have 2 names and thus 2 rows, Excel will consider I have 2 different investigations).
- ???
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
I have a excelsheet that looks like this:
Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept
Health Services
Internal Services
Public Works
Social Services
Los Angeles
3
12
New York
8
22
100
7
Chicago
15
56
4
I have a spreadsheet that has identical data in column A throughout the worksheet and want to take data in columns F and G and combine them all onto one row. An example of the data is attached.
test example.xlsx
I am trying to combine the data of 31 columns into one column on a separate worksheet. The number of pieces of data in each column is different and as a result I am hung up on how to code the macro so that it automatically moves to copying the next column when a blank cell is reached, as it currently copies until it reaches the 100th row and then moves to the next column even though they're blank.
Sub CopyColumns()
Dim improw As Long, impcolumn As Long, MyCell
Dim ws1 As Worksheet, ws2 As Worksheet
Dim improw2 As Long, impcolumn2 As Long
Application. ScreenUpdating = False
Set ws1 = ThisWorkbook.Sheets("Data")
Set ws2 = ThisWorkbook.Sheets("Details")
'paste location on ws2
improw2 = 2
impcolumn2 = 2
There are column headers for the data on ws1 in row 2. The other thing I would like to do if possible is copy these column headers from ws1 onto ws2 by pasting them to the left of each piece of data (impcolumn2 - 1).
I have 2 columns "H" and "I" from line 6 to 409. Each cell may start with AR , MA or a number. If cell H6 starts with AR I would like to put that cell into B6. If H6 does not have AR then I would like to check I6 for AR, and put that into B6 if it's found. If I don't find AR in either I would check them both for MA, and write the cell that finds MA into B6. If the AR or MA are not found then I would want it left blank. I did try
IF(FIND("AR",$H7,1),$H7,IF(FIND("AR",$I7,1),$I7,IF(FIND("MA",$H7,1),$H7,IF(FIND("MA",$I7,1),$I7," ")))) in B7.
i need to take all cells in column A with values and all cells in column B with values and combine them into column C so i can sort and subtotal. Columns A and B may have 2 values or 2000 values.
View 10 Replies View RelatedI 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.
I need two solutions, if possible. One macro and then a "poor man's way" in case the macro breaks down the road. something where I can do it with copy/paste, formulas etc.
I will just use letters and numbers to make it easer.........
Column A might have 10 rows, with 1,2,3,4,5,6,7,8,9, and 10 as the data.
Column B might have 7 rows with A,B,C,D,E,F and G as the data.
I need column C to have 70 rows, with 1A, 1B, 1C, etc. .......through 9E, 9F through 9G as the data.
I have three spreadsheets. They all contain different data but each one has a 'Rental Equip ID' column. Is there a way to take these three spreadsheets and consolidate in to one spreadsheet with all the data on one sheet? I have included an example of each sheet. The column headers are in bold. I can send an example file examples if needed.
Rental Dscr
Rental Equip ID
Rental Group
Rental Sub Group
Rental Qty
Rental Cost
BALL JOINT ADAPTERS 4WD
004-0302
13
2834
1
$ 50.22
[Code] ......
I wanted to see if it were possible to combine multiple worksheets into one new worksheet, but without duplicating columns with the same heading, enabling the data to fall into the correct column if it's shared in multiple worksheets, and adding columns if their unique. Furthermore, if there are a few "leading" columns, i.e. ones that are shared in every sheet and never move from where they are, to include those as the leading columns in the new sheet. I'm unable to post an attachment, but I'll try to paint a picture. This is on a much smaller scale than intended, but I want to combine Sheet 1 and Sheet 2 to form Sheet 3 (which I've manually copied to look how I would want it to look if the formula/code works properly). Columns A, B, and C are the "leading" columns I was referencing, whereas any of the following columns may or may not be shared in the various worksheets, but should still be included and combined if they're the same. There also might be a different number of columns depending on the sheet. I consider myself somewhat of an intermediate user, and I am familiar with using VBA codes for formulas should that be the proper remedy.
View 2 Replies View Relatedcombining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.
Desired output:
Item
Quantity
[Code]....
Columns A, B, C and D contan a list of Names, I want to be able to in column E list all the items in A,B,C and D with out duplicates. How would I do this?
View 1 Replies View RelatedHow can i merge all the columns from Column D to Column Y in Column Z. The seperator between the Columncontents should be numbers in format "<<1>>" for column D, "<<2>>" for column E,"<<2>>" for column F........
i have a wordlist(arabic from right to left lanuage) containing words and meanings. Column A contains Words, Colulmn B contains Transcription, Column C contains Grammer information and the following Columns up to Column Y contain of Meanings.
!!!The meanings start with Column D which is then followed by E,F,G... Y. If any column cell is empty then it would mean, that the following cells of corresponding columns are also empty. For example if Colulmn D has content but Column E not, then it means that F,G,H...Y are also empty!
I have single values in each cell in column A. I have 100 rows.
I want to take six rows at a time and get them to populate columns B, C, D, E, F, G.
Is there a simple formula for this?
I have an address in one cell in excel. Street/City/State/Zip are all separated by two spaces like this: 123 Anywhere St Chicago IL 60607 United States
I want to break them out into separate columns. The "Text to Columns" won't work because there are no commas or dashes separating the data.
I have a list of data that is currently separated into 5 different columns with a heading. Each item in the list is given a score 1, 2, 3, 4 or N, which is then placed into the appropriate column for that score.
What I want to do is to place the data in another sheet in the same workbook in a single column list separated by the labels from the columns in the first sheet, without any blank rows.
I am sure it would be easier if I could show you the spreadsheet and the result I want to achieve but I cannot see here how I can. I tried to copy and paste, but it only pasted as text.
I found this treat but it didn't help me as i hoped. Move Data From One Column To Multiple Columns. I try to convert multiple datablocks to a new worksheet and make the adress blocks ready for further sorting and removing actions. The Excel I use is Dutch and i hope that i make my question clear. What I want:
copy each adressblock to another worksheet, put the initals as well as the beginning in a seperate column and delete the unnecessary comma's. Each adressblock is identified by a number in colom A. When a new number is shown, excel has to start a new line to fill. finally,if possible i like to extract the adress line into seperate colums of "street+numer", zip and place. Some adresblock have more than onder adres, if possible i want excel to ad these information in the 'extra' colums.
I am trying to move information that is pulled from a database to look cleaner and easier to read. All of the information exports out into column A only. The attachment is a brief example of what it looks like, and what I’m trying to get it to look like.
I want to move the data in col A to 5 columns C through G in groups of 16 rows. Once the last column is filled with a group of 16, it needs to fill below the first group of 16 in column B onward to column F. It should repeat this process until there is no more data in A. The attachment shows it much better than i can explain it.
I have some basic experience with formulas and macros, but I haven’t had any luck trying to manipulate any of them to give me the information the way I have shown. I would prefer to not have to manually type or cut and past the information in if possible.
I am trying to do something that would look at column B and for each value that is the same in any row in column B, it would look to column A and add that value into a text string of all those values in column C separated by commas.
example, If A1=37294 A2=37295 A3 = 37296 A4=37297 A5 = 37298 and B1 = abc B2=ABC B3=ABC B4=XYZ and B5=XYZ I want C1 to result in 37294,37295,37296. And I want C2 to result in 37294,37295,37296 and C3 to result in 37294,37295,37296 and C4 to result in 37297,37298 and C5 to result in 37297,37298.
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>
in 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 been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.
EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.
DATA
Account Code
44211
44215
[Code]....
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'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?
