Formula that works like a data filter

[rfinnegan]

Hi All:

I have a file with about 2000 lines of data. In column A is the customer ID. Also in column A is the customer ID with the word 'Total". So if the customer has 3 invoices, It will show his customer ID in three rows, then the fourth row will be his ID + "total".
So:
Jack1
Jack1
Jack1
Jack 1 Total

I can filter for these lines using the 'Text Filter' and 'Contains', then 'Total" , but was wondering if there is a formula that would also accomplish this. In those 2000 lines are about 120 lines that I'm searching for with the word 'Total'. The solution I'm looking for finds the first instance of the word and drops it in row 1, then finds the second instance and drops it in row 2, even if the second instance is 30 rows away from the first instance.

Thanks in advance for any help.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Thanks for updating your profile, but it would have helped if you had let me know.
How about

=FILTER(A2:A10000,ISNUMBER(SEARCH("total",A2:A10000)))

 

[Peter_SSs]

Another option would be

=FILTER(A2:A10000,RIGHT(A2:A10000,6)=" Total")

Probably highly unlikely but this one ensures space and total is at the end, just in case total could be part of a customer id as well.