Excel 2007 :: VBA Lookup Two Columns And Change Value In One Column?
Jan 17, 2013
macro for an Excel 2007 file. It has to be a macro. I have cells in two columns I need to look at. Column A will have a value of cat, dog, or mouse. Column B will have a date. If A2="mouse" AND B2<"1/1/2013" Then A2="" ElseIf A2=mouse" AND B2>="1/1/2013" Then A2="mouse". I don't need anything to happen if A2 is equal to cat or dog. The macro has to move down the entire A and B column: A3/B3, A4/B4, and so on until the end of the columns. Hope this makes sense. I've tried a few macro samples I found online but they don't quite fit what I need done, or I don't know how to modify it.
View 2 Replies
ADVERTISEMENT
Feb 19, 2014
I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm
View 14 Replies
View Related
Jun 13, 2013
I have the below data. I'm trying to create a lookup that would return the Month and Year in another cell. For example, I have in another cell the min of all the data, I want to get the month/year in the cell below it with lookups. I've tried INDEX/MATCH, but not getting the results I want.
Excel 2007ABCDEFGHIJKLMN3JanFebMarAprMayJunJulAugSepOctNovDecYTD420134.47 (0.35)0.86 4.98 520121.27 (2.29)(0.27)1.55 4.61 (2.33)2.07 1.38 0.62 (0.60)2.50 0.12 8.63 620111.41 3.69 3.97 2.49 2.06 3.14 (2.97)5.83 (1.26)(1.30)0.34 (0.36)17.04 720103.34 2.24 1.59 (0.33)7.70 0.63 (0.26)2.00 2.78 2.81 3.01 7.96 33.47 820098.51 3.12 (1.95)(7.30)7.91 8.94 2.90 0.41 0.66 0.67 1.39 3.10 28.36 920084.09 1.60 6.83 8.65 4.42 6.79 11.01 8.16 1.03 18.41 17.74 7.01 95.74 1020072.08 (0.62)6.02 (0.30)4.74 (2.28)4.04 (1.12)1.29 0.89 11.58 1.79 28.11 1120064.87 (1.83)4.56 5.04 2.19 0.53 3.55 6.00 (0.68)2.76 2.82 4.74 34.55 1220052.43 3.17 1.71 4.44 5.78 5.84 (2.61)5.21 0.04 6.34 1.87 5.76 39.98 132004(0.22)4.98 2.86 (0.52)4.80 3.22 3.45 1.97 2.77 2.61 25.92 Portfolio Summary
View 9 Replies
View Related
Apr 15, 2014
I'm working on a decision matrix to evaluate different alternative production methods. Before that criteria have to be choosen and weighted against each other. The user would fill out the yellow/ blue cells with (0=less important; 1=equally; 2=more). Then values are added up and put into some kind of ranking.
The example was done by hand, which costs a lot of concentration. I would now like to extend the matrix - do you know how to fill out the grey cells more efficiently?
(Excel 2007, Win7)
View 1 Replies
View Related
Oct 22, 2013
I am trying to reformat the attached doc into one single column, It's a race listing of overseas marathons and I need it to look like this
Event Name
Event Date
City
Country
Entry fee
Website
in date order, one event after the other in a single column so I can flow it into a word doc ready to be designed. I need to apply in Excel (I am using 2007) to get this doc into the required format?!!
View 5 Replies
View Related
Jun 6, 2014
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
View 4 Replies
View Related
Oct 12, 2011
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
View 2 Replies
View Related
Jun 30, 2014
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
View 5 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
May 4, 2013
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
View 9 Replies
View Related
Jul 31, 2014
I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:
1st sheet:
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
I have 180 rows of data like this one in the first sheet
Second sheet(named sheet 1) is not used for this
Third sheet(named sheet 2):
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.
This formula should be in the column following "majoration".
I am using excel 2007 and windows 7.
View 3 Replies
View Related
Jan 31, 2013
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
View 2 Replies
View Related
Aug 9, 2014
User of Excel in Office 2007. However, I for statistics about how my Bitcoin Device's works and decrypts Bitcoins per day for trends and statistics, and so on
Now while I was away so had the power gone, and for almost 24 hours so stood all still, I would now like to outline in red the date / dates where I have zero or very low running time for specific date, but I can only change Fragen for all dates, not individual dates.
The stack's not exist in the data value is equal to zero (null) where by I want to be able to get the date in red color, and possibly also in bold. see print screen below.
[URL] .....
View 2 Replies
View Related
Jan 21, 2011
I am trying to find a way in Excel 2007 to convert the date from US format to UK format, I tried to change it by using [right click=>Format cell=>Select Date=>Select the first option in Type:*14-03-01=>OK], but I doesn't work , the attached file are presenting the date in US format.
View 10 Replies
View Related
Mar 3, 2011
If I want to format a cell I go to that menu. Choose formatting number.
The default value will appear with 2 decimal digit.
I want to change it to 3. How can I do so?
Is it possible to change the DEFAULT values that Excel 2007 offers?
View 3 Replies
View Related
Oct 9, 2011
Is there any way to change the settings in excel 2007, to give me the original color palate from 2003? I want to use the old colors because I don't like the new ones.
View 2 Replies
View Related
Mar 19, 2014
Using excel 2007. In a A table of marks obtained by a student. I want to get the content of the cell be displayed in a bigger font and in a particular color when i point to it. When I point to a cell content the display should be in a color of my choice say green and the font be greater than 20 pixels.
View 1 Replies
View Related
May 1, 2014
In Excel 2007 I know that if you have a cell containing text that you can change the color of parts of that text like This is the text in my cell and this is red text
But I have a cell with a formula that adds text to the end based on a conditional. I can't see any way to change the text color in this situation. I basically want this formula:
=D_Notes & TEXT(CHAR(10),) & IF(Terms = "Budgetary Estimate Only", "Quote is budgetary only and is subject to change pending final design.","")
View 3 Replies
View Related
Aug 26, 2010
Here is my situation:
I created one worksheet called: Assumptions. In this worksheet I have 6 different columns representing 6 different cases.
I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B columns for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.
Any quick way to do the changes?
My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.
I was thinking there was a way to highlight ALL the reference cells used in my Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.
View 12 Replies
View Related
Sep 7, 2010
I've just switched to 2007 and can't seem to find where they've moved the ability to change the width of bars in a chart. Where is it in 2007?
View 7 Replies
View Related
Apr 11, 2012
Someone Im working with says she used to change Excel to work on it on some vertical monitor. Is there a way to do that in Excel 2010 or even 2007?
Obviously I'm not talking about print layout, but viewing the whole application in portrait view, as if she turned a monitor sideways and altered the Aps view.
View 6 Replies
View Related
Jun 22, 2012
Ii have a UserForm with a ComboBox in it. the selections in the box are
0
5
10
15
20
25
30
I have 7 shapes in Worksheets("Interface") and renamed each shape caution1 through caution7
What i need is when i click on continue in my userform each shape will change according to the selection.
if 5 is selected all caution shapes will read, Caution flag is out 5 minute break
if 10 is selected all caution shapes will read, Caution flag is out 10 minute break
if 15 is selected all caution shapes will read, Caution flag is out 15 minute break
and so on
excel 2007 btw
View 3 Replies
View Related
Jul 9, 2012
I wish to monitor a change in a certain columns at some worksheets. I have - after having consulted John Walkenbach' fantastic book Excel 2007 Power programming with VBA - understood how I do to monitor a change in specific worksheet, by using the Intersect-function in the change-event on that particular worksheet. That works fine.
My situation is that I have an application that I start out with a certain number of workheets in a workbook, and in the course of the work that the application does, it now and then adds worksheets, and these worksheets I would like to monitor for change in a special column. I know, by looking at the names of the worksheets which ones of those in the collection that I want to check.
I therefore used, not the Worksheet_Change event but the Workbook_SheetChange. It doesn't work quite well. I guess my question for now would be:
The call to this event looks like this:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
1) How - in what way - should I use the first argument, the "Sh as Object"? Can I/Should I give it a name?
The error message I get when I use an instruction like this:
If Not Intersect(Target, Range(M:M)) Is Nothing Then
'Do Stuff
end if
The method 'Intersect' in the object '_Global' failed
(M is the column that I want to check)
View 4 Replies
View Related
Oct 7, 2013
I enter a date and time in a cell, e.g. "2013-10-07 12:30", and then I save the workbook.
When I open the Workbook again, the cell contents is "2013-10-07 00:00".
I am currently using Excel 2007. I haven't tested this explicitly with Excel 2013 which is my normal working version, but I think I should have noticed it if it had happened in 2013.
View 1 Replies
View Related
Dec 1, 2013
I have a macro that imports text file and puts a comma after each number (alphanumeric also). I have tried to change it to put single quote around the number and comm afterwards.
Text file listing
123
456
789
When I run my marco I get the following
123, 456, 789
which is what I want...
Now I want the same text to have single quotes around the data example:
'123', '456', '789'
All that I have tried to adjust keeps missing single quote at the front end and also missing the single quote at the rear end example: 123', '456', '789
Here is the vba I'm running
Sub GetserialNumbers()
Dim FileNum As Long, PathAndFileName As String, TextFromFile As String
Const Delimiter As String = ", "
[Code].....
Whe I run this I have to select the text file to get. I keep it in my documents as a quick access. I'm using MS VBA 6.5 Excel 2007 Windows 7 Enterprise.
View 2 Replies
View Related
Jun 22, 2014
I have a SS of a WW1 casualty list and one of the columns is Dates of Birth but the data has been entered in three different formats eg 21/2/1898 or 21.2.1898 or 21 Feb 1898. I have tried to Format Cells and change the date format to the slash but after highlighting a number of fields and right clicking they will not re format. Though i use Excel quite a bit i am a general user and dont understand it in depth.
article using =DATEVALUE(
am using Office 2007.
View 4 Replies
View Related
Feb 11, 2012
In an effort to manipulate the xml documents within an xls, you used to be able to change the extension to .zip then open. This no longer works for me, just getting the 'cannot open file: it does not appear to be a valid archive.
I have tried it with a number of different files, but no joy.
It still works with a .doc so I guess it's not that Microsoft have stopped this capability?
Using Excel 2007
View 2 Replies
View Related
Jul 11, 2012
I am using excel 2007 and when I change cells my formulas do not automatically refresh. I have set it to automatic and the calculations will not refresh. How I can force a calculation. I did the control alt f9 and nothing seems to work. Any macro that can force all calculations.
View 4 Replies
View Related
Oct 3, 2011
Excel 2007 - Sorting across columns
Need to sort part of an Excel worksheet across multiple columns.
For example data is:
ABCDEF1
Machine
4394264394294394434399834399842
Model
TD860TD400TD860TD620TD6203
Pgm
OPTABCOPTEP2EP24
Start
5/31/119/1/115/1/096/15/092/1/085
End
2/28/158/31/125/31/114/30/108/31/09
I need to sort it first by Pgm, within Pgm by Model, and within Model by Machine. I do not need to sort on Start or End
The results should look like:
ABCDEF1
Machine
4394294399834399844394264394432
Model
TD400TD620TD700TD860TD8603
Pgm
ABCEP2EP2OPTOPT4
Start
9/1/116/15/092/1/085/31/115/1/095
End
8/31/124/30/108/31/092/28/155/31/11
After selecting the data to be sorted (columns B,C,D,E,F), I tried using "Sort Left to Right" under DATA-SORT-OPTIONS but it doesn't seem to work.
View 3 Replies
View Related
Jul 29, 2013
how to add many columns to the rightmost part of the worksheet carrying on from Col AA?
I keep getting error 'Cannot shift objects off sheet.' and followed instructions to change a setting to All or use control-6 but this made no difference.
View 7 Replies
View Related
