notogmos said:
I copy and pasted the first formula and I still get a result of 8 - where 6 is desired (the number of unique names)
this is obviously unrelated to the Array issue... this should return 6... it may be that when you have copied the names you have included trailing spaces etc in the copy (?) -- to check, if you go to say Y4 and enter:
Code:
=TRIM(A4)=A4
applied to Y4:Z7
what is the output ? are there any FALSE returns ?
notogmos said:
Then I copied the formula for B1. I go to my sample spreadsheet. When I paste the formula, I don't see the opportunity to hit CNTR/SFT/ENTER as you suggest. The formula appears and the C/S/E doesn't do anything. When I try C/S/E before I paste the formula, there's still nothing.
If you copy the formula from the earlier screenshot when you paste it into B1 it will appear with { } ... these brackets should be removed from the formula manually ...
To do this, with B1 the selected cell, hit F2 and remove the brackets and then confirm the formula with CTRL + SHIFT + ENTER
To ease the above process I suggested copying the formula from post 4 into B1 (rather than from screenshot) thereby removing the need for the edit to remove the brackets, again with B1 active hit F2 and press CTRL + SHIFT + ENTER.
Once the array is set successfully in B1 copy the cell to the right as far as required.
Obviously PGC01's approach is the clever of the two given there is no requirement for the numbers in row 1 to begin with, however, unlike the earlier example it is Volatile given use of INDIRECT.
I would not necessarily advocate one of the other (nor I'm sure would PGC01) - you should use whichever method you feel most comfortable with or whichever suits your requirements best (or whichever works of course)