Copy data to different columns by name on same sheet

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




3 Replies

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.


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 ?


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