Combining Cells In One Column Based On Another Column
Jul 18, 2014
I currently have a spreadsheet that has 4 columns. I want to review the information in column A and if it matches, I want to combine the cells in column D into one cell for the matches in column A. Here is an example of the information I have:
123456
ABC Company
901
0001
123456
ABC Company
901
0099
[Code] ....
So basically I want to review column A and any # in that column matches, I want to combine the cells from column D into one cell. So I would want the first sequence to catch that A1, A2, and A3 match... so then it would combine D1, D2, and D3 into one cell that would read "0001/0099/HRA1". Is this possible?
View 9 Replies
ADVERTISEMENT
Apr 5, 2013
I have a problem which requires a vba routine that is currently outside of my level of expertise...
Here is an example of the data:
TESCO GARAGE 3729
59
REF 177 1092098207 BCC
59
CO-OP GROUP LTD
60
[Code]......
View 3 Replies
View Related
Jan 8, 2008
in column A i have account name but it can be up to 4 lines (rows)
in column B i have the fund name which goes with the first account name row
so...
View 9 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
Mar 10, 2014
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies
View Related
Apr 24, 2009
I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
View 2 Replies
View Related
May 1, 2014
I am working on a spreadsheet to track membership data. In Col A, I have the year that the member started their membership. In Col B, I have the dd/mm/yy that their membership renews.
For instance:
ABC Company began membership in 2002 (Col A). The next billing cycle (membership renewal) is scheduled for 7/1/2014 (Col B).
Is there a formula to take the month and day from Col B and the year from Col A and combine them? For the example above, I want the result to read: 7/1/2002
View 2 Replies
View Related
Feb 27, 2012
I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.
A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX
I had thought an IF formula would be the way to go.
View 6 Replies
View Related
Aug 17, 2006
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept()
Dim Dept As Range
With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept
I keep getting error 92 - "For loop not initialized".
View 8 Replies
View Related
Dec 6, 2013
I am trying to sum up cells from a column based on conditions in another column (see attached file). I know this could be done using a formula but for various reasons I want to use code. I am not sure if what I am doing is the right approach.
View 1 Replies
View Related
Jun 10, 2008
I would like to write a macro to add cells in columns C,D,E,F,G & H based on the text (employee) entered in column B. For example I need it to add all the cells for "John S" found within column "C" and then total it at the bottom with a code JSSS (John S straight shop time) or JSOS (John S overtime shop) as per my example. The attatchment is a spreadsheet I use to total billable hours for our employees.
View 3 Replies
View Related
Dec 12, 2007
see the picture below to get an idea of what i want to do.
http://aycu15.webshots.com/image/362...0389296_rs.jpg
View 9 Replies
View Related
Jan 14, 2014
Say I have a column of numbers. I'd like to be able to string them together in the following format: number1|number2|number3....
connect.xlsx
View 3 Replies
View Related
Apr 24, 2007
I need help with a formula so that I can create a single chart or (more likely) multiple charts for each of the choices in column C.
Attached is a sample of my spreadsheet.
I want to be able to break out each of the "Br#"(A) associated with the "BUM"(C) and graph each of the choices in column D (Run Time).
I am sure I can create the chart I need once I get the formula created to pull the data I am trying to combine. I hope that I have explained this well enough.
View 9 Replies
View Related
Apr 10, 2014
I currently have a worksheet filled with data from other worksheets. I want to combine data from different columns when they share an identical row header and don't know how. For example:
1b--
1--c
1-d-
2-x-
2y--
2--z
Would go to:
1bdc
2yxz
Another specific issue is that some row headers are bold and need to be considered a separate header from the non-bold alternative.
View 4 Replies
View Related
Jan 11, 2009
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.
A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,
What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View 2 Replies
View Related
Aug 5, 2009
I have a book with 250 worksheets, with one cell of data per sheet. It is the same cell in every sheet (C7). I need to somehow aggregate all of this into one column on the first sheet, 250 rows.
View 5 Replies
View Related
Mar 20, 2009
im currently working on a project that needs to consolidate the data per given date. but the thing is, all of them are located into one column. i was able to separate each category into separate columns but now, i cannot align the data on a per date basis.
View 10 Replies
View Related
Dec 2, 2008
what i want to do is 1 have a workbook with 3 worksheets. Sheet1 contains the columns:
Reference No. Line Item No. Amount Date
1 3 500 12/01
2 4 900 01/01
Sheet2:
Reference No. Line Item No. Quantity PersonInCharge
1 3 10 Kim
5 6 60 Noel
For sheet 3, what i want to happen is that when i run the VBA macro, it searches through both worksheets (Sheet1 and Sheet2), finding rows that have matching Reference No. AND Line Item No. and combine all of the columns (and values) for that particular row into another row in Sheet 3. So for the above data set, the resulting data in Sheet 3 would be:
Reference No. Line Item No. Amount Date Quanity PersonInCharge
1 3 500 12/01 10 Kim
As this is the only entry in both sheet1 and sheet2 where there is a matching reference and line item no.
View 9 Replies
View Related
Apr 14, 2014
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies
View Related
Mar 2, 2012
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
View 5 Replies
View Related
Jun 27, 2012
Is there a function in Excel that allows you to combine non-numeric columns as a list?
For example, 91401 is a zip shared by three cities: Van Nuys, Sherman Oaks and Valley Glen.
The data is in a workbook as three rows (each with 91401 as the first column, and cities listed individually:
91401 Van Nuys
91401 Sherman Oaks
91401 Valley Glen
I am eventually wanting to do a v-lookup with the zip, but the duplication is causing problems. I want the associated cities listed, but only want one row. The cities could be listed, separated by commas (preferably), like below:
91401 Van Nuys, Sherman Oaks, Valley Glen
The list of zips I have is several thousand long, so it's not practical for me to do it manually. Is there a "pivot table" of text that I could use?
View 5 Replies
View Related
Apr 30, 2014
I am really struggling to summarise weekly budget data into period budget data by subcategory.
I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.
View 4 Replies
View Related
Jan 7, 2014
I have data written in Cells B5, C5, D5 etc that changes as you add more column data
I am trying to figure out how to merge cells starting from Cell B3 based on used cells in row B5 to end of column.
View 6 Replies
View Related
Feb 15, 2008
I have a spreadsheet that has columns stating both status and then further to the right in the row, currency totals. There are only two status options, Stocked and On Order. I need to add the total currency amounts based on this other column's listing and it must change if status changes.
(Put simpler:
Column A lists Stocked or On Order and Column B is the currency tied up in that row.
I need to make totals for the cells in column B based on the status listed in Column A and it must change when Column A is changed. This should result in two totals, one for Stock Status Currency and one for On Order Curreny)
View 5 Replies
View Related
May 14, 2014
I want to write a formula/script that searches file names in Column "A" for an item number located in Column "B", then combines all file names containing that item number into Column "C" with comma separations. Each item number will have a different number of file names associated with it (between 1 and 10 files), and there are about 2000 different item numbers being crossed with about 7000 file names.
Here is an example completed with a CONCATENATE formula:
File Name
59481A_1.jpg
59481A_2.jpg
59481A_3.jpg
59481A_4.jpg
59481A_5.jpg
59481A_6.jpg
Q110XL_1.jpg
Q110XL_2.jpg
C5710_1.jpg
C5710_2.jpg
C5710_3.jpg
C5710_4.jpg
Item Number
59481A
Q110XL
C5710
Final Image List
59481A_1.jpg,59481A_2.jpg,59481A_3.jpg,59481A_4.jpg
Q110XL_1.jpg,Q110XL_2.jpg
C5710_1.jpg,C5710_2.jpg,C5710_3.jpg,C5710_4.jpg
View 4 Replies
View Related
Nov 7, 2013
What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?
Also i need this to work on all atbs as the commandbutton will on a master tab
VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False
[Code]....
View 4 Replies
View Related
Aug 17, 2014
Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A
It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.
View 2 Replies
View Related
Mar 10, 2014
count cells row wise that meets criteria both in column & Row as well.
View 2 Replies
View Related
Mar 19, 2014
Now I know that merging cells is usually not a good thing in VBA, but I'm working on a data report that just looks bad unless longer pieces of text can span several cells to minimize column width.
So here's the problem:
I have a column of 1's and 0's in columns E and F, and text in Column G. I want to merge the cells in columns G and H for that specific row if there is a 1 in either the E or F column for that specific row. And I need to do this for a long range...rows 7-5000. Any ways that VBA can do this?
View 4 Replies
View Related
