Excel 2010 :: VLookup Based On Values From Two Columns?
Jun 4, 2012
I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.
Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas
Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas
View 5 Replies
ADVERTISEMENT
Apr 12, 2013
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
View 3 Replies
View Related
Jan 15, 2014
I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.
I have attached a sample workbook as a reference : Sheet1.xlsx
View 8 Replies
View Related
Oct 22, 2012
I have excel 2010....
I am having problems creating a chart off of the following data. Each of the cells which have a numerical value are v-lookuping from another sheet. It needs to remain this way as when I switch brands (through data validation), the numbers will update and the chart should as well. Right now, the chart shows these values as zero. I have done this a million times in excel 2007, but now with 2010, I keep having this problem
2010/Jul
2010/Aug
2010/Sep
[Code].....
View 2 Replies
View Related
Feb 18, 2014
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
View 3 Replies
View Related
Jun 24, 2012
Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.
OrderNumber
TaskName
SignOffDate
1
OED
01/05/2012
[Code] ........
What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:
OrderNumber
OED
CTN
FAD6
RFS
1
01/05/2012
17/05/2012
22/05/2012
02/06/2012
2
03/05/2012
19/05/2012
27/05/2012
02/06/2012
There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.
An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.
I'm using Excel 2010.
View 5 Replies
View Related
Feb 4, 2013
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
View 9 Replies
View Related
May 9, 2014
I need to highlight a column in a pivot based on the 3 rules below.
Highlight cell in column A if:
Cell in Column A contains numbers 4, 5, 6. or 6.5.
Cell in Column D contains a value
Cell in Column E contains a value
An example of how it should look (Excel 2010): MrExcelhelp1.xlsx
Formula to put in conditional formatting?
View 4 Replies
View Related
Feb 26, 2014
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies
View Related
Apr 6, 2014
I have a checkbox where the values are reflected as True/False in a seperate column, what i simply want to do is take the value from one cell, and add 10% of the value each time a value becomes True.
For example in this value column after the check box has been filled out it might look like this:
False
True
True
True
False
False
False
The cell then needs to take the figure, lets say, 100, add 10%, then add 10% to 110, then again and so on for however many "True" statements are in the column.
I have tried with no success with various SUM/SUMIF/COUNT/COUNTIF/IF etc cant seem to get it to work, im not sure the cell refreshes correctly after the checkbox is filled out and its not registering the new "True" value as by default its all set to "False".
View 7 Replies
View Related
Jun 17, 2014
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
or
A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
View 5 Replies
View Related
Mar 14, 2012
I'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is
=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
Sheet1ABCDEFGHIJK12ItemWidthHeightPrice
3mm6307809301080123019751821#N/A
49001581681791891992512001621741851952083615001661791912032167
18001701851972102228210017418920321623192400179193208224239102700
18520121823324911Excel 2010Worksheet FormulasCellFormulaK3=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
View 4 Replies
View Related
Mar 14, 2012
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
View 3 Replies
View Related
Jul 9, 2014
I have successfully populated a website forms using Excel VBA. As a continuation of this task, i need to calculate the total of a column based on two other columns in the same worksheet.
Column A contains Date, column B contains 3 fixed alphabets (I, L and K) and column C contains time. I have attached a sample sheet here. sample.xls
How can i write a VBA code to find the sum of effort values of column C for each values in column B for a given date (value in column A).
View 9 Replies
View Related
Jun 4, 2013
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
View 1 Replies
View Related
Aug 21, 2014
The for loop don't work in my excel 2010 macro. Probably the problem might be the Range("Fi") construct with the variable "i"?
Dim i As Integer
For i = 4 To 10004
Cells(i, 63).Select
Cells(i, 63) = Application.VLookup(Sheets(3).Range("Fi"), Sheets(4).Range("F:AY"), 45, False)
Next i
View 3 Replies
View Related
Dec 28, 2011
Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
View 3 Replies
View Related
Mar 10, 2012
Is is possible to construct a vlookup to identify that there are duplicate values in the Lookup_value and the Table_array and return the second and third values in the Formula result?
In the acutal project the Lookup_values and Table_arrays are in separate worksheets, but I think the example below represents the issue I'm working with.
I'm using Excel 2010
ABCDEFG1=SUM(IF($A$2:$A$8=A2,1,0))=VLOOKUP(F2,$A$2:$B$8,2,FALSE)
2JOHN13BILL12BILL11BOB14MARY11FRED15JOHN23JOHN16FRED11JOHN17BOB11JOHN18JOHN33MARY1
View 9 Replies
View Related
Feb 5, 2013
I have a table in columns A-D. I am trying to perform a vlookup to return the first non-blank in columns B-D based on the lookup value in column A. Columns F-I are what I am looking for the formula to do. I think I am close with the following array formula but not quite there.
Excel 2010LMN12#N/A3Sheet1Array FormulasCellFormulaM2{=INDEX($B$2:$D$9,MATCH($F$2,$A$2:$A$9,FALSE),
MATCH(TRUE,INDEX(INDEX($B$2:$D$9,MATCH($F$2,$A$2:$A$9,FALSE),0)<>"",),FALSE))}Entered with Ctrl+Shift+Enter.
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
Excel 2010ABCDEFGHIJKL1PFQPFQ212341234CRWEWS312343456
WETXDR41234CR5123461234WE712348123491234WS1034562)
What I want it to return, vlookup of first non-blank
113456WE123456DR133456143456TX153456163456173456183456191)
What table looks like20Sheet1
View 4 Replies
View Related
Sep 24, 2013
We have a business system which has some very disjointed reporting. To fix this I have multiple extracts that are then combined into a single report. I then add Year & Period from a lookup of a date in a particular field. Unfortunately most values are returned as #N/A but if I manually go into the sheet and select an offending cell and click then magically the formula then corrects itself.
So I thought I would be clever and use Sendkeys {F2} as part of my code. Unfortunately this is a bit hit & miss as it works on some but not on others. Also the Sendkeys piece repeats itself leaving my frontsheet a 1000 rows from the start. This is easily fixed by selecting cell A1as the last step.
My question is there methods in VBA by not using sendkeys that will achieve the same result and is consistent. I am using Excel 2010.
Code:
Sub Add_Calendar()
Dim cell As Object, c As Range
'setup for calendar data population
' Range("H1") = "Created Date"
Range("N1") = "Year"
Range("O1") = "Period"
'copy the format
[Code] ........
View 1 Replies
View Related
Nov 25, 2013
I have Excel 2010 & Windows 7
I am using the following formula to copy the name of the tab - =MID(CELL("filename",C26),FIND("]",CELL("filename",A1))+1,6)
I have a marco that creates a tab from a Master file, and I need to copy the name of the tab into cell A2, using the formula above. Then I use a vlookup on this cell, to get the data needed for about 10 other fields. I have these vlookups in the Master file. The vlookup doesn't work all the time. I get a #NA answer. Sometimes it does work. When I manually type in the project number on the tab, the vlookups work.
Sample of the vlookup: =VLOOKUP(A2,'AC-Program'!$A$1:$H$58600,2,0)
I was trying to find a way to use a value in cell A2 instead of the formula above, but I don't want to have to manually update it, I need it to be generated when the Marco generates the tabs.
Here is my macro:
Sub CreateSheetsFromAList()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")[code]......
Is there a way to add this function into this macro, and it will leave this field as a value instead of a formula?
View 2 Replies
View Related
Sep 15, 2013
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
View 7 Replies
View Related
Oct 5, 2011
There is a formula
=vlookup(a1,Named_Range,2,false)
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
View 1 Replies
View Related
Mar 2, 2012
I am using the this formula to look up data using 0 to show not found. VLOOKUP is acting case sensitive. The Master Array data is in caps and the input is in lower case. There are no spurious spaces. Here are the results:
Code Name
0 River Branch Foundation = not found
X RIVER BRANCH FOUNDATION = found - in array as caps
The formula:
=IF(ISNA(VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3053,2,FALSE)),0,VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3054,2,FALSE))
View 4 Replies
View Related
Mar 28, 2013
I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.
I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.
I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.
Right now, I have, on Source col L:
Code:
=MID(C2,1,9)
and in source M:
Code:
=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)
This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M
Then, in Log col R, I have an array formula:
Code:
=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)
This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)
Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.
View 2 Replies
View Related
Aug 11, 2014
I am creating a payroll spreadsheet that will look up tax deductions from a spreadsheet I pulled from the IRS website. The first two columns represent a range of values the employee's pay would fall between. The first column is "paid at least" and the second column is "but paid less than".
Once I locate which row the employee's wages fall between, I would then need to match their number of withholdings with the corresponding column. Columns 3, 4 and 5 have headings to represent 0, 1 or 2 withholdings.
The value I need returned would be the intersection of wages paid and withholdings. For my spreadsheet example, if the employee's wages are 1,023 and they have 1 withholding, I need the formula to return 147. I was able to find a vlookup/match formula but it worked only without a range of wages paid.
For example, if the employee was paid exactly 1,030 and had 1 withholding, it would vlookup/match to return 147. Obviously, this won't work with me needing to find the correct row based on where their wage falls in the ranges.
I've attached a picture of the spreadsheet example below. payroll example.jpg
View 2 Replies
View Related
May 9, 2014
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
View 2 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
Jun 20, 2013
I have two columns (A and B) with the same data. Column A is missing a value that is in column B.
Column A has 11330 rows whereas Column B has 11331 rows.
I am trying to do a comparison on the two columns to determine which value is missing from Column A.
I am using Excel 2010.
View 3 Replies
View Related
Jul 25, 2013
I could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.
View 3 Replies
View Related
