List INCLUDING repeat values

Hi,

I need to create a list from one column, but I want to include all occurrences (ie. not a unique list).

I have attached a spreadsheet with my data. Basically, starting in D8, I want a list of ALL occurrences of cells containing 5002 and 5200, as a single list.

I tried this =INDEX(Account,MATCH(0,IF(OR(LEFT(Account,4)="5002",LEFT(Account,4)="5200"),COUNTIF($D$7:D7,Account),""),0)) where Account is the name of my original list. However, it picked up all of the 7087's I don't want those.

Please help. What am I missing?

Thanks,

Is this what you want

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Originally Posted by Fluff13

Is this what you want

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks, Fluff13. However, this still just gave me a unique list.

In that case how about

Formula:

Please Login or Register  to view this content.

This is a normal formula, so just use Enter

Thanks again. Almost. It didn't pick up the 5200 rows.

Oops, missed a set of brackets

Formula:

Please Login or Register  to view this content.

You sir are a genius. Thank you so much. I really appreciate it.

You're welcome & thanks for the feedback