Moving from verticle to Horizonatal, but still matching the verticle data

How many Account numbers can a customer have?

Would it not make more sense to have the customer reference first?

No reply as yet. This solution allows a single customer, in effect, any number of account Nos.


Ordinary formula to return list of customer refs.
=IFERROR(INDEX($B$2:$B$7,MATCH(0,INDEX(COUNTIF($F$1:$F1,$B$2:$B$7),0),0)),"")

Array formula, copied across and down to return accoutn numbers:
=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$7=$F2,ROW($B$2:$B$7)),COLUMNS($G:G))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Fair enough. The formula/set up that I used should do the job for you, doesn't it??

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Im away for a while. Did you read the bit about array formulas?

First of all, in your screenshot (remember: don't post screenshots - we're lazy and don't really want to retype all you stuff), customer ref was column C, not column A.

Secondly, formulae to return unique values are a bit slow. Very slow actually. And with 2000+ rows to look at... you could grow old watching the circling donut of despair....
So, I used a bit of VBA to return the uniques...


See sheet. happy to explain anything.

Im away for a while... on the road. Look back in about 3 hours.

Please Login or Register  to view this content.

A very tiny piece of code to return the duplicates...

and then an array formula to return the account numbers, in F2, copied across and then down:

=IF($E2="","",IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$3000=$E2,ROW($A$2:$A$3000)),COLUMNS($F:F))),""))


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...