I have this tab that won't show the marks when I group the columns or rows.
I know it is grouped becuase if you copy the data to the blank tab the marks appear fine. Copying the data is an easy work around but I would really like to know the root cause of this. The issue was only with this tab. I used to have a ton of other tabs and they grouped fine.
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have tried to construct a formula for columns M N O and P to extract the names of the four best marks for each student. I have used the 'Large' formula but it does work when there are equal marks in the list.
Has anyone used a formula to order the first four highest marks and show the headings?
Here is a sample of the Spreadsheet I am using.
(Using Excel 2003)
Column headings are:
(ignore the commas - they are just spacers for the data when posting)
,B,,,,,,C,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,M,,,,,N,,,,,,O,,,,,P
Foot, Athl, Base, R.Cl., Rug, Crick, Tram, Bad, TT, Gym, Golf,,,,, 1st,,,2nd,,,3rd,,,4th
,20,,,,,25,,,,21,,,,,19,,,,22,,,,,20,,,,,24 ,,,25,,,,21,,,24,,,,19,,,,,, Athl Bad Tram Gym
,25,,,,,21,,,,19,,,,,22,,,,20,,,,,24,,,,,25,,,,21,,,,24,,,19,,,,25,,,,,, Foot Tram Golf Crick
,20,,,,,26,,,,25,,,,,21,,,,19,,,,,22,,,,,20,,,,24,,,,25,,,21,,,,24,,,,,, Athl Base TT Bad
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
Rows 1 & 2 are on a single sheet - there are 26 counties in all, in alphabetical order and divided into IPF groups (as shown on row 1).
On a second sheet I have a data validation list showing IPF1, IPF2, IPF3... etc.
In the columns beside the validation list I would like to show just those counties in that IPF group (example in rows 4 & 6).
The following is how the data is displayed:
[JAN] [a] [FEB] [b] [MAR] [c] [Q1] [APR] [d] [MAY] [e] [JUN] [f] [Q2] etc.
Basically, it is months and quarter. However I have an additional column that is hidden after each month - [a], [b], [c] etc. Those are displayed whenever a cell in the previous column is clicked. Now, I select all the columns from [JAN] to [c] and group them and I have a + on the top most row.
Problem is when I click on +, it expands the month as well as [a] [b] [c]. I have a macro to hide these, but how do I trap the [+] [-] action and run the hiding macro after that?
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 need some formula to enable, me ,to make some manipulations. What I need is to present the colored cells (in row 3). I will use those results in another formula where I will use address etc. I assume it can be accomplished with a combination of COLUMN() and MOD or OFFSET - but I could not find it myself.
View 2 Replies View RelatedHow do I transpose as shown below?
I have to columns of data, Site number and Disc number. I have sorted by unique site number but I don't know how to transpose the data.
******** ******************** ************************************************************************>Microsoft Excel - testing.xls___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1SiteDisc****210123****310124****4101222****5103111****6109125****7109126****8109129****9109189****10109222****11******12******13SiteDisc1Disc2Disc3Disc4Disc5141012324222**15103111****16109125126129189222Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
i have seen this cool feature in a workbook, but I am unable to find out how implement it myself. I can't really explain it (thus I am unable to find good search results) so I have added a little picture of what I mean. Its about adding controls for hiding and unhiding columns. How do I get such nice plusses and minusses into my workbook? I use Excel 2002
View 4 Replies View RelatedI have a ton of worksheets that I maintain for Budget, LE, LE2, LE3. Every time we complete a Budget I need to group the LE Columns, and once we complete an LE, the budget column needs to be grouped and LE column ungrouped. So twice a year I need to make these changes. It is quite cumber some to change this for multiple tabs.
I already have a technical worksheet to dynamically change the date, naming etc. I want to be able to add a cell that will either read 200 (budget), or 300 (LE). Depending on what his cell reads, I want to group and ungroup their respective columns.
I need to move a portion of info over 4 columns. I need to do this after a certain criteria is met.
A B C D E
1 Mem# Mem Name Sta Area
2 123C Joe Smith G bev
3 4 Wine 4.95 6
4 5 wine 5.95 6
5 2 food 6.95 5
6 124G Fred Choi C Bev
7 4 Wine 4.95 6
8 5 wine 5.95 6
9 2 food 6.95 5
A3, B3,c3,d3 need to shift over 4 clmns then when nxt member #(a6) is met rows below need to shift over 4 clmns until nxt mem #, so on and so forth. Please Help. New to the macro scene.
Not sure if this is possible but I have a sheet of data which is effectively a project plan. It has a list of resources, role and phase etc. I then have lots of rows which are w/c dates and under each "week" I have the amount of hours or days they work. So for each person I can record their hours etc.
I then need a Pivot which basically collates the data and displays each phase's total hours but month. There maybe multiple phases or and each person may appear in each phase
I've attached an example - Excel Problem.xlsx‎
What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. If it doesn't read Bud, I need it to ungroup those columns and group and collapse other columns.
So for example: If I have 6 columns (A - F), when Date tab cell D4 reads 'Bud'. I want to group columns A and B, and only show C-F. If the cell doesn't read Bud, I want columns A and B to be ungrouped, and Columns E and F to be grouped and collapsed.
I've been racking my brain, using the Record Macro and just cannot get it correct. Granted I have created something similar to HIDE my columns, however I need it to be grouped so that higher management can ungroup to see some details if need be.
Code:
Sub GroupColumn()
Dim s As Worksheet
For Each s In ActiveWorkbook.Sheets(Array("Name1", "Name2, "Name3", "Name4"))
If Worksheets("Date").Cells(1, 4).Value = "Bud" Then
[Code].....
i have a macro that will group duplicate cell with a border base on column a
View 2 Replies View RelatedI am looking for a VBA to highlight a every other set of rows in a data set in grouped by name. I am able to do so by using conditional formatting but being that I will be doing this on a frequent basis. I have attached an example of what I'd to do.
If c2 = c1 then copy row color, if c2 does not equal c1 then highlight row light grey.
I'd like to select a range and then delete the top 10 rows, then skip down 500 rows and delete another 10 rows, and so forth until the end of the range.
View 9 Replies View RelatedI have a sheet named “Input” where the name of the persons appear more than once as the same is made by consolidating monthly sheets.
Now I want to make summary of each persons to a new sheet named “Output”.
In the new sheet, the name should appear once.
The name should summarize the total of all the columns corresponding to the name.
For eg name Ashu may appear more than once in the Input sheet but in the Output sheet this name should appear once and shall summarizes all the amount for all the rows where Ashu has appeared and so on.
******** ******************** ************************************************************************>Microsoft Excel - RowBasedTotal.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16=ABCD1NameAmount1Amount2 2Ashu10050 3Basu200100 4Natu300150 5Garu400200 6Ashu500250 7Basu600300 8Natu700350 9Garu800400 10Aayur900450 11Mayur1000500 12Basu1100550 13Gastu1200600 14Natu1300650 15Garu1400700 16Total105005250 Input [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
******** ******************** ......................
I need to create a vba macro which will sort data according to a code it is denominated by and then will apply specific values from a code key to the data. I really need to have some help with this as although my vba is getting a bit better this is definately beyond me.
View 3 Replies View RelatedI tried to make attached worksheet self-explanatory. find attached.
Basically, I want to number group of rows based on a logic (blank A,B, and C columns)
inincubus.grouprows.xlsx
I HAVE THIS CODE THAT SELECTS ALL THE ROWS THAT CONTAINS CORTAR IN "I" COLUMN
In columm (A) I have a row for each day of the year, that is, 1/1, 1/2.....12/31 which has a corresponding value in column (B). In column (K) I have a row for each week of the year, that is, 1, 2....52.
In column (L), what formula can I use so that each row will sum the values of a seven day period from column (B)?
I have about 30 worksheets in the same workbook that are identically formatted. My critical/formula data range starts in Cell A537 and ends in cell O576.
Column O is just a sum of Columns C through N.
I would like to know if I can Group (not hide) the rows that have a zero in Column O. Now with 30 worksheets, I will have some worksheets that may have 5 rows grouped and some with 20 and so forth. The numbers are not identical in the 30 sheets meaning not the same rows will be grouped in all 30 sheets.
That piece there might complicate things. Basically, a macro that looks through each worksheet and and starts grouping the rows that has a 0 in column O.
I also want to restrict my macro to the range above.
I have a workbook with data that represents trouble tickets assigned to different applications and sorted by application. I want to select all of the rows assigned to one application, and paste them into a new worksheet, then do the same for the next application, and so on. I don't know which specific applications I will be looking for, as this will change from day to day (the system has more then 160 apps). I've attached an example.
View 4 Replies View RelatedI am creating a new form, and have 2 option boxes. I have linked these checkboxes to a cell which gives a calculation depending on what is selected (cell E10). If the value of this cell is 2, then the person has selected that they don't need to fill in the details below. If this is the case then I would like to hid the entire rows below (rows 18 - 45 to be precise).
At the moment I have used data validation to blank out these rows, but I'd much rather group them automatically to avoid a large gap between the option boxes and the next section.
*I have my document ordered hierarchically with group functions.
*Aesthetically I would like to have the font size get smaller the more levels into grouping they are.
*For aesthetic reasons I have merged cells rendering auto-fit useless as it auto-adjust to the first column's font size even if it has no characters in it
I find myself contemplating manually adjusting all 500 some rows of data.. probably by selecting ranges with ctrl or shift clicking my way through the document, grabbing ranges based on how high their grouping is, and adjusting the whole selected group at once, so they all have a uniform height setting.
Can I do some kind of conditional formating based on group level? like all group level 7 rows have a row height of X.
Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011,
Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231
Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var
[Code] ........
There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.
What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using
Code:
LR = Range("C" & Rows.count).End(xlUp).Row
With Range("R10:R" & LR)
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")"
End With
I have a smaller spreadsheet with 40k rows/34 columns of data. Column A has serial numbers and does contain duplicates. The rest of the columns have varying data pertaining to those serial numbers. I'm sure there is an easy way to do this, however I have just not had enough coffee today because it is not coming to me. Here is an example of the data (not all columns obviously) and I put in ZZZZZ for each blank cell in the table and each space you see is the column break just for visual because I couldn't get it to paste here and have the staggering that it is doing.
See the laddering effect that is going on? When the data was imported into excel it created a new row for every value. What I need is all of the values in the same row EXCEPT if there are two part numbers referenced for the same serial number. In that case I would want two rows of the same serial number showing only the part number, description and two dates for that serial number in each row.
12345678SNABCPARTZZZZZZZZZZZZZZZZZZZZZZZZZ
12345678SNZZZZZHARDDRIVEZZZZZZZZZZZZZZZZZZZZ
12345678SNZZZZZZZZZZFALSEZZZZZZZZZZZZZZZ
12345678SNZZZZZZZZZZZZZZZ8/26/2011ZZZZZZZZZZ
12345678SNZZZZZZZZZZZZZZZZZZZZ9/24/2012ZZZZZ
12345678SNZZZZZZZZZZZZZZZZZZZZZZZZZFALSE
[Code] .....
Would like it to be like this (for the above data):
12345678SNABCPARTHARDDRIVEFALSE8/26/20119/24/2012FALSE
87654321SN123PARTPRINTERFALSE8/26/20119/24/2012FALSE
87654321SN987 PARTTONERFALSE1/1/201312/31/2013FALSE
Quick note, each serial number does NOT always have the exact same amount of repetitive rows. So there aren't 10 rows of 12345678, 10 rows of 87654321, 10 rows of 12345679, etc. They can be different. I do not have the original xml file used to import or i would just reimport it correctly this is all i have to work with.
I am trying to select all cells in a range whos contents match the contents of a specific cell so I can group the respective rows of these cells. I did a forum search but I am not sure if I have found what I am looking to do. I am a very basic user of VB so keep answers as simple as possible.
View 8 Replies View RelatedI am trying to develop a formula that will tell me whether an account has both Medicare and Medicaid or Medicaid HMO insurance. An account can have one or more lines, and one or more insurances. I manually completed the sample file in column 'D' (see attached) to come up with the desired output. The criteria is below:
-If the account has Medicare and Medicaid financial class, then it is a '5'.
-If the account has Medicare and Medicaid HMO financial class, then it is a '6'.
-Otherwise, it is a '0'.