So, this spreadsheet has some duplicate rows and some unique ones. All are needed as duplicate zips mean that the dealer is sending out multiple mailings to the same zip code.
My task is to transfer these into Worksheet B so that duplicate zips go in different columns but unique zips don't. So this is what Worksheet B will look like:
Worksheet B
Dlr# Dlr Name Drop 1 Drop 2 Drop 3
X123 Dane's 50266 50266
X123 Dane's 53135
X983 Andy's 50254 50254
X348 Ryan's 45678
My spreadsheet has over 500 dealers with over 1500 rows of data in it, so if there is an automated way to create spreadsheet B.
I am still trying to develop a macro that will copy and paste a set of formulas into a dynamic destination range. I have included a worksheet that shows what I am trying to do. Basically when using AutoFill I need the columns of the destination range to be dynamic based on the number of rows in another sheet.
I'm trying to produce a fixed format pivot table in Excel 2010. Normally I'd just construct a manual table using COUNTIFS, SUMIFS etc, howver, for this exercise the requirement is to be able to click on any field and have a tab pop up with the relavent data a la Pivot Table. But....I need the Pivot table to have a fixed format (which I can do with 'Preserve cell formatting on update') and to have all of the rows and columns in irrespective of whether there is data or not (i.e. if I haven't sold any apples in June, I still want the 'Apples' field to appear, just with a value of zero). I had assumed that the option I needed was 'Show items with no data on rows/columns' but these options are greyed out. I've tried right-clicking on the whole table, on individual fields and on labels but still get the same greyed out options.
Is there any way I can retain all of the rows and columns?
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category.
I have a large spreadsheet that is 65000 rows and colomns A thru W. I need a macro to delete rows that if Date and Account and Type are equal and the net of Quantity is zero delete those rows.
Col A = Date Col C = Account Col D = Type Col J = Quantity
What the macro would do is if an account had 250 rows of activity on say 7/7/2004 (Date) and same type of account and the net of all Quantity is equal to zero delete those rows.
I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.
I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).
Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.
Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.
The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?
I would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.
Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.
I've not used SUMPRODUCT previously and can't understand how to get results for the attached.
I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.
I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.
The second table is actually in a different sheet but is the source of the data I need added.
I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.
In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?
I need to insert one or two rows depending on the criteria of two different columns. We have two shops (A and B)...and the sales are expresed like this: ....
I posted this on Mr Excel, but did not get an answer. I hope someone here can help.
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code" Column J = "Exp" Row 2 > this month Row 3 = Forecast.......................
I want to create a 6th column that looks to the columns on the left with data in ti and concatenates all data in the 5 columns and puts it into one cell in the 6th column however put a space between each break of data so that it can be distinguished which bit of data was in what column previously.
The challenge is the new 6th column can only contain 30 characters - When it exceeds 30 characters then create a 7th column and put the rest of data in the 7th column, again the 7th column can only have 30 characters so if exceeds this then put the remaining characters in a 8th column
There will never be more than a total of 90 characters in the original 5 columns so there will only need to be scope for a maximum of 3 additional columns
So for example
Column A had two words in it that totaled 20 characters (the space between the two words is also counted as a character) Column B had two words in it that totaled 20 characters (the space between the two words is also counted as a character) Column C had a word that contained 10 characters Column D had a word that contained 5 characters Column E had a word that contained 10 characters
Then the result would be
Column F would only have the data originally held in Column A (because it can't include Column B's data as this would exceed the 30 characters) Column G would have data that was originally held in column B and column C - with a space between B and C data Column H would have data that was originally held C, D and E - with a space between C, D and E data
Another point to consider is if in one of the orginal 5 columns had say 3 words in it and lets say the 3rd word is the word that exceeds the 30 character limit, then the whole of the third word is to be carried oved to the next new column, I can't have words cut in hlaf with one half in Column 'F' and the other half in Column 'H' for example.
I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted. The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I need the different categories to be separated into separate columns, but they do not appear to be separated by anything useful or a fixed width. Not only that, but the last few columns were moved onto a second line when I copied the data into Excel for some reason. The data originally came over in an Adobe Acrobat file. This is what the final product should look like: ....
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD =SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference) =SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?
Name Start End Other data Verizon 1/10/2010 1/25/2010 AT&T 3/2/2010 4/14/2010
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
EG
Store 1 1111 16:40 2222 13:00 Store 2 1111 15:05 3333 16:50
I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post.
Sub split() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = InputBox("Please enter the Text File's name, e.g. test.txt") 'Check for no entry If FileName = "" Then End 'Get Next Available File Handle Number FileNum = FreeFile() 'Open Text File For Input Open FileName For Input As #FileNum .................
I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.
The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.
This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.
I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?
I have a long (190,000) list of customer data, all in Column A (unfortunately with blank rows among it, but working now to fix that).
Down the column, individual customers are bookended by a "adf" and a "/adf". (these have open and close brackets like HTML code, but I cannot reproduce them in this forum).
For each customer, I need to find the rows that begin with (brackets spelled out since I do not know how to show them):
And transpose only those rows it into columns.There is a dynamic number of rows for each customer, so there's no way to simply count and transpose, as the columns would all be mis-entered.Somehow it needs to recognize those 6 row items, and transpose those values only, with the and the only telltale of a start and finish of a specific customer.
EDIT: How about a macro to delete all rows except those that contain those partial values above?
