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 RepliesExample:................
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:.............
How 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
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.
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 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 am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedI have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies View Relatedhow to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
View 2 Replies View RelatedI have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
I have multiple names in each of 10 groups. Each name will have a number beside it. I wish to have a separate worksheet that will contain only the name with the highest number beside it, from each group.
View 5 Replies View RelatedI have an Excel sheet with one column (column A) and I have 503 000 data rows. In the first 503 rows I will need to insert the data group1. The next 503 rows, I will need to insert the data group2. The data always increment by 1 every 503 rows.
I could do it manually for few groups, but I need to create 1000 groups (503 000 rows). So you can imagine the load of work if I would do that manually.
Does anyone can explain me how to create a macro or anything that could automate this?
Im using Excel 2007 that support more than 1 million rows vs the old Excel versions which is a max of 64 000 rows
If you have 5 option boxes in the group at the top of a form and want to make sure the user of the form selects at least one box before continuing on, how do you do that?
View 9 Replies View RelatedI created a pivot table with daily dates listed from 1/1/2009- 3/31/2009. 3 months worth. I see i can group this data by hours, days, months, quarters and years but what i really need to do is group this by WEEK. Does anyone know a formula I could use to accomplish this?
View 3 Replies View RelatedHow to insert a group between another - see att. picture.
I have tried several solutions but havent yet found the right one.
The rows are not locked.
Picture: Capture.PNG
How to group different record and name the group. Attached file
View 14 Replies View RelatedI have a big database that has different name in column A(something like Y1, Y2, Y3, Y4, Y5, Y6, Y7, Y8...). What i'm trying to do is to group them! In group A i have Y1,Y7, in group B i have Y2,Y3, in group C i have Y4, Y6 and etc! I think the solution is something like index...but i never worked with that in VBA! So each name of group A should copy on a new sheet, the next rows should come group B and etc! Is there a way to do that in VBA?
View 11 Replies View RelatedI have uploaded 100,000 of record which is already sorted by group.
Now I have to insert balnk row or line that differentiate data ...
To do it manually is to much time consuming ..so can anybody help how to insert line or blank row between sorted group ..
Example...
111 dsdas
111 gdfg
111 sdfsf
222 sfsdf
222 cvx
222 vcxvx
222 vxcv
Desired result:
111 dsdas
111 gdfg
111 sdfsf
-------------------
222 sfsdf
222 cvx
222 vcxvx
------------------
Looking for quick reply.
222 vxcv
I have some VBA code which inserts a new row at the bottom of my data and copies the formulas down from the row above. This all works fine. However I now have the need to insert a new row within the data into the correct group.
So within the data there are 4 product groups (sorted).
So column A looks something like this (but much bigger):
Product1
Product1
Product1
Product2
Product3
Product3
Product4
Product4
Product4
I want the user to be able to select "Product1" from a drop down box (which I've set up). Then click the button called "New_Data"
So "New_Data" runs the code and inserts a new row at the bottom of all the other Product 1's (in this example it would be between row's 3 and 4).
Here is my existing code for just inserting a line at the end:
With Sheets("Sheet1")
.Unprotect
With .Range("A1").End(xlDown)
[Code]....
I have 2 columns, A and B. The data looks like this:
0040005A2002868000PMTo 164.40
003000005000037000PMTo 104.40
001000002002090000PMTn 188.35
002000002000015000PMTn 104.35
001000002000298000PMTn 92.80
001000004001042000PMTo 78.00
001000004001050000PMTo 78.00
003000001002100000PMTo 97.10
001000004002115000PMTn 92.75
I want to have column J with values from column A grouped by the first 9 digits and column J with the totals for these groups. It would look something like this:
002000002 250
002000004 300
003000027 100
003000050 70
004000002 90
etc
I have a pivot table listing stock prices from Jan to today. I would like to group them as networkdays.
I see the option to group as days and then select number of day=5. However when I view the data, the 5 day group does not mirror the calendar.
It is 5 days in a group, but it is not the 5 work days of the calendar. It might be the last 3 days and the next 2 days of the following week. I am trying to get the same week to stick together.
I need to find the age group from the date of birth using excel. Eg.
if some one born between 1st Sept 1998 and 31st of August 2000, they come under 12.
if some one born between 1st Sept 1996 and 31st of August 1998, they come under 14. and so on.