Combine Multiple Rows To One Row With Blanks In Some Reference Columns
Jun 4, 2014
combining 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.
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:
Sub CopyToA() Do While ActiveCell <> "" Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0) ActiveCell.Offset(0, 1).Select Loop End Sub
However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?
[Code] .....
For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?
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 am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
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.
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?
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
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 a 8500 row sheet with 40 series of data given for each "data set". This data is situated in four columns and ten rows, so that rows 1-10 contain one data set, rows 11-20 contain another data set, etc...
Does anyone have a simple VBE script that can grab this data and format it so that my data sets are arranged as 850 rows and 40 columns instead of the native 8500 rows and 4 columns?
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.
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 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.
so basically, here's an example of what I have. The spaces in between represent different cells:
Name Corporation Type Group Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3
What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3
In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.
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.
I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.
VB: Sub Patient_Detail() Dim n As Integer 'index of rows to record to Dim i As Integer 'index of column to record from
I have a table with multiple rows for some employees and single rows for others. Each employee has a unique identifier.
Employee IDT1T2T3T4123Net salesmarket assetsmarginsspeed to market123Net salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time delivery180unit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
Is there a way in which I can restructure the data so that the multiple rows for any single employee are added into subsequent columns in one single row? e.g.
Employee IDT1T2T3T4T1bT2bT3bT4b123Net salesmarket assetsmarginsspeed to marketNet salesturnover per quarterProfitOn time delivery112Net salesmarket assetsmarginsspeed to market180No. of ordersturnover per quarterProfitOn time deliveryunit salesturnover per quartermarginsspeed to market98No. of ordersmarket assetsmarginsturnover per quarter
The table has approximately 15000 rows & c. 30 columns in the original format. I can use basic excel, but I've never dipped into macros or anything very complicated...
I have a list of companies and employees, if a company has more than 1 employee listed, I need to combine all instances of the employees into 1 row per company separated by a comma.
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1 Row 1: Mike │ "Great to work with" Row 2: Mike │ "Bad manager" Row 3: Tom │ "Great guy" Row 4: Mike │ (blank) Row 5: John │ "Cool" Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet] - Great to work with - Bad manager - Best boss
I have two workbooks open, Workbook 1 (W1) & Workbook 2 (W2)
W1 has the following data entered in about one of our clients:
A1= Client Company Name A2= Client Name A3= Client Address A4= Client Phone Number
What I would like to do is the following:
In W2, when I enter (in any cell, but let's say C3 for example) that C3 = A1 (from W1), that I can automatically have the following cells D3, E3, & F3 filled in with the same information appearing in cells A2, A3, & A4 from W1 as well.
I believe this must be possible-- I just don't know the right formula & steps to take, to make this happen.
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
I want to be able to reference cells in a table by using two drop down lists. One drop down for the columns in the table and another drop down for the rows in the table. The values in the columns will be different metal materials (aluminum, copper, stainless steel, etc.). The values in the rows will be thicknesses of those materials (1/16", 1/8", 1/4",3/16",3/8",1/2",3/4",1"). I already created a drop down list for the materials and another for the thicknesses. But now I want to be able to select a particular material and then a particular thickness and receive back in another cell a value that's cross referenced in a table. Specifically, the values in the table will be the weight of the particular material and thickness per square foot. I'll populate all of the cells in the reference table manually. I'll hide the table on a separate worksheet. i haven't created the table yet.
I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.
