I have an excel table which i have to summarise the content in the table, the contents in two consecutive rolls needs to be mergered together to become one roll, altough i do not know how to explain this better.
View 3 RepliesThe table has 13 columns and thousands of rows. The first column is the month field (ie, 200904) with all the following fields being procedure fields represented by codes (ie, aaaab, aaaac etc.).
Each row is a patient, the table therefore gives the month a series of procedures were carried out with all the procedures having specific codes.
The procedure fields can be populated in any number of ways and is essentially quite random.
What I want to do is count the number of a specific procedure that occurs in a month in a second table. The second table would have the complete list of procedures down the left with date running across the top. The cross section in this second table would therefore contain the count for that procedure in that given month...
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies View RelatedI have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.
In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.
The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).
View 8 Replies View RelatedI use a sumproduct to count a special content from a table.
I have the country, a special key and the product.
I have different products like rismo 12, rismo 100, rismo 200.
But i want to count them all.
So i have the following function.
=+SUMPRODUCT(((ISNUMBER(FIND("rismo";E1:E1000))*1)*(C1:C1000=D2)))*((H1:H1000=60)+(H1:H1000=65)).
In the cells c are the countrys. In the cells h are the special keys. I am looking for the 60 and 65.
My result is always 0.
i have a database of the kind:
Model Version Color Store
1 a zz HH
2 b xx QQ
3 c yy OO
4 d ww PP
I need to obtain in the y axis the model, and in the x axis the store.
The data per each model and store should be: the version and color.
I'm using Pivot table to do that, but i'm only able to obtain the "count" (or sum or other formula) but I'm not able to obtain the content of the cell.
Example:
Store HH PP
Model
1 Version a d
Color zz ww
2 Version b c
Color xx yy
I have several data sets that that have the same column headers (product portfolio data, split by categories into different workbooks) and the same structure and want to merge them to create summaries in a pivot table that I can slice the way I need to look at the data (e.g., by "Category 1" level, "Category 2" level, etc..). The messy way would be to just copy/paste all data in one master set and then create one pivot from it. The problem is that this a) takes a lot of time since I have several data sets and b) the content of these data sets is constantly changing, so I would need to constantly update the master data set by replacing updated sub sets.
I was hoping that I can use PowerPivot for that which is apparently not the case - as I understand PowerPivot is only being used to link data sets that contain a column with an identical key to then basically do on a larger scale what a vlookup would do. Is there a way to do this with PowerPivot?
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
How Can I Get First Cell Content ? here is my sheet
.... A
1 Color
2 Red
3 Blue
4 Yellow
note : Color has filter
I want to make the cell B1 = first cell of column A - I try type in B1 =A2 ، but when i make filter (example i select blue) the cell B2 show nothing (i want B1 to show the filtered data on first column )
When I type in a cell it will repeat what I have typed multiple times within the same cell. For example, if I type x, as soon as I move out of the cell it will become xxxxxxxxx. I have checked all of the formatting of a cell that works fine against these ones and they are identical, there aren't any formulas.
View 5 Replies View RelatedI've got two columns with data in only some of the cells in them. I need the cells with data in them to be put after each other at the top of a new column. Is there a formula for this?
Korsningshjälpen Excelforum.xlsx
I have a worksheet of about 500 columns (or more)... I need to collect from a certain row, only a few dozens that are non zero. How can I create a formula that will take only the non-zero values from the columns, and list them in a new array or row?
The primitive solution I use now is copying the entire line to a different sheet (pasted with transpose) and the sorting A-Z... It's annoying because I need to afterwards delete hundreds of zero lines that seperate the positive from the negative, and it makes me lose track of the order in which these numbers appeared...
I have a table in Excel 2007, I need to show the content of a column if it has data and if not leave empty, I think I need an if statement.
Column A may have content in a row but could have empty cells
Column B would be the same, but different rows, Column C to have the answer.
Like this
ColumnA ColumnB ColumnC ("Result")
Awe4 Awe4
Bye Bye
Empty Row
b78 b78
b65 b65
I`m trying to create a macros that find new cell content in a column and add a row before it.
In the attached file is an example : insert_row.xlsx
In column A is the name of the row. And in some rows the name is changed.
I want to add a row before this change.
Sum A1:A100 only for corresponding B1:B100 containing "paid". translate that to a formula? I don't know conceptually how to approach it.
View 1 Replies View RelatedI have been supplied a list of names. The format is Smith J Mr, I would like this to read Mr J Smith.
Is there a command in excel I could use?
What I am trying to do is take the data (all columns) in Spreadsheet 2 and put that data in Spreadsheet 1 with all the companies matching up, how do I do this, is this possible?
I tried using VLOOKUP but I don't know if I was doing it right and I don't even know if it's the right function to use.
In sheet 1, column A I have a list of about 30,000 companies
In sheet 2, column A I have a list of about 10,000 (pulled from sheet 1), I have other info associated with these companies in columns B and C.
Basically I want to take column A, B and C from sheet 2 and match them up with the same companies in sheet 1. So there will be 10,000 matches and 20,000 N/A's. This is how I set up the VLOOKUP but nothing happened.
I was reading Box Office Mojo and wanted to know which actor would have the highest box office total if worldwide sales were accounted for and inflation was considered (for some reason they offer the data in 3 different formats but that is not one of them). I had previously only used Excel for simple records so I had to search your site for ways to write a few macros but I got everything working. I want to present the data.
It looks like embedding a spreadsheet in a webpage can be done but is there anyway I can add a feature so that if the person browsing the site clicks on a cell that cell's data will removed from consideration? I don't want the cell to actually display a "0" but if the background color could change to signify it was no longer being counted that would awesome. A lot of people argue about what movie should be counted toward an actors total box office record (starring roles only, supporting roles, voice work, cameos) so I would really like to be able to allow people to easily remove roles they personally don't think should count towards the total.
I am looking for a vba to offset the cells.
The code I have currently is:
[Code] ........
This is only moving the selected cells across one, however - the content of the cells is what I wish to move so cell C25 has data pointing to another sheet cell A1 i.e. =Sheet1!$A$1 - can I edit this code so it alters the cell to reference B1, C1 etc....
I want a macro to show a message with a content in the cell B3.
For EG: "Rec as of 'B3' is created"
This has to be the message and 'B3' has to be the content in the cell B3.
I tried doing it but i am unable to show the content in B3 in the message.
select only cells with content. Is it possible to use the next
View 5 Replies View RelatedI have 10000 data in my column A. i can genterate random number from 1 to 10000 through function "randbetween".
Now i want to display the particular data which column a and row as per that random number. in short i want to display data of "column A row (generated random number) for lucky draw selection.
I am attempting to use a cell reference generated using the "address" function and text function (so the cell displays text only, not formula) as a means of defining the top left cell into which I want to paste a column of formulas. The reference cell is static, but the contents is dynamic.
View 3 Replies View RelatedI have a named range (D:16 F:800) called “NameRange” which contains names.
I’d like to create a macro which offsets a letter “x”, 10 columns to the right everywhere that name appears within the range. As an example: If I’m trying to find the name Tom, A dropdown menu shows the name “Tom” ( which I’ve created) whereby it calls upon a script to search within the named range from this:
Bob Kim Tom
Tom Sue
Mary Bill Ted
Tom Sue Bob
Fred Bill Sue
Bob Tom Kim
and offset “x” to look like this:.......
Hi i had created a drop downlist (Data Validation list) which consist of the city names and a masterlist of the respective City id to the city names on a different work sheet (same file).
But i would need to configure in such a way that when i select the city from the drop downlist it would appear as number ( City ID) on the excel file.