Merging Columns / Deleting Rows And Summing Totals
Mar 22, 2012
My data in excel looks like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5
What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7
I'm still having issues with this workbook. I cannot use a pivot table to fix it, I don't understand them and it confuses me greatly..... so I'm at the mercy of either a formula or macro. I need to combine the duplicate part numbers (a), total the quantities, average the price (d), and total the amount of the part (e). I'm having a very difficult time with it.
(I have a sample attached that is file sample 2, and the entire spreadsheet attached- sample 3)
I have a sheet whereby in column A i have company names and column B how much is invested in this company so:
A B AAPL 10 GOOG 5 MSFT 15 APPL 5 IBM 20 GOOG 10
What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.
I've been trying unsuccessfully to find a way to merge data into a matrix, but can't quite figure out a way to manage it. I have a list of employees in column A, in the list there are employees, many with multiple instances, and in columns C onwards there are dates in dd/mm/yyyy format.
Each line represents the number of nights in a trip, from 1 to 35 nights and the start to finish dates. Where as some employees may only have one row with one date others may have 20 rows with between 2 to 35 dates.
I'd like to merge this into a matrix with one line for each employee showing which nights in the year they were away (tab matrix) which has a day of the year to illustrate which employees were away on which nights of the year.
I've uploaded an example to highlight what I mean.
I've tried a pivot and an index match formula but couldn't get either to work.
What I am trying to do is have the formula look at the order statuses in column E and total the dollar amounts attached to the status of those orders and sum them up. So, for example, if I wanted to see everything "On Hold" I'd enter this formula I am trying to figure out, and it would should me the sum for everything on hold.
Right now, I've been working with these formulas, but noticed there would be a problem (most likely) with Excel not looking at each occurence of the word "On Hold"; it would more than likely just stop at the first one:
=IF(E2:E51="On Hold,I2,"0") or =IF(E2:E51="On Hold,I2:I51,"0")
A friend of mine asked if there is a formula that will add the "Y"'s and "N"'s based off of a filter per certain dates. Below is a brief example.
2/18/2009Y 8/15/2006N 2/18/2009Y 8/15/2006N 4/6/2005 N 4/6/2005 N
We want to be able to sort by dates so regardless of which date we pick, below the Y's and N's will be the total for the Y's and N's that are visible. If sorted by April, it will show 2 N's and 0 Y's, and so on. Is there any formula out there that can do this.
I assume there's a database (or pivot table?) solution for my task, which is to detect the days, weeks and months within a very long table, and obtain the totals of the data therein.
I have Master sheet where I collect info from sub sheets. All sheets are similarly formatted, ie. product numbers on column A and headers on row 2. I need to sum values from all sheets based product number and header. Master sheet includes all product numbers and some extra headers, sub sheets include only needed numbers. Headers on sub sheets are identical.
Currently I have this thing solved with following formula:
But problem with this is that column is hard coded, so I have to know that that value I am looking for is in column L. That wouldn't be show stopping problem on its own, but I have columns all the way to DR and copying formulas for each column takes a lot of time when I have to manually update each column. Just copying cell holds that L:L and doesn't change it.
So, in addition of getting values for specific product number I need to get values from specific column based on column header.
I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.
I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.
The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.
I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.
Code: Sub DeleteAll() Windows("Template.xls").Activate Dim r As Long For r = 2 To 36500 If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then Range("D" & r).EntireRow.Delete = True
I found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]
Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?
For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.
I'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...
I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.
Here's an example of what I'm working with:
5 10 15 20 50 2 4 6 8 20
This is what I need the final product to look like:
5 10 15 20 50 2 4 6 8 20
So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?
I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).
As filter I would like to be used A,B,C columns.
EXAMPLE: BEFORE A B C D E F G H Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1 Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1 Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1 Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
AFTER A B C D E F G H Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
ive got columns of dates that i want to aggregate up into a single column of distinct dates. the dates columns are of varying length, some with gaps in the middle of the column, and some columns are blank. dates in teh columns may duplicate, but i only want distinct dates copied in the single column.
i cant do this by hand because there are dates in around 200 columns so i need a macro or way to do this quickly. ive attached a picture to explain the problem.
I have two very long columns of client names. The columns represent reports that were run at different times on the same information.
What I want to do is merge the two clomuns. The issue is that there are many names that are the same but some that are different. Is there a way that you can merge the info. So it ignores the client names that are the same but auto adds the ones that are new. There are financials in other columns that I want to have come along during the merger.
I need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".
I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.
OK, I have a spreadsheet that has several pages for tracking when items are added and removed. On the main sheet I need to have the total items added, the total items subtracted, and the total avaialble. This is also based off of month.
For example, on sheet 2 it would show 5 of an item added, 2 of an item subtracted, all for March. It would also show 3 items added, 2 subtracted for April. On Sheet one i need to report in a row any items added, any subtracted, and the current total. I am pretty sure that i have the formula for added items and subtracted items for the given month, but i do not know how to get the current total.
I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden.
I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data without one column overwriting the other. If only there were an automatic way to merge two columns of data and to be able to place a delimited character in-between, just like the “Convert Text to Columns Wizard”, except in reverse. Currently, the only way to merge two columns of data is to manually go row-by-row and cut and paste them together. However, for 500,000 rows of data… this is impossible. Or to use a function to merge two columns, however this requires that the original two columns remain. This is also unacceptable. If Microsoft really wants to make Excel more functional, how can this vast improvement be overlooked?
I am looking for some tips to merge the 2 rows into 1 row with all data transferred. I have tried the following:-
=IF(MOD(ROW()/2,1)=0,INDEX($B$1:$B$100,ROW()/2),INDEX($A$1:$A$100,INT(ROW()/2)+1)) However, it returns "0" when cell is empty. Your help is greatly appreciated. Attached a file for better understanding.
Note that the "Fruit" and "Fruit Totals" column letter position will always be different every time I run the macro, so I would like the macro to find these columns by name rather than by column letter.
I'd like a macro that adds the total number of fruits in the "Fruit" column by looking doing 2 things...
1. Add the total number of "Fruits" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Fruits Total"
2. In the cell directly to the right of "Fruits Total" put the value of the total number of fruits in this cell (which will be in the "Fruit Totals" column)
3. Add the total number of "Apples" in the "Fruit" column, find the bottom-most row of data in the "Fruit" column, go down 2 rows, and create the text named "Apples Total"
4. In the cell directly to the right of "Apples Total" put the value of the total number of apples in this cell (which will also be in the "Fruit Totals" column)
