Countif & Vlookup array problem(s)

[schatham]

Both of these may be related, since the problem I'm having is doing the same in either of the things I've tried to do.

I have a spreadsheet where I have several thousand rows of shipto numbers. They may be repeated in this initial worksheet.

I have another worksheet in the same workbook that I have that has about 40,000 rows in it, with this shipto number in column A and a "key" (that tells me something I need to know) in column B. These two columns are a range named REPS.

On Sheet1, I need to know how "dirty" my data is - i.e. how many instances I have where the number of rows of these shipto numbers is > 1. I have a column "Occurrances" in Sheet1, Column D. Column C has this shipto number in it.

If I go to Column D2 and enter:

=COUNTIF(REPS,trim(C2))

what's displayed in Column D2 is exactly that. No error, no nothing. If I hit F2 & edit the formula, then hit Ctrl+Shift+Enter, it does nothing. Left Ctrl + Left Shift + Enter does nothing - same formula is still displayed in the cell.

Even if I do something simple - like put in:

=VALUE(C2) in D2, it just displays the formula in it.

There has to be a setting somewhere, but I haven't knowingly disabled it, and use Vlookups all the time.

Which brings me to the second problem I am having.

On those that have > 1 occurrances of a shipto number, I need to know all the ones it has associated with it.

If I enter the formula manually, I get the same thing as above - the formula displaying in the cell - doesn't make any difference if I hit C+S+E or not.

If I copy the formula from an example spreadsheet & change the references, it works once I change the formula to refer to my data.

That formula is:

=VLOOKUP($C$11,INDEX(Reps,SMALL(IF($C$11=INDEX(Reps,,1),ROW(Reps)-MIN(ROW(Reps))+1,""),ROW(1:1)),,1),2,FALSE)

In this case, $C$11 is =VALUE(B11) Here, the formula appears to work.

What in the heck am I doing wrong?



SC

 

[schatham]

............. After losing the last 2 hours on this issue, it finally dawned on me that the cells were formatted for Text only & wouldn't show a formula no matter how much I edited it, changed it, thought it was wrong, or cussed at it...............................


Sorry!!!!

But, in some small way, explaining the issue is what FINALLY clued me in to what was wrong.

 

[Jon von der Heyden]

Not sure if you have noticed but usually just changing from text to number format; it doesn't convert all of the existing formula to their results. Sometimes after you have changed to number format you need to select the column > Text to columns > delimited (step1) > clear all delimiters (step2) > finish.

 

[Aladin Akyurek]

...

That formula is:

=VLOOKUP($C$11,INDEX(Reps,SMALL(IF($C$11=INDEX(Reps,,1),ROW(Reps)-MIN(ROW(Reps))+1,""),ROW(1:1)),,1),2,FALSE)

...

Just curious:

Why this instead of just:

=VLOOKUP(C11,Reps,2,FALSE)

 

[schatham]

Just curious:

Why this instead of just:

=VLOOKUP(C11,Reps,2,FALSE)

Would this work if it was a variable number of repeated records for each of those shipto's?

 

[Aladin Akyurek]

Would this work if it was a variable number of repeated records for each of those shipto's?

VLOOKUP() always picks out the first record that satisfies the lookup value. Would you like to post a small sample along with the desired results?