I got four product name in column & want to bring other product name equal to same same product in different colum
like if products name in column range is PRV IT should bring birkett in other colum
PRV SHOULD BE IN NEXT COLUMN AS BIRKETT
TANK " " "" WAM
INSTRUMENT " " " " AGI
HVC " " " BAILEY
and rest of should be Marston
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID VOL
001 01
001 05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique) VOL (concatenated)
001 V01, V03, V05
002 V01, V04
003 V06
PS: I have people using this table with office 2003, so compatibility is necessary...
In range C15:C45 I have names of some steps. Starting from column D, row 6 have a drop down with 6 possibilities. Now based on what is selected in row 6 (so cell D6, E6, F6 and so on), I would like to color certain cells from 15 to 45 in that particular column (certain steps that are applicable to option selected in row 6 of that column). And as month goes by, users do that in more and more columns.
Also, steps overlap between conditions that are selected in row 6 and I would like this to macro (or formulae) to be running so that as soon as user selects the condition cells are colored and then they can fill out only those cells. So, I couldn't figure out any way to do this by conditional formating as there are more than three conditions and range is not continuous.see attached file as an example of final output.
I am trying to sum based on a set of conditions including time. The formula works fine except when time is between 11.30 pm to 12.00 am.I have attached the sample which will make the scenario clear. I am not sure how to modify the formula to ensure it works when the time is between 11.30 pm to 12.00 am.
I am trying to write a formula, that looks at row A1:A6 (Country), and equals A11 (Country chosen), but also looks at Columns C:E (Month), and equals B10 (Month Chosen). I can then add the figures in C3:E10. However I want a YTD idea, and so ADD Month 1 through to Month chosen in cell (B10) for the relevan contry chosen (A11). Uptil now I have used SUMPRODUCT, but am only able to sum one column, and not the required numbe rof columns.
I try to add some conditions to get specific text. i.e.
if cell b4 <= 20% then cell f4=01.30min if cell b4 >= 21% and <=50% then cell f4=02.00 hr if cell b4 >= 51% and <=70% then cell f4=02.30min if cell b4 >= 71% then cell f4=03.00 hr
I tried different ways, including select case but unsuccessful.
I have a macro that is supposed to type a text in column 8 if condition is met, on all sheets but one (" Total"). When I run it, it stops at "Next Line" with a "Compile error: Next without For". For the life of me, I can't figure out where I'm missing a "For".
Sub ForceMatch() Dim ws As Worksheet Dim line As Integer endline = Range("A1000").End(xlUp).Row
For Each ws In Worksheets If ws.Name <> "Total" Then With ws For line = 3 To endline If (Cells(line, 9).Value <= 5000 And Cells(line, 8).Value = "") Then With Cells(line, 8).Value = "FORCE-MATCH CANDIDATE < $5K ICDP" Next line End With End If Next ws End Sub
I've been trying to figure out how to do a vlookup using two conditions. Reading some other posts, I saw this could be done by combining a MATCH function but I'm still having trouble. Here is what I'm trying to do: I am trying to match cash amounts based on two look up values....one being the identifier (cusip) and one being the pmt code. http://i105.photobucket.com/albums/m204/RAK_08/data.jpg
This data is formatted exactly the same but comes from two different sources. I need to match the pmt amounts for a cash reconciliation.
You all were so great last week (thanks Ninja). This is a tracking form for a contest based on restaurant cost results (Page attached). I need a formula for if C2>D2, but less than 1/2%, give100, if C2<D2, give 200.
I have a worksheet where there are rows of information in a specific range (A5:G600). I'm using the conditional format of =AND(MOD(ROW(),2), COUNTA($A5:$G500)) to alternate the row colors. I would also need to have the information in column A formatted. Column A contains dates, and if the date is 90 days less than or equal to today's date (90 days or more old) I would need to format that also.
I've tried using both at the same time, but the conditional formats will override each other when applicable. Am I able to do this via conditional formats, or would I need to do one or the other in VBA?
I am trying to use an OR condition within a sumproduct formula. Why am I not getting correct results?
(see attached) I want to sum all costs for each of my PO #s: If the Rebate Type is IPT, Wireless, or Security, then the result goes in the "Fndtn Ext Cost" column If the Rebate Type is IPT Advanced, Wireless Advanced, or Security Advanced, then the result goes in the "Adv Ext Cost" column.
But for some reason, even though the OR statement is evaluating to FALSE (I tested it by itself), it's still summing ALL the extended costs for that PO #.
How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).
There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.
if B2 value (red column) of datahave tab is equal to B2 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to A. if B2 value (red column) of datahave tab is equal to B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to B. if B2 value (red column) of datahave tab is not equal either B2 and B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to H.
[code]....
i tried normal Hlookup but i do not know how combine multiple IFs to solve my problem.
Is there a way to filter data based on multiple OR condition with a like parameter.
I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".
I am trying to round a number to next multiple of 10 with condition i.e. for example if number is 1230.56 then it should round off to 1230 but if the unit number is more than zero i.e. 1231.56 then it should round off to 1240.
That means the main number should round off to next multiple of 10 only if unit number is equal to or more than 1.
Example 120.11 should round off to 120 121 to 130 120.99 to 120 119 to 120 and so on
I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:
A B C D 1 jan 2, 2008 brake front 200 ? 2 jan 20 , 2008 clutch 150 3 jan 30, 2008 wheel front left 300 4 feb 2, 2008 brake front 50 5 feb 5, 2008 brake left 85 6 feb 15, 2008 clutch 300
the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.
we can get if functions to return conditions, as in if so and so "a" etc... is it possible to return long statements, instead of letters, with fill stops and commas. i don't know if it would be appropriate if i put an example of what i was trying to do. i am not trying to be rude but this
will this work in c3 =if(b3=5,"Readily understands classroom statements, questions and instructions.Can usually understand short electronic texts without difficulty.",if(b3=4,"Understands most classroom statements, questions, instructions.Can usually understand short electronic texts with only occasional difficulty.",if(b3=3,"Can generally understand classroom statements, questions and instructions, but may need repetition.Can understand short electronic texts reasonably well.",if(b3=2,"Sometimes misunderstands classroom statements, questions, instructions.Has some difficulty in understanding short electronic texts.",if(b3=1,"Has noticeable difficulty in understanding classroom statements, questions, instructions.Has great difficulty in understanding short electronic texts.")))))
What I need it for is an automated report that should be used for several different projects. Each projekt have events on different dates, and I only want the report to show a specific date if there is some text (i.e. event) on that date. As it is now, it's a looong report with several blanks with only a date showing. So it should insert a row (the tricky part) + the date + the text if condition is met, and do nothing if they are not. The script should add and remove rows and text depending on what project the information is taken from.
The data it should grab is in this form (many more data inputs though..): Date Date Date TEXT
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
My data set has a number of duplicate entries. But I would like to sort them out based on some conditions. Say for example my C2 code appears three time in the dataset. I would like to sort this multiple code using the time and i column.
Say for example, 871514 code appears three times in this dataset, and if this code appear within three years - time with a different (i column text e.g. public and private), I would like to exclude these three duplicate entries from my dataset. If this code appears within next three years from the date, but have same I column text (e.g.public versus public), I will keep them.
So each code will be considered based on three years of time and type of text in I column. If the code appears again later after three years, I will keep them.
So the codes need to be checked within three years time with i column text.
My dataset identifies the difference of dates between last entry and next entry.
I have 4 conditions in one formula, and only the first condition (when met) evaluates correctly. When the other conditions are met, I get a VALUE# error.
I want to concatenate 2 cells, based on whether there is an "a", "b", "c", or "d" in a particular cell. see example. Below is my formula:
I am having trouble evaluating two conditions with nested logical operators to produce the output I need.
For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).
Breaking it down, [Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes") [Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")
[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")
[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")
What I need is the equivalent of:
if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")
I am having problems with choosing multiple values for the second condition in the b range. The formula works for one account in the array, but not multiples.
I Want To Use User Form to add data with multiple condition........
1st condition : select name, AHSAAN G, ALI G, SHAHID G....... 2nd condition : select , ONFLOOR BC, ON FLOOR VC, ON LINE VC 3rd condiotn : select, PT P2 P3 PB HR LK
In this use form the i used define name "name_1"
1 = I Added this to combobox 1. if if a aded a new name who is not in the list "name_1" then he asked for added this name or name. if i click yes then added the name in "name_1" list.
2 = When i added the data to my sheet by default all field of UserForm don't blank....
I'm trying to sum across multiple worksheets based on a simple condition. I have a time sheet with a worksheet for each week (52 worksheets). I've gone back and added a column to code the type of work done, types 1 - 17. I want to sum the hours of each type across all worksheets. This is what I have that doesn't work:
Here is the problem: i have two conditions to satisfy first is data from column A and second is data from column C, what i wanted to do is if both data from column A and B has the same other data in column A and C then sum the total in column D, F, I retain data for column B, E, G and H. Then delete the duplicate rows. By the way we also need to replace the remove the text (W1,W2,W3,W4 and W5) in column C.
