I have a sheet having over 10,000 rows. I am giving an example:
Column A Column B
1. Ron NY
2. Ron SF
3. Dennis Australia
4. Rohit Delhi
5. Rohit Mumbai
I want a macro which gives me the result as below:
Column A Column B
1. Ron NY,SF
2. Dennis Australia
3. Rohit Delhi, Mumbai
I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?
View 2 Replies View RelatedI did my search, but cant find and knows what key search to look/type for...
If i have data A1 through A10, such as 1 1 2 2 2 2 3 3 3 3
How can i get column B1 through B3 as 1 2 3 ?
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?
/code
Dim cell As Range
For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp))
If cell.Value = "xx" Then _
cell.Value = "01"
Next cell
/code
I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:
=SUMIF(D2:D110,D112,E2:P110)
My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
View 1 Replies View RelatedI'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
I have the following data in 1 of the tabs.
[TABLE]
ClassSequenceAB
10SE132422
20SE23212
20SE321
20SE23425
10SE332455
15SE132412
10SE234
[/TABLE]
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE]
ClassAB
1032
1511
2012
[/TABLE]
Can this be achieved through a formula?
Assuming I have a data set like this :
column a column b
916 12,5
916 23,4
923 34,5
923 23,9
932 98,6
934 67,7
How can I get a sum of all cells in column b when values in column a are equal?
What is the formula for results in C?
A | B | C
NAME|RNK|SORT
Frank|2|David
Jane |3|Frank
Lisa |5|Jane
David|1|John
John |4|Lisa
Abby |6|Abby
I have an excel file and what i want is to sum two different columns to a third one, however there are some important notes:
1. Column A has dates
2. Column B has numbers f.e. 25, 30, 35 etc. , which have to be added to dates of column 1 , so a new date will be created in the new column
3. Colums A and B are not next to each other.
4. Some cells of column A and column B are blank , actually columns are sth like this
A B C
5/2/2014 25
blank blank
8/5/2014 35
10/9/2014 30
blank blank
When a date value occurs in a cell of column A , then always a number will occur in column B
When a cell is blank is column A , then the respective cell of column B will also be blank
What i want is to add values of column A and B to create column C automatically ( each column has 1000 cells ) , however in case of blank cells , i want a blank cell in column C, not something like #####
Apart from this , i want in the future , when i expand column A and B , when values are entered there, i want column C to be created automatically.
We have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.
The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.
The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.
how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
In the attached excel file I have two macros that create hyperlinks:
1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).
2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.
In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.
In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?
If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro.
Shape color changes as the hyperlinks are selected from sheet1.....
I am working on a sheet which calculates payroll. I have a list of people in column A, and in column B, i have their different professionnal status. Their wages vary according to their professionnal status and are in another column C. How can i sum up the wages of only certain people, based on their common professionnal status in column B?
View 3 Replies View Relatedi have a problem adding cells every 5th cell for example i have
=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
1
1
1
1
1
1
1
but it does not work when it looks like this
1
1
text
1
1
1
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.
Sheet linked from external file, new data coming daily. How to copy Values of cells from B4 till B-empty to C column? The attached file has a properly displayed data.
View 3 Replies View Relatedis there any way i can take a 'snapshot' of a range of cell values and copy them into a specified range on the same sheet if F50=1? I dont want to just copy them as the cells update and change every minute and i just want the values when F50=1. I would like the same to apply to each open sheet on the workbook, as they all have F50 as a 'trigger' and the cells to be copied are the same range
I have a column of cells that the user enters values in ... say cells A1 through A10. Once all cells have values in them, I want cell A11 to display the sum. The formula I'm trying to use is:
{=IF(ISBLANK(A1:A10),"",Sum(A1:A10))}
But this is putting the sum in A11 as soon as any cell has a value in it.
Is there an easy way with a formula to require all ten cells to have values?
For simplicity sake if I wanted to paste the letter A in to a cell that already had the letter B in it to make the cell read AB how could I achieve this?
View 10 Replies View RelatedI'm trying to merge cells in a row (row 1, in the example below) that contain the same values. For example:
A A A B C C D D
into:
A B C D
I've used the following code, and it actually seems to work, but it gives me a runtime error and I can't figure out why.
VB:
Sub MergeCells()Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("A1", Cells("1", Columns.Count).End(xlToRight))
[Code]....
I have a column (i.e. A) with hundreds of numbers. I'd like to be able to write a number in a cell (i.e. B4), and have Excel find and tell the coordinates of those cells in column A that sum up to the result I wrote in B4. I also would like to be able to "hit a key" and see the next possible set of result-cells.
View 7 Replies View RelatedI need a formula that tells me if neither of two different cells does not contain a value that matches one of the values in a list (column) of cells.
Eg.
A1-A10 contains a list of values from 1-10
B1 & C1 contain imputed values, say 6 & 11 respectively
Required:
A formula in D1 that tells me if neither of the values in B1 & C1 correspond with the values listed in A1-A10. If one or both of the cells in B1 and C1 do contain a corresponding value (as in the above example) D1 should indicate "Ok". If neither correspond, (eg. B1= 12 & C1=15) then D1 should indicate with the word "MATCH".
I currently have a cell with a formula like this: =C5*B4 + F5*E4 + I5*H4 + L5*K4
ie the columns in each couple increase by 3. I now want to add more source cells say like this: =C5*B4 + F5*E4 + I5*H4 + L5*K4 + O5*N4 and possibly onwards. Is there a way of doing this in some kind of shorthand? =LOOP(C4,stepH,stepV,noOfSteps) * LOOP(B4,stepH,stepV,noOfSteps) or something?
I have set up an excel program that performs several iterated functions and spits out an answer. Is there a way I can get excel to take this answer and put it in a separate cell? I want excel to do this every time I change an input variable to my equation so that I will end up with an output that is a list of solutions.
View 9 Replies View RelatedIn Excel I need a particular cell to always have a default value. Say Cell B1 always needs to be 22. If any values are given in Cell A1, then B1 should diaplay that value. And if anyone deletes the value in Cell B1 still by default it should show 22 as the value. Is it possible.
View 2 Replies View RelatedExcel 2003 > I am developing a comprehensive VBA function that needs to read the value of many cells in a worksheet. I don't want to Name all those cells because then I will need to pass too many variables to the function. I can use the following set of objects and properties ...
View 6 Replies View Related