Excel 2010 :: VLookup For First Non-blank Cell
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
ADVERTISEMENT
Mar 3, 2014
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
Excel 2010
Userform = Tab 1
Database = Tab 2
View 6 Replies
View Related
Aug 30, 2012
Looking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
View 9 Replies
View Related
May 8, 2014
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
View 9 Replies
View Related
Mar 31, 2014
When I had Excel 2010 and now with Excel 2013, whenever I open a workbook, it opens and displays to the row AFTER the last row of data. (So, if my last row of data is 38205, it will open starting at row 38206...hence I see no data until I hit Ctrl+Home). I have not found anything about this on the internet and I cannot find a setting. I need Excel to open to A1 so I don't freak out every time I open a workbook and see no data.
View 2 Replies
View Related
Sep 10, 2012
I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week
I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns
I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34
=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,
X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)
I could use SUM, COUNT COUNTIF etc but its gonna get messy
Using EXCEL 2010
View 5 Replies
View Related
Jan 27, 2014
Why do blank columns with no information sometimes appear when I prepare pivot tables? Excel Version 2010. I hide the columns, but many times they re-appear on screen or print-outs.
View 1 Replies
View Related
Jul 7, 2014
find the attached excel 2010 file <Count LBP or UBP.xlsx>.
My requirement is if `LBO' is available (in Column A) then the formula should count the number of `LBP' available above the `LBO' upto the blank cell. If there is no `LBP' above the `LBO' then it should plot zero in Correponding Column B Cell. If 2 `LBP's available above the `LBO' upto balnk space it should plot `2' in Correponding Column B Cell.
As like `LBO' and `LBP' the formula should work for `UBO' & `UBP'. refer the attached excel file.
View 6 Replies
View Related
Aug 21, 2012
I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.
Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.
Also I am using Excel 2010.
View 6 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
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
Apr 23, 2012
My office recently upgraded to Office 2010 and we would like (in the accounting department that I work in) to change the default number formating in a blank sheet to Number, 0 Decimals, using seperators, from the current default of general format number.
I have looked for the Book.xltx file to replace but can't see it any where.
View 5 Replies
View Related
Dec 19, 2012
how to fill the blank cells in a Pivot table. I am using Excel 2007. How to do it in excel 2007. Heard that there is a provision in excel 2010 version.
View 9 Replies
View Related
Mar 15, 2013
I am trying to insert a blank row into a table (created using the table function in Excel 2010) without the copying the formulas. Every time I insert a row the the formula is copied.
View 3 Replies
View Related
Feb 10, 2014
We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)
Right now here's how the data is received:
a
a
a
b
b
c
c
c
c
d
d
d
e
e
e
e
Using two vba scripts, we first separate the data with row spaces between the unique data as follows:
a
a
a
b
b
c
c
c
c
Then with another script, we transpose the data as follows:
a a a
b b
c c c c
d d d
e e e e
When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...
a a a
b b
c c c c
d d d
e e e e e
where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.
The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.
Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?
My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.
I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)
The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.
One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.
We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.
For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.
One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.
View 5 Replies
View Related
Jan 23, 2014
In earlier versions if I used the double clicked the autofill handle it would only fill down to the next adjacent cell containing data. Now it fills down to the bottom of all the data regardless of whether there is a blank cell or not e.g
X X X Z
X X X Z
X X X Z
X X
X X X
Now this happens
X X X Z
X X X Z
X X X Z
X X ...Z
X X X Z
How can I stop this as I need to enter different data in the next section?
View 1 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 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
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
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
May 21, 2013
I have a list that I need to move to another column without spaces. I have it moving without spaces but it is only one item over and over again. I am using Excel 2010 and that may be my issue. I have attached the exact worksheet and formula.
To Buy List.xlsx
View 2 Replies
View Related
Jun 14, 2014
We have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.
Each order entry is 1 or 2 rows
Using Excel 2010/2013 although the file produced is an xls file.
Columns A to J are populated
Rows 1 & 2 are the header
I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.
View 3 Replies
View Related
May 12, 2013
I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.
Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6
Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0
The formula I am using is:
=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5
Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.
Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.
My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?
View 2 Replies
View Related
Jul 25, 2013
I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
View 1 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
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
