Getting Different Result According To Contents Of Columns
Jul 3, 2012
I am trying to get result in a column based on contents of previous 4 columns. For example as per table below, if there is value 1 in either of first 4 columns of same row, I want to display A in column 5. As first 4 columns in row 2 to 4 contains 1, result is A. If there is any 2 in first 4 columns, I want to display B. If there are both 1 and 2 in any first 4 columns, I want to display C. If there is 3 in any of first 4 columns, I want to display D. And if all 1, 2 and 3 are in any of first 4 columns, I want to display E.
I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F
For example:
Column A Column B Column C Column D Column E Column F Column G (Results) Royal Mail 1 100 2.8 2.3 1.2 Royal Mail 2 100 2.9 2.4 1.3 Royal Mail 3 100 3.0 2.5 1.4 DPD 5 200 4.5 2.8 1.5 DPOST 1 100 1.2 3.2 1.7
I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]
Here are an example of what I have tried, amongst many...
=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I need to compare two columns A and B with a result in column E. BUT I don't want the result to be added together. ( since it's receipt numbers ) SO my formula is incorrect I need a formula that will show the receipt numbers next to each other divided by a comma or forward slash...
-Every time a cell in Column D contains New or Old, “Customer” is entered into Column G in the same row. -Every time a cell in Column D contains Old2, “Assets” is entered into Column G in the same row. -Every time a cell in Column D contains New2, “ShortTerm” is entered into Column G in the same row. -Every time a cell in Column D contains Old3, “LongTerm” is entered into Column G in the same row.
I have one column which contains suppose first names & i have other two column which contains first name & last name in same sheet but like they may be having in g & h column.
so i want to exact last name of user form that column(g & h) to my first name column(a).
How can I sort two columns of function results using the result value? Excel is trying to sort on the function in the cells rather than the results themselves.
If I have the following: =SUM(E2:F2) - G2). So lets say that E2 has 50, F2 has 60 which = 110. Now I want to take that 110 and subtract it from what is in G2 and put the result in H2.
Date formatting. I have two columns first column is for the date (3/6/2013) and the second column is for the time (12:37:16 AM). I would like to combine the content of both columns to be like (2/11/2013 12:35:00 AM) .
I am using concatenate but I don't have any luck. How to combine the content of my two columns.
I have a large number of Excel files with unique filenames that contain information I would like to add as columns to the actual spread sheet.
SPY_P_20131025_172.00_XX
I am trying to in break apart the above format with each of the above underscore separated fields being placed in there own column. How I would accomplish something like this?
...And if I wanted to perform this function on a large batch of files is there a tool I would need to automate the process?
I export data from another programme into excel and I wish to write a macro to copy the contents of columns G, H, I and J and paste it into columns A, B, C, D but starting at row 4. I then carry out various formatting tasks. I have written a macro that works for the copy and paste and the formatting but it only works when the data is the same length. The length of the data exported varies each time and so I am looking for some code that stops at a blank cell and copies the range above. I believe this uses xlup or xldown.
I've been trying to loop data for column A from row 1 until 6 and print the result on specified cell. But the problem is, it keeps looping until column B,C and D.
I want to calculate average result separately for column B,C, and D without taking the previous data.
Here is the code that I've done.
VB: Sub Avg() col = 1 Row = 1 Do Name = ThisWorkbook.Sheets("Sheet2").Cells(Row, col).Value If Name <> "#" Then
[Code] ....
I attached my excel. The green box indicate the result that I want : function.xls
Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
I have a worksheet in which column A:C contains slno,name etc. Col D contains numerical data.D4:D5 is col heading. D6:D41 contains numerical data. I want to copy the data from D6:D37 only. for this i tried the following but with negative result.
1.select cell D5. 2.press F5 and enter -to goto cell D37. 3. press control and shift and up arrow to select the upper cells. Excel selects from D37 to D4 whereas i want it to be from D37:D6.selecting D6 and then press control shift and enter keys takes me to the last cell with the data in the column, ie,D41.
How can I select only the required cells so that i can copy the content.selecting the first cell and then dragging the cross is not an expected answer to solve this.
Below is my initial code that can't get past the first line.
if right(cells(2,i),4) = "D_MA" then ..... ?
I'm trying to clear the contents of all data in each column that has the text conaining "D_MA" in that columns row 2. For instance, if cell F2 has 30D_MA, I want the macro to clear all contents in Column "F" -- but to do this for all columns that contain "D_MA" in row 2.
Was looking into this, but basically i have a repetitive task of copying contents from various columns, and pasting them into 1 column.
1 right after the other. I figured there could be a way for a macro to copy contents from multiple columns and paste them into 1 column.
I have including 2 files, 1 from the start of the file, and 1 how it should be so it will have clarify what i am trying to do.
So basically if you open book1 , i have factory install tabs,
I need the columns contents from the first tab the (5k-20k) starting with Column B, then E, then H, and so on( Every 3rd column starting with B ) to be copied and pasted in Column A of the (Factory Install Tab) ( last tab ).
If you open the 2nd file this will be clearly understood. In the second file in the factory install tab you will notice i did the first 2. I highlighted each starting part in yellow just so its easier to see. SO in column A you should have all the contents in the 5k-20k tab ( starting with b every 3rd ), pasted in column A 1 after the other.
The next tab is the 30k tab, i need all of the columns contents copied starting with B ( every 3rd again ), copied in Column C of the Factory Install tab. Basically every tab's contents needs to have its own column in the Factory Install tab.
If anyone could help that would be great, this is a very time consuming process manually, and it would seem like a macro could be made to easily copy contents.
I frequently review DAT files. These DAT files use a variety of characters as delimiters. I'm trying to devise a macro that will perform a text-to-column operation, using the content of a specified cell as the delimiter.
Here's my code, which uses - (ASCII 0254) as a delimiter:
Code: With Sheet1 Range("A1", ActiveCell.End(xlDown)).Select Do
[Code]....
Instead of having the delimiter hard-coded, I'd like my macro to use whatever character I enter into Sheet3.Range("i5") as a delimiter. That way, I can easily change my delimiter character as necessary.
I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.
As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.
When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet. -----
WHAT I NEED TO DO: Select some cells (must be in the same row - that we have just pasted in). Press a button on the Worksheet labelled "Auto Assign Desk", which does this:- For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.) -----
This will assign the employee to the desk they were sat at last, when they are in the office next week.
Sheet2: col A = contains the style# col B = contains the color of the style col C = contains the size of the style col D = contains the qty of the style,color, size
Sheet1:
I would like to do the following:
A1 = input the style # B1 = input the color of that style C1 = input the size of that style
then D1 should automatically contain the qty of the mentioned style, color, and size.
I'm trying to pull thesum of huge number of columns in this case the range of pulling the data is B2:E7 based on two criteria (B8 and B9) The result is on B11
I have two sheets: - sheet 1 ('current') - sheet 2 ('31May')
The data in column C is the same, although sorted differently and can't be re-sorted to be the same. Where the data in column C is the same in both sheets, I want to fill columns A and B into sheet 1.
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column: 000122042009ABCDEFG00567
Required result: 0001|22042009|ABCDEFG|00567
when running below macro; result shows: 1|22042009|ABCDEFG|567 (Beginning zeros figures of the first and last column disappear)
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 With Range("E" & intLstRow) If .Value > .Offset(0, 1).Value Then .EntireRow.Delete End With Next intLstRow For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
