How to rearrange row entries based on keywords in the entries itself

[iamjesss]

Hi guys, need some help here.

Basically every month, my company accounts dept will send me a long list of claims/expenses that have been made for the project I'm working on. I need to sort all these claims out into its respective category (flights, accommodation, food, etc.)

The problem is that the excel file they give me is not very organised. There is no unique identifier for the items. Only the cost, and a description of the claim which looks something like this:


[NAME OF EMPLOYEE] - TRANSPORT (L)- TAXI 4/11/2016
[NAME OF EMPLOYEE] - TRANSPORT (L)- TAXI 10/11/2016
[NAME OF EMPLOYEE] -BUSINESS - PHONE- MAXIS @ 07/11/2016
[NAME OF EMPLOYEE]- FOOD & ACCOM (O)- MEAL -18/11/2016
[NAME OF EMPLOYEE]-FOOD & ACCOM (O)- HOTEL - 10/11/2016 -11/11/2016


So imagine lines and lines of this going on for what feels like no end.

- How can I sort this bulk into categories (flights, accommodation, food, etc.)?
- Is there a formula that can pick out keywords?
- Is there a way to simply upload this excel sheet and let the program categorise it for me? Since the format will be the same each month, technically it'll always be the same column from which the program needs to search (eg. sort from H13 onwards)

Thanks lifesavers!

Jess

 

[RedBeard]

Is the information all in one cell per row?

 

[footoo]

Let's say you want to summarise by TAXI, PHONE, MEAL, HOTEL (as in your example).
Put these 4 categories as headers in I12:L12
Assuming the cost is in column G, enter in I13 the formula =IF(ISNUMBER(SEARCH(I$12,$H13)),$G13,"") and drag across and down.

You will also need a column for un-categorised items:
=IF(COUNTIF(I13:L13,"")=4,G13,"")

 

[iamjesss]

Yes, the name of employee, item, and date are all in one cell per row. The cost amount is in its own separate cell in the same row

 

[iamjesss]

Hey this totally works! Thanks for your help!