Spreadsheet to make my transactions easier to share with my accountant

[sean74]

Hello,

I am trying to create a spreadsheet to make my transactions easier to share with my accountant.

My process is to:

1. I upload my csv credit card statements, bank statements, etc... to a tab in my excel accounting spreadsheet.
2. Next I have to insert the transaction category and transaction type next to the transaction  (example, Office Depot would be Office supplies, Expense )

Virtually all of the of the businesses we transact with are the same transaction category and transaction type every month.

I would like to create a formula that is able to search the column of the businesses we transact with and automatically categorize the transaction to the proper category on the same row of said transaction.  I have already created a table in excel with the transaction category and transaction types.  I just need to pull them over to the correct cells when there is a match.

Can anyone help me with that?

Thank you,

Sean

BTW, I've tried Money in Excel and have had many problems.  Sometime banks or credit card companies will not send info when they are updating, other times info seems to change or disappear.  I have given up on it and I am trying to create the most efficient spreadsheet that I can to share my transactions with my accountant.

[#2]

can you share sample of your file.?

@sean74 

[#3]

@sean74 

I have used PowerQuery for splitting the CSV files into separate tables as well as filtering and sorting.  Otherwise XLOOKUP to categorise items and FILTER and SORTBY to group and reorder them.

[#4]

Thanks, Sorry I am a noob and don't understand.

[#5]

@sean74 Power Query is a way to on demand split csv data into columns.

The attached model makes it every calculation using a custom function named splitCSV.  

splitCSV([delimiter - default ,], [if blank], upper left cell, [column number])

It is not all to easy for a beginner but a model to start playing with.

Use sheet1 and paste data in column a.

Change column number in eg b6, c6 to get your choice.

XLOOKUP is a way to pull the transaction type and category.

[#6]

Thanks for the info....I will work with it to see if I can figure it out.  @bosinander