I have rows of data that repeats every now and again and codes next to them What I need to do is have same row of data (without it repeating) and relevant codes next to these across the page. Tried doing Pivot table and failed. Example and proposed format enclosed.
View 4 RepliesI have a data validation list, but i dont want the user to be able to make the cell blank. I have 5 items in the list and i wont to make sure they choose one. But the data validation allows them to enter blank.
How to highlight rows or data if data repeat in complete row.
View 3 Replies View RelatedI need to data sort several rows of a worksheet, followed by a different selection of rows, followed by another selection, etc, etc. The first selection is done by highlighting the rows and then following the data sort menu. \
However, if I try to highlight a second selection and use the repeat command, excel repeats the action on the first selection, not the second. As a result, I have to use the data sort menu again. There must be a repeat option which acts on the new selection.
I do have some other coding background. I am working on some VBA script to run against data being put into a single excel sheet. Column C will always have data, but column D will not. What I need to do is loop through each row with something like this:
Staring at row 2 (since row 1 is a header in this report)
If Column D is not blank, then subtract D from C.
Repeat for next row until the end of the data
I'm trying to create a formula (if possible) that will repeat column headers down a row and repeat every nth time. I have text values in cells E6:AL6 and would like to use a formula that will make it so that E6 will show up B2, F6 will show up in B3, G6 will show up in B4, etc. And then when it gets to B35 (AL6), B36 will then revert back to E6, and the loop will continue - B37 will be F6, B38 will be G6, etc.
My thought is to create an offset/address of some sort that will read as OFFSET(E6,0,x+1) and if x>35, start back at 0. However, I don't think you can create such a formula using an offset?
I have spreadsheet which consists of two columns of data.
Column A contains a gerneric heading/group and Column B details some results for each heading/group; these results cover between 16 to 40 rows.
Basically, I need to transpose each heading/group's rows of results data into the same row as the heading/group row; then delete the rows where the results data was copied from; then I want to go to the next heading/group in Column A and repeat the process - transposing the results into a row.
I have written a simple macro which seems to work intermittingly - one which doesn't repeat or loop though. Sometimes I get the Runtime Error 1004 and sometimes the macro overwrites the transposed row if the results are listed over 16 rows.
My draft macro code is as follows:
Sub Transpose()
'
' Transpose Macro
' Macro recorded 27/11/2008 by Hunter
'
' Keyboard Shortcut: Ctrl+p............
I frequently use a pivot table to extract summaries from a large data set and then use the pivot table output itself as a data table for further analysis. Where a data value in field 1 has many corresponding values in field 2, the field 1 value appears only in the first line and the field 1 cells for the remainer of the rows below appear as blank.
Currently, I copy the pivot table and paste values elsewhere and populate the blank cells with the value above in order to get a complete data set. I would like to find out if there is any way the pivot table can be configured so that where a field 1 value has multiple corresponding items in field 2, each of the cells in field 1 would repeat the relevant value.
How can I repeat identical data in the previous cell. For example I have in Cell A1 - Marketing then I have in cell A2 to A3 same Marketing but it is not shown. Then I have in Cell A4 - Operations then Cell A5 to A8 same Operations but not shown. I need to have them all shown as in Column B,
A
B
1
Marketing
Marketing
2
Marketing
[Code] .......
I would like to copy rows based on the value of another cell.
For example turn this
code qty
apple 3
banana 4
orange 2
into this
apple
apple
apple
banana
banana
banana
banana
orange
orange
I've been trying to find something that can do the following. I would like Column D and E to auto fill with a repeated data when the user fills out column B. For example,
Column B | Column D (auto filled) | Column E (auto filled)
101 | 1 | 1
102 | 1 | 1
103 | 1 | 1
how to make conditional formatting for border line (top & bottom) after repeat 5 data/text ....
it's possible with CF formula and do not vba?
see my excel file attached..
I'm working on spreadsheet where I have to match the data on two columns, and then count how many times that same number appears on both columns (see below table) In this below sample, I should get total of 3. (23654 , 12345 and 57963).
A
B
12345
23654
67891
12345
57963
11111
23654
57963
56954
77777
1. The idea is that the macro will start on sheet1, look at column "Jon", copy the values down to the last active cell as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B8 and B9. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
2. The macro will then go to sheet2, look at column "Jon", copy the values to the last active as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B18 and B19. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
3. The macro would then save the "Jon" tab as a separate pdf in a specified location.
4. The process would then repeat for "Mike" and "Paul". Each day the number of columns can fluctuate, so it may be 3 one day (Jon, Mike, Paul) and 8 the next. The number of rows also may vary from day to day, and column to column.
I have attached a workbook that contains the data for the scenario above as well as the output (Jon, Mike, Paul tabs). Ideally there will not be new tabs that remain for Jon, Mike, Paul, just a default tab (e.g. "output" tab) that would receive the transposed data from the columns for Jon, Mike, Paul. I put all 3 in the workbook so you could see how each of them would output.
I have a VLOOKUP formula and if it looks and does not find a match it will either put a "0" or a N/A. Is there something I can add to it to leave the cell blank if it does not find a match?
View 9 Replies View RelatedBasically I have a =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) And sometimes it doesnt find the number its looking up, so theres no data to display, so it just shows #N/A is there anyway I can make the =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) display "not found" if it doesnt find anything?
also sometimes if it does find the number its looking up, and the cell next to it is blank, it just displays 0. I could filter it.. and then just replace all the 0 and #N/A with not found, but im just wondering if i could do it in the code too.
I've developed a VBA application for excel in my english version. Now I sent to the client, wich is dutch and use dutch office. they say it gives errors at simple lines like TextBox1.Left=100. It seems there is no Left property. I installed their office but Left is there for me. I suspect their configuration have some version of VBA with translated code language... like using Links instead Left... wich is same thing for dutch. What could cause VBA translation of properties so I can have same configuration as they do?
View 4 Replies View RelatedI am using Excel 2007. I have read that if you open an excel 2003 document, that has external links, the links will be recacluated automatically. How do I change the settings in '07 so that the links are not recalculated when I open a 2003 excel file?
View 9 Replies View RelatedI have a spreadsheet with rows as budget line item and columns for the months they occur.
I have a cell on each row (in column B) displaying the total of that row for all the months. (columns C to Z)
I want to be able to hide certain months (columns), and have the total not include those hidden columns; but the formula for the totaling cell ( "subtotal(109,C3:Z3)" ) appears to continue counting all the columsn, even the hidden ones
I am making a spreadsheet and would like the "placement" tab to look at the "years in position" column and go to another tab and find the corresponding data then put that information into the cell that the formula is in. I have concatenated and the value carries out to about 6 decimal and I cant change the formating. I would like it to display as a currency with 2 decimal places.
If you look at the placement tab cell H7 you can see what I am trying to do. If you change the value in d7 to a value that is on tab 06-09 step plan (b9-b23) then the value is put in other wise noting is returned.
=SUMIF('[Weekly Sheets.xlsx]W1'!$A$8:$A$10000,$C$1,'[Weekly Sheets.xlsx]W1'!$K$8:$K$10000). It works fine but I need it to also lookup cell B8 if B8 has text named "ABS" dont count whats in cell K8
View 2 Replies View RelatedThe first one works fine but the "Description" and "Customer" paste over the top of each other and dont find the empty cell. im sure im missing something!
This submits the following details into the "Stored" table. It also finds an empty cell.
My dad wrote me a VB programme to save me time entering repetitive data into an excel template for my business. At first it didnt work untilll i reduced security to low. Sice then I fried my computer and have had to reformat it with a diffirent version of windows, vista service pack 3. Now even on low security it doesnt work.
The little button in the worksheet to make the macro happen isnt visible or doesnt exist.
I have spread sheet that adds meter readings. As you enter the reading it subtracts from previous month's and gives you net value. But this always shows a negative value in the following month until you enter the current reading.
Also I would like to be able to total these net values as a running total. Is there a simple way to not show the negative values and or use those when getting the running total?
Sub unprotectEm()
ActiveSheet. Unprotect Password:="shreked"
ActiveWorkbook.UpdateLink Name:="R:SHAREDPASSACCEXCELPASSACCCONTROL.XLS" _
, Type:=xlExcelLinks
ActiveSheet.Protect Password:="shreked"
End Sub
Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly = True Then MsgBox "READ ONLY YOUVE BEEN WARNED"
End Sub
It works fine until its been opened as "read only". What I mean is I open the file and the read only msg box comes up no prob. When the file avail for editing again I open the file and its not updating the control list.
I have a sheet with data only in column A. I want to remove any rows that dont contain the word MAR. All the lines with MAR in start with: CN=GR GG COF MAR ****
macro which would do this as i have over a 100 sheets. and maybe an explanation as i am totally new to this?
I am using office 2003 and I need to create the conditional (just like in the attached image), but I need that the days -1 (day) -2(days) or -3 (days) that are in the end of each formula, I need them to be weekdays (i don't want the to count weekends.
View 3 Replies View RelatedI was done but the formula works great except if the cell is zero or blank I don't want it to include that cell in the average. in this case it still counts.
=IF(SUM(H32>0,H67>0,H102>0,H137>0)=0,0,SUM(H32,H67,H102,H137)/SUM(H32>0,H67>0,H102>0,H137>0))
I wondered if I could pick your brains and hopefully get some answers to my problem. I have recently been using the Personal.xls worksheet to make all my Macros available to all open workbooks so that I don't have to cut and paste Macros each time I wanted to use them.
Now I know you sometimes have to make certain adjustments to Macros in order to make them work globally. I wondered if I could find out how to amend the codes below to make them work properly. Thanks.
The first code Macro below deletes all worksheets in a workbook except for the currently active sheet. However it no longer works.
The script works however I dont want the formulas to be entered into the cells that dont have any information in them because I am using them in a list box. To sum it up. So if sheet LA (Range A3:B98) is empty then dont copy the formula. If there is a data in this range then copy the formula. Here is the script that copies the formula's I just need to check sheet LA before it does this.
View 2 Replies View Related