Search results

There was a tip this week about how to delete all columns where you found the word "John". Great tip, so I tried it on a spreadsheet where someone had inserted "spacer" columns for formatting purposes. I want to remove them. The only thing they have in common is the cell has no data. I know how...

Thanks to all for your quick and elegant responses.

Say my key is ANIMAL, and I have an array Machine Car Machine Stove Machine Train Animal Dog Animal Fish Animal Squirrel Animal Cat Animal Weasel House Bungalow House Cape Cod I want to specify the key value and return all the values associated...

Yesterday I asked for help on, and received several excellent solutions, for a sumproduct issue. The SUMPRODUCT expression contains 3 arrays where 2 are the same length, and 1 is a different length. The formula works - I want to understand what's happening under the covers. If I attempt to use...

Perfect. Not sure what I was doing wrong.

I am. My horizontal array has 28 items. My vertical RPT List has 693 as does my Yes/No array.

Luke M - I don't "own" the lookup table, therefore I cannot dictate that it be sorted.

Montrey, I neglected to note that the # of elements isn't necessarily equal to the number of entries in the lookup table. Hence, the arrays are irregular.

Thanks so much to both of you.

I need to count elements in an array where the element qualifies based on a separate lookup. Here's the horizontal array of 6 items (could be more, of course) Example: RPT01 ABC02 RPT03 RPT04 XYZ05 RPT06 For each item, I want to count it ONLY if a lookup vs. a separate table =...

That did it! Thank you for an elegant solution.

Sure. I have a 12 x 12 array of cells. A random number of them, say 23 out of the 144, have data in them. The data in the cells isn't the same (think animal names, for example). I'd like to isolate the non-blank cells and change the 23 values to the same value (say, "Yes"). Does that make sense?

We know Excel can isolate blank cells in an array using the Go To Special facility. Does something similar exist for non-blank cells? Thank you, in advance.

Narayan, kind of. Ideally, I like to put one array formula on each row and have it deliver back a variable length array that looks like the one in the example. This is fine, thank you, for now.

Thank you Sajan and bobhc for your reply. I've posted a file here https://www.dropbox.com/sh/yficwj2a6r43b9g/TokgmomFpX showing what I need. There are values attached to each "Set". The number of values can vary from 1 to N. Some of the values are special by dint of them being found in the...

I want to do N VLOOKUPS and return an array, something like {#N/A,#N/A,#N/A,#N/A,#N/A,Dog,Cat,#N/A,Fish,#N/A,#N/A,Mouse} I've tried =VLOOKUP(A$3:A$50,TableName,2,False) then Shift-Ctl-Enter to process as an array. It's coming back with only the first "action". Is there a way to return what I...

Actually, the questions would be answered by Frontline Systems (based in Incline Village, NV.) They've been the authors of the Solver algorithms used in Excel since forever. This isn't a plug, but I thought most of the Excel fanatics on this call would enjoy knowing this tidbit.

You're referencing the right article. When I'm counting the number of numeric characters in the cell using =SUMPRODUCT(COUNTIF(lstDigitsWithSlash,MID(B4,ROW($A$1:$A$200),1))) The array 1stDigitsWithSlash contains 11 items the "/" character as well as as 0-9. When you evaluate the phrase...

but it's not in the list array. In other words, the cell containing Date of birth?!? What is that now? has a count of 3 even though my list array has 11 entries - 0-9 and /. Diagnosing the formula finds that the ? character is being counted.

Exactly the syntax I was looking for! Thank you very much, Hui.

I'm testing a cell to see whether it's not equal to several values, e.g. =if(and(A1<>"Bear",A1<>"Goat",A1<>"Moose",A1<>"Lion") . . . Is there a way to contruct the test where I only have to cite the cell once, e.g. =if(A1 <&#62...

Is there a way to tell whether you're within a Custom View or not? I rec'd a spreadsheet the other day, and it drove me nuts for awhile until I figured out the author was using Custom Views.

I have a column of member Ids - 6 digits, many with leading zeros - ~7,000 of them. I get other files with information that I attach via vlookups using this member number. Each time a new file comes in, I have create a new column formatted as Text, strip off the spaces from the original value...