I would like to sum multiple columns in a table driven by certain criteria. The criteria is text and it is specified by a text function that is date driven and it also includes a wildcard. Essentially, this table contains YTD information, each month a new columns are added. I would like the formula to update automatically when the new accounting period date is populated.
I've tried sumif( and sum(if( using an array, but was not successful.
I have an problem transposing multiple columns into a table. Source data is organized in 3 columns - ID, Visit#, Date. What I need is a seet in which I'd have in Column A - and ID, in Row 1 a Visit type and Visit date would be populated in the table. See attached file. Since I'm handling about 50k datapoints a simple "paste special--->transpose" is a nightmare.
View 5 Replies View RelatedHow do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.
View 4 Replies View RelatedThis is a sample of the attached workbook.
Project
Task
Current Phase
Start Date
Planned Finish
Status
[Code]...
What I am trying to do is break up the "Phase" column into several columns, where each phase would have its own column. Something like this:
Phases
->
->
->
->
->
Project
Task
01.
02.
[Code]...
It is very important that the output of the data goes on a new sheet, and that the records maintain integrity.
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Solved
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
sort>sort>sort>sort>VEHICLECOLOURSMAX SIZESPEEDA9brown50100PRONTObrown100110VELICITOgreen200120
SPECIALpurple50155ZEROpink2585GREENVgreen5155MIDEOblack2000135DELVOpurple300155ASPIROblack6155
I am trying to round a number to the next half penny. The mround function would seem to work but does not work for fractions.
View 2 Replies View RelatedMy pivot table wants to total 2 sets of data and put it in rows. I would like it to be in columns.
The small attached sample shows what I would like.
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.
I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.
{Using Excel 2003}
NameOwnerJan JanJanFebFebFeb
PlanActualsForecastPlanActualsForecast
Project 1Region 1 126.1 119.6 119.6
Project 2Region 2 18.0 0.9 0.9 8.2 8.2
Project 3Region 2 80.0 2.6 2.6
Project 4Region 3
Project 5Region 3 60.0
Project 6Region 4 55.8 55.8 55.8
Project 7Region 4 19.4
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
I need to Match to columns of one table to another two columns of another table, both tables are on the same worksheet.
One table has 500 rows more that should not be, I need to find out which of these 500 rows should be deleted, so the two tables should be same. So the batch number and corresponding amount should tie to another tables batch number and amount.
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
Vlookup a table, to another table that has multiple values.
E.g.
Table
GPASP002
Look up table - need to return second coloum but not the first value, all the values?
GPASP002 KZASP100
GPASP002 KZASP500
GPASP002 KZASP600
GPASP002 KZASP501
GPASP002 KZASP502
GPASP002 KZASP601
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
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?
get the code for sending multiple emails as per the spreadsheet list. Assume the spreadsheet has 100 line items and each columns specifies the name of the person, value, recipient email address ("To" and "CC") and sender name.
And the Body of text is :
Hi "Name of the person" Please find the value of "Value" to be paid for the moth of xxx and kindly let me know for further clarification.
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
Example:................
The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............
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 am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
View 2 Replies View RelatedIn the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedI have a large database of research data where the first column contains different categories (labelled as I, II, III, IV, V, and VI). For each row there is a data value in the second column that is numeric. What I need to do is move the data so that the data are displayed in 6 columns with the data values listed each column heading according to the category label. In the example I've shown there are 6 different categories, but the number of categories (and hence the number of columns in the final resulting sheet) will change for different data sets that I'm using. Also note the the number of cases for each different category is different (so that each column will not be even in terms of the number of rows of data under each column heading). I hope I've been able to explain what I'm looking for clearly.
I've attached a sample file showing the sample data input that I have on one sheet, and then the desired re-tablulated outcome on the second. I have done this using the auto-filter function and copy/paste, but this will take far too long for the larger datasets I'm working with.
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.
View 3 Replies View RelatedI have columns A through AKP (A:AKP) with data, and there is a varied number of rows for each column. If possible, I need a macro that will "stack" columns in pairs of two. For example, column "C" directly under column "A" and column "D" directly under column "B" and so on for all columns A:AKP.
This is an example of what my data looks like:
COLUMN A COLUMN B COLUMN C COLUMN D
ROW 1 2598 F800 2599 F800
ROW 2 2598 K1300 2599 K1300
[Code].....
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
I have 8 different files all have a set of data in them
each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank.
I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
Sample data:
Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication
[Code] .....