Hello All,
Long time lurker (thanks for all the problems you've helped solve, though you didn't know it), first time poster. I've encountered an issue that I've spent almost a good 6 hours researching, to no avail. Please see if you can help.
I'm working in XL 2010.
I have a HUGE data file that has a list of estimators (names) and about a million lines of data corresponding to each person. Ultimately, I need it cleaned up to move it into a dashboard that is usable by management. For this, I had planned to use dynamic pivot tables to avoid having to manually update every line of data...HOWEVER, the original author of this enormous data file left lines available (mostly titled "Estimator #1" "Estimator#2" "Available," etc) to account for potential additions to his already mostly-automated spreadsheet. I didn't think this would pose such a substantial issue but I guess it has. I'm not sure the most efficient formula and, at this point, I can't even get anything to work.
What I'm trying to tell it to do is, "If the cell <>, "Estimator*"), return the value of that cell.
I have tried using:
IF statement -- has issues with returning wildcard text in a logical function, I guess.
IF(SEARCH -- Returning all cell values, regardless of = or <>.
ISTEXT -- returns only FALSE.
IF(ISTEXT -- Returns no values with wildcard in text string.
IF(ISTEXT(SEARCH -- Returns all cell values, regardless of = or <>.
I have tried all of these combinations with either a wildcard or, if that didn't return values, a <>.
And, while admittedly newer to vlookups, I couldn't find a workable way to do this.
All I can find that is working (I digressed just to make sure I wasn't writing formulas incorrectly) is the COUNTIF...this isn't an option, as I need the actual names and not a 1, 0.
sample.xlsx
I am at a total loss and thankful for anyone who puts forth the effort to teach me to solve this problem.
Thank you!
Bookmarks