Copy data to different columns by name on same sheet

Highlighted
New Contributor

I'm putting several months worth of accounts onto a single spreadsheet.

I copy and paste Cols A,B,C & D  from the bank a/c, before c&p each amount into the relevant column,  ie. BP fuel goes under "Car"
With the same PAYEES appearing alot I wonder if there is a way to copy figs. into the right column dependant on the Payees NAME.
For example, the amount in Col D for BP would be automatically copied into Col G, "Car",
Col D for Mill Cafe would always be copied to Col H, "Client Lunches".
Many thanks

EXCEL.JPG

 

 

3 Replies
Highlighted

Excel shall know somehow for each payee to each category it corresponds. That could be additional table with such mapping. When you may add formulas to categories columns (INDEX/MATCH or so) which based on this information returns sums from column D to categories columns.

 

And better main info also to organize as Excel table to, as a minimum, autofill formulas when you add the information.

Highlighted

Thanks for your reply Sergei.

One off payees and their amounts will need to be c&p into the correct column. ie a one off payment to British Airways etc etc.

What I'm wanting is to copy those sums/figures from regular payees into a specific column.

How do I do that ?

Highlighted

But again, what if the formal logic behind, how do you know into which column and how to explain that to Excel.