I have a spreadsheet that contains data with the following format:
5 EA/BX
12 EA/PK
Unfortunately, sometimes the data is input without a space:
5EA/BX
12EA/BX
I am looking for a way to insert a space between the data when one does not exist. (I cannot have more than 1 space between the alpha numerical character and the numerical character.)
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 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 several 4 groups that all need to do the same thing. Is it possible to write all of them in one group or do I have to write each one individual in the conditional formatting?
For example:
=ISEVEN(CJ3007)
=ISODD(CJ3007)
=$BA$3007:$BD$3055
=ISEVEN(BA3007)
=ISODD(BA3007)
=$AT$3007:$AW$3055
=ISEVEN(AT3007)
=$AF$3007:$AI$3055
=ISODD(AT3007)
=ISEVEN(AF3007)
=ISODD(AF3007)
Attached spreadsheet has the resource names and their date of joinings (Column - F2), now I would like group the date of joinings into Month & Year format. I tried text formula and then converted it back to date format but its giving incorrect results. For ex - Column F2 has 9th Sep -2013, whereas my formula in H2 is showing Sep-2014. Any way to group these dates into months in a simpler way?
Once I have this grouping done, I would like to do a pivot and sort them.
I have a spreadsheet with a content in cells. Some of the cells are indented left and some aren't. I would like a macro that groups all of the cells that are indented left above the cells that are not indented.
I've attached a spreadsheet : Book1.xlsx
I am trying to find a way to have excel recognize text data input as date/time.
[Code] .....
Where 02 Is the Date, 2020 is the time (military) Z is Zulu/GMT, MAR is Month and 14 is Year. I believe excel recognizes
[Code] .....
But I have a spreadsheet (on a confidential system) with thousands of entries that I need to convert. Also, the people I have working for me are not remotely. I reject the idea of entering data that way.
I am also trying to keep the display the same format: ddhhmm"Z" MMM yy
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
im tyring to make excel add one group of cells. but not like 5+5=10 but 5+5=2. confused yet i am. basicly i want it to only count the number of cells with numbers in it. im shipping boxes with differnt amounts in them. but i want to know how many boxs are shipping.
View 13 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.
When you highlight a group of cells and give them a reference name- that name seems to be permanent. How do you delete one or add cells to the reference? Example attached book1.xls Cells A1-B3 are named "TheGroup".
View 2 Replies View RelatedCan someone tell me it is posible to increase cels value (diferent values) from D2 to D21 for example, by 20. I just start to use excel.
View 6 Replies View RelatedI have a column of about 3000 numbers that need to be converted to bar codes. In order for the codes to work, I need to place a set of parentheses "()" around each number in the column. Do I need to do this by hand, or is there a function/script for this? Using Excel 2003
View 2 Replies View RelatedI have a question regarding automatic merging in VBA. Its similar to this thread (Macro Required - Automatically Merge vertically identical cells) but with a slight twist.
So say if I have four columns set up as follows, where the first line contains headings (before)
Team
Position
Salary
Term (years)
Arsenal
Goalkeeper
100
1
[Code] .......
Im looking for something like this (please ignore the periods as i had to put them to keep the table from incorrectly displaying). I'd like to highlight the non-merging of Striker (line 7 and 8 in column2) becasue one belongs to Manchester and the other to Arsenal
Team
Position
Salary
Term (years)
Arsenal
Goalkeeper
100
1
[Code] .......
currently i have a address of a cell, i.e. "$C$3". and i want to search for this cell from a few group cells i.e. "Range1 = $A$1:$A$10", "Range2 = $B$1:$B$8", "Range3 = $C$2:$D$8". after searching for the matching cell, the macro would return the name of the group cell to me, i.e. in this case is "Range3". how would i go about doing that? could anyone enlighten me on this?
View 5 Replies View RelatedI 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 RelatedIf I have 5 names like apple, boy, cat, dog, energy
and if i enter a in a cell it should output apple in the same cell.
example: (=IF(A1="a",A1=apple,0))
How to do this?
I am trying to figure out how to Concatenate a group of cells if they contain a certain text. Basically I have a list of Architects in column A and the account name in column B... and I want to say Concatenate column A, IF column B=Specific Account (ex: Coca-Cola). The list will be added to frequently so the formula must refer to the entire columns, not just a certain range.
As of right now, only the first architect associated with each account will appear. I have attached a workbook with the example..
example.xlsx
I have a table with 2 columns A & B and desired outcome in column C (see attached sample).
I need to assign the same value to cells (in column A) located within the same group (in column A) using the following rules:
If any cell value within the same group = W220 or W210 or E240 or E250, then assign value "Group A"
If any cell value within the same group = P210 or C100, then assign value "Group B"
If any cell value within the same group = N230 or N250, then assign value "Group C"
I have a workbook that contains part numbers and their components listed like below. There is always one empty row between the part number and the components, and then 3 empty rows between the last component and the next part number.
The number of components varies from sometimes only 1 up to 25. Currently, I select a part number and its components like B1:C5 and then I have a macro that copies it to another workbook. What I would like to do is somehow get the macro to automatically select the next set down, in this case, B9:C14, so I could copy every set over to its proper place with only one click of the button.
i have a worksheet that is about 100rows x 100columns.
i would like to create some code to find a certain record. for example..
a1 a2 a3 a4
the record might be " tony tony alex tony"
basically, this group of cells could be any where in the group and i need some code to search the worksheet and then outline it. i just want to be able to find a certain block of cells and outline it.
I have some code which refers to a group of cells in another workbook as follows:
Workbooks("SUMMARY06.XLS").Worksheets("JS").Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value
I wish to change the reference to the sheet reference rather than the tab name, but the following doesn't work.
Workbooks("SUMMARY06.XLS").sheet103.Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value
And if I make two or more references to the same Workbook, can I use a with statement, like:
With Workbooks("SUMMARY06.XLS")
.Worksheets("JS").Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value
End With
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.
Is there a way to hide certain cells using the protect sheet option? Checking the hidden box on the format cells dialogue doesn't seem to do it. I know you can do it by entering ;;; in the custom formating but then if you have to remember which ones you did that too. I would like to be able to hide and unhide a group of cells easily.
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 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?
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09
View 2 Replies View RelatedI am working on a contact information form for given relationships, and I'm trying to figure out the best way to design a button that copies the "form" (not an actual VBA form but a grouping of formated cells for information collection) and inserts it into the document. I would also like a button that deletes unused and empty forms so that we only print forms that are filled in.
I tried recording a macro to do it, but it's messy and creates issues with hidden rows and pagination.
I am looking for a formula in D7
In col E I have a range of numbers
In Col AA I have yes or no
I would like to add all of the col E amounts from line 18-72 where the answer in AA of the same line is no or blank
E AA
18 2.00 Yes
19 3.00 Yes
20 5.00 No
21 7.00
D7 = 12.00