Is it possible to get multiple cells with the criteria? it always happen to only show the first cell in a column that have that criteria. For example, I have a column1 named "projects" and a columnb named "employees". How do I get all the names that have the same projects?
View 2 Replies
How to combine cells with a similar value inside them into a single cell. Here is what my data looks like.
10'X14'{&}SNM8981-1014{&}16
2'6"X10'{&}SNM8981-2610{&}9
4'X6'{&}SNM8981-46{&}6
[Code]....
For example, I need to have every cell with SNM9012 to combine into the same cell. If I could figure this out it would save me hours vs pasting the similar lines into textmate and removing the enters.
I have attached the file, in the last sheet (Consolidated) i need to get the data from all the previous sheets, my problem is that whenever i have similar cells the data that is showing is only the first value and not the sum of all values,
View 1 Replies View RelatedI am trying to merge two cells that have the exact same content in each cell. Everytime I do this, I get a pop-up warning that Im trying to merge cells with content in them and that only the upper-left content will remain. This is exactly what I want, without that pop-up, as I have to do this for multiple columns at a time.
View 2 Replies View RelatedI am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies View RelatedI, daily get a list of individuals with some data against each one of them. E.g Amit would appear 7 times in the list, John would appear 10 times in the list and so on and so forth. I am required to sort the data as per names and then fill one background color. One color for one person so that it becomes easy to read data specific to an individual.Ia there a way that the color fills up automatically. note the names and number of entries are not same everyday.
View 8 Replies View RelatedI have two sheets, one is "Summary" sheet and the other is "Sheet1". Both sheets has the same template. The "Summary" has color bordered cells and the "Sheet1" has no color. Whenever new color had been added to "Summary" sheet, I would like color from "Summary" sheet to be copied to "Sheet1" in same locations like if red been added to D12 cell in "Summary" sheet, D12 in "Sheet1" will automatically filled in red from "Summary" sheet. Similar to Lookup.
View 3 Replies View RelatedI have a Macro 'Timenow' to give current time in a Cell of MS Excel, but then it changes all Cell values of sheet where the macro was used.
Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub
Even assigning value to variable does not work:
Sub Timenow()
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
Dim TN As String
TN = Format("=now()", "h:mm")
ActiveCell.Value = TN
End Sub
how to restrict the macro to change the value of current/active cell only without effecting other cell values?
I need to check a range of cells (B4:B35) and see if any of the contents are less than a specified cell (M1) and then show a message, (the message part I can do). I have tried using For Each but I then get the message for every cell that is below the specified cell (which in theory could be all of them). I have also tried using an If Any statement but didnt work.
View 5 Replies View RelatedHere is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
As you can see in the example below, in column B I have a list of vendor names, some of which are similar but not identical. (For Example, in one instance a vendor will be called "Ford Motor Co.", while in another it will be called "Ford Motor Inc.".
I need to populate column C, which at every instance where two plants (listed in column A) have similar vendor names in Column B, a universal name will be assigned and recorded in column C for each of the similar names.
HOpefully it is clear as shown below.......
As you can see in the example below, in column B I have a list of vendor names, some of which are similar but not identical. (For Example, in one instance a vendor will be called "Ford Motor Co.", while in another it will be called "Ford Motor Inc.".
I need to populate column C, which at every instance where two plants (listed in column A) have similar vendor names in Column B, a universal name will be assigned and recorded in column C for each of the similar names.
HOpefully it is clear as shown below.......
I have created a sheet that contains a new diet program, calculated down to the precise calorie required for my training routine. Please see below for an example of one of my daily meals:
[url]
I need a formula that will help me to create a weekly shopping list (as the values in the example above will change on a regular basis). So I need to take all values from the from columns A & B, multiply them by 5 and then show me the totals in another sheet.
Similarly, I need to do the same with columns E & F, only they need to be multiplied by 2. The totals then need to be added to give me the required amount (in grams) for the week.
=ISNUMBER(MATCH(X1,A:A,0)) where X1 is cell you are checking for a match to check and see if there are duplicates in 2 rows. Is there anyway to check to see if a cell contains a string of numbers. Example: Cell A1 has 0000402502LK and Cell A2 has 402502. Is there anyway to get this to show up as true since the 402502 in contained in the string in A1?
View 2 Replies View RelatedI want to delete rows that are a subset of any other row. Not manually, as there are thousands of rows. For example, in the attached file, row 4 is a subset of row 5 (also row 12 of row 13, row 14 of row 15, and both rows 21 and 22 of row 23).
View 2 Replies View RelatedI have a large spreadsheet where some data are similar. for Example
Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000
I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.
I have the following seven codes used to fill a ListBox with data retrieved form a range (DACNRange).. they are almost the same .. the only difference between them is the range column number (highlighted with red). Instead of these seven similar codes, Can we create a general code replaces them all?
View 2 Replies View RelatedI need to return the text from a string, in a similar way to they way LEFT/ RIGHT would do.
I have some text in a cell, say A1 that looks like this ->
TEXT BEGIN --- TEXT NOW ENDS
I want to return everything before / after the --- symbols.
When you filter a spreadsheet, you can find results that are similar to a condition. I have families of products that share a common 3 digits, but the rest of the model number is different. The 3 similar digits could be the first 3 digits, or it could be 3 digits starting with the 2nd digit. This spreadsheet has each complete model number on its own row. In that row is a number that represents a volume. I want to be able to sum the volume column if the model number contains the 3 digits.Model Number
Volume
A hat
5
B hat
1
A glove
4
hat
8
In the above example, the total for "hat" = 5 + 1 + 8 = 14
I would type in a condition (family name) into another cell (D1) and the formula in D2.
I have an excel grid which have 2 sheets, sheet2 have all the data, I am doing a macro which based on a value that I put in sheet1 is supposed to find Items which have similar value +/-3 from sheet1. I made a code which I posted below, I don't have any error message but it doesn't work as it should. I explain you with an example: If in the data grid I have the following values
11.8
67.0
25.5
18.1
13.0
if the value I look for is 12 +/-3, it doesn't find anything.
if the value I look for is 11 +/-3, it finds 11.8 but not 13,
if the value I look for is 20 +/-3, it finds 18.1,
if the value I look for is 23 +/-3, it finds 25.5,
Here is my code:
Sheet1.Cells(1, 3).Select
Dim Amin As String, Amax As String ', Bmin As String, Bmax As String
Amin = Sheet1.Cells(3, 2).Value - 3
[Code].....
Problem is Findstring finds cells that contain "SS1" AND "SS1 EXT". These are separate grades.
I need Findstring to only find SS1. Using xlWhole doesn't work for me, as the cells also contain other data. For example: " SS1 48 /"
CODE:
FindString = "SS1"
Findstring1 = "SS1 EXT"
With Sheets("Sheet1").Range(Rng4)
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
[Code] ........
Cell A1 = 78-36
This is a win/loss ratio so to have it as a percentage I would have to divide the first number by the sum of the two numbers so 78/114 = 68.4% How would you do this with a formula?
I came up with this that isn't quite right, because RIGHT and FIND don't work together.
=LEFT(A1,FIND("-",A1,1)-1)/(LEFT(A1,FIND("-",A1,1)-1)+RIGHT(A1,FIND("-",A1,1)-1))
There must be a smarter way of doing this. TY
This is probably a simple question, but one I can't figure out after several hours. I am creating a spreadsheet focused on certifications.
I need to take the date each initial certification was achieved, and add x years to each cert, which is simple enough.
The sticky part is that the (date + x years) can't logically be a weekend; if it is, I need to subtract one or two days from the result, so that it's the *weekday* immediately prior.
e.g. if my initial certification date is: 1/23/2006 and my certifcation expires in four 4 years:
A47= date(2006,1,23)
h47= 10
=DATE(YEAR(A47)+H47,MONTH(A47),DAY(A47))
Result = Saturday, January 23, 2010
If the result is a Saturday or Sunday, I need to subtract one or two days from the date. In this example, I'd like: Friday, January 22, 2010 as the result.
I've been playing with 'if', et. al., to no avail.
I am after a control similar to the Properties one in the VBE. The one where you modify properties for a control in design mode.
Does anybody know of a suitable Control that I can use in my project that has similar usage?
I have a spreadsheet that has descrptions in column D. Where the descriptions are the same ,
I need the VBA code that will do the following:
1) Sort the data by columns C (descending), G (ascending) & E (ascending)
2) Putting a border around all the data where the decriptions are the same
i.e from column A to Column I beginning from row 2
I have attached a sample file for ease of reference....
I use these and related functions (Format, Right$ etc) in my VBA code. Works perfectly on my machine, butwhen a colleague opens the sheet I get repeated compile errors - "Can't find project or library". I have checked all the references and tey are all there, so much scratching of head.
View 9 Replies View RelatedI'm trying to create a formula that would check if there are similar values in a row. How should I do this? There are 20 values in a row, and they should all be different (true). If similar values are found, it should give a warning or highlight the two or more cells.
Daily We have to search the similar numbers in a enormous database (around 35000 - 45000). Base will vary daily.
The Numbers in 2nd Row of 'wish sheet' has to be compared with 'base sheet' (B,G & L columns in Base sheeet) and the similar numbers has to be copied down from corresponding 3rd row. single character variants and double charcter varients need to be found. box is made to differentiate single and double variants. The test file may be useful to identify my problem.
It is very difficult to use find Numbers manualy. We have been helped by the experts on various circumstances in the past. So, We again seeking help from our senior brothers here.
I have a list box contains of customer name and I used that to set up formula based on your selection in the list box. However, I wonder if there is a way that can select multiple customers in the list box and it will add up all that customers from your multiple selection like in the pivot slicer. I don't know if we have any formula that can do that complicated thing.
View 2 Replies View RelatedThis particular formula is used several times to gather various data points across 3 different sheets named Month-1, Month-2 and Month-3.I gather the data the following 3 formulas then sum it as needed with a helper cell.
=IFERROR(INDEX('Month-1'!$A$1:$G$6000,MATCH(TRUE,INDEX(1/('Month-1'!$A$1:$A$6000=$G$24)*ROW('Month-1'!$A$1:$A$6000)-MATCH($A96,'Month-1'!$B$1:$B$6000,0)>0,),0),MATCH($M$2,'Month-1'!$A$14:$G$14,0)),"")
[code]....
