Hopefully you’re brain is tired yet. I posted this question in GQ where someone pointed out this thread.
My original question was related to sorting out duplicate entries in a list of 6700+ rows. Now I am using my sorted data (900 rows) to create a VLOOKUP. Somehow the data is not being recognized and I get #N/A as answer (ie even though both the lookup data and table array value are the same number, eg 310110, I don’t get the return value I’m looking for). If I do a F2/Enter on the row the formula works (ie the VLOOKUP gives me the correct return)…short of doing a F2/Enter on all 900 rows, is there an easier way of doing this?
Is the column you are searching through sorted in ascending order? If it isn’t, that could be your problem.
The parameters for VLOOKUP is VLookup( value, table_array, index_number, not_exact_match ). If sorting your data in ascending order doesn’t work, then try changing the last parameter to the opposite of what it is right now (true or false).
This isn’t quite right. Vlookup only needs the data to be sorted in ascending order if the not_exact_match is set to TRUE. I always set it to FALSE for this reason. It will give you a #N/A if there’s not an exact match which I find to be very handy.
It sounds like your data isn’t in the same format (your lookup value is a number, but the target array data is text). When you hit F2, you’re editing the contents of cell, then when you hit enter, excel recognizes it as a number and converts as such.
Try using the VALUE() function to convert your text into a number, then do a lookup off of the cell with the VALUE equation. Let me know if you want more specific instructions on how to do that.
If you wouldn’t mind expanding on this I would greatly appreciate it. I figured it might have been a value/format issue (given that part of the data is extracted out of a non-excel database), I just don’t know how to work around that.
Is there any way to create a macro to filter items by date, using a column on the spreadsheet as a reference? For example, I have a spreadsheet with a column that lists the date the spreadsheet was created (Column A). I want to search a list of invoices to give me all that are 60 days past the invoice date (column H). So basically I want it to filter column H for A2-60. Can this be done? I know I can do it using the filters, but I want to automate it.
Sure thing. Say that your data is in columns A through E. Your vlookup should look something like this: VLOOKUP(X2,A:E,2,FALSE)
Insert a column before A, and in the first cell (assuming you use column headers), use this equation: VALUE(A2)
Autofill the equation all the way down to the bottom of your data, then correct your vlookup equation to be VLOOKUP(X2,A:F,3, FALSE). That should do it!
Somehow that gives me back to #N/A…In my case tab1 has a value in A2 for which I need the corresponding value (description) in the 2nd column of tab2 (Lookup).
When I do your steps I get the following function
VLOOKUP(A2;Lookup!$A$2:$C$894;3;FALSE) which displays #N/As again
Since my earlier question wasn’t answered (see right above EuroMDguy) i’m going to ask it another way.
I’ve created a macro to filter certain items and paste them on a different page. It seems to be working okay. But since one of the filter criteria is a date minus 60 days, how can I do that? I went into VBA and created an inpt box to ask for the date, but how do I use the inputted data in my filter? And even better, can I create an equation that takes the input date and subtracts 60 days. My end goal is >= 60 days from today’s date.
Hello. Sorry to bump the thread, but let’s face it, with a title like that it is truly immortal.
Macro programming question: I have a cell range, say A1:A23. I want to put the values in that range, into the array happyfeet(). Is there a quick one-line way of doing this? I keep using for/next loops which seems a bit wasteful. But whenever I try something like “happyfeet=range(“A1:A23”)” or any variation I can think of, it doesn’t work. What am I missing?
Doesn’t seem to work. I still get the same problem I always do… whenever I try to refer to an element of the array (say happyfeet(1)) it always crashes with a ‘subscript out of range’ error.
I should mention I use ReDim not just Dim, because I need to get the required length of the array as a variable first. (and for some reason you can’t just say “a=23: dim happyfeet(a)”)
Also surely you don’t need to include ‘As Variant’? I thought it defaults to Variant.
Did you mean to write ‘dim happyfeet’ without parentheses or array size?
I want to have a separate rank column for the percentages. No problem. However, when I filter out a Group, I want the rank to change to rank only the visible records. With the example above, the third record’s percentage would rank #3. However, if I filtered it to only Group 2, the rank should then be #1.
Is it possible to rank based only on visible cells? I looked everywhere this afternoon and had no luck.
Hey Excel guy! I have a question. If I have a column of text and some of the entries are in bold font and the rest aren’t, is there a way to just count up the bolded entires? Thanks!
In this folder(s) a buch of photographs of parts. All named by part number.
then over here, an excel chart with all the part numbers.
Actually we have them listed by ‘old part number’ and ‘new part number’.
So let’s say someone wants to look at parts that are similar. So we want to filter the parts down by ‘type’ or part, (ring) (d-ring) (grommet) and perhaps by size.
Then, could we display the images of those parts? Is there an easy way to embedd the images, rather than one at a time? Or hyperlink, again in a batch process, not one of 10,000 at a time?
if you have the line happyfeet()=range(“A1:A23”) in a VBA Macro, you will be assigning the whole range A1:A23 to the variable as an address. This will not assign the values of these cells to any variable.
You could do something like this;
DIM happyfeetrange AS Variant
happyfeetrange=Range(“A1:A23”)
DIM happyfeetvalue AS Variant
DIM i AS integer
i = 0
FOR EACH Cell IN happyfeetrange
happyfeetvalue*=Cell.Value
i=i+1
NEXT
This might give you another avenue to go down (my knowledge of arrays and VBA isn’t that good!). I’ll kick this around over the weekend and see if I can get something together that will help.
I have an 80-year old relative who has a Mac laptop and uses it to edit books for a 3rd party. He has mastered Photoshop, sort of, but he does not know what a spreadsheet is even though I have told him about my heavy use of them, at home and at work on my PCs.
Is Excel typically preloaded on a Mac or would it have had to have been bought separately and loaded? His Mac was a gift from the textbook author for whom he does the editing. He’s not “into” computers or their extra features.
If Excel is typically loaded and if he has it, how can I tell him (by phone or email as he’s 800 miles away) to access it?
Dear Excel Guy (and assorted Gurus),
I am trying to reference a table of normative data and return the “top-most value” in the column that matches two given values:
Value that identifies the row
Value that identifies the column
Example, In two cells, the user would enter the value “19” (which will select the row starting with 18) and the value “56” which will select the column with 55) and the formula will return “1”
I have been reading about VLOOKUPs, OFFSETS, UPPER LOOKUPS, but cannot get it right. I;m comfortable with html, php, Javascript, but this is taxing my brain.
Each month, I get a consolidated income statement from my corporate accounting that includes seperate worksheets for each location in my company. Normally, I’ll copy each individual sheet into its own worksheet so I can then send them out to the location.
Is there a way to do this in one step for all the worksheets I need to seperate out?
I still get the same problem I always do… whenever I try to refer to an element of the array (say happyfeet(1)) it always crashes with a ‘subscript out of range’ error.