Spreadsheets

Separate person-to-person UPI from merchant payments in a sheet

Separate payments to people from merchant payments in a UPI sheet using name heuristics and a review column, keeping a To people bucket for manual checks.

Editorial illustration for Separate person-to-person UPI from merchant payments in a sheet

A statement does not say whether a UPI payment went to a person or to a shop, so any split is an educated guess. The practical approach is to flag likely merchants by keyword, call everything that looks like a personal name To people for review, and let a manual column have the final word. Never let a formula decide the category of a transfer to someone you know.

Why not categorise people payments automatically?

A payment to a person can be rent, a loan repaid, a gift, your share of a dinner, a domestic helper's pay or a family transfer. The narration shows a name and an amount, and nothing about the reason. Putting them all in one expense category would be wrong for most of them. A neutral bucket called To people, reviewed by you, is more honest than a guess.

What clues can a sheet use?

  • Business words in the payee name, such as STORE, TRADERS, MART, FOODS, MEDICAL, SERVICES or ENTERPRISES. These are examples, so build your own list from your statement.
  • A payee name of one to three words with no digits looks like a personal name.
  • A payee ID made of digits before the at sign is often a mobile-number address, though not always.
  • Repeat payments of identical round amounts to the same name, which may be rent or a helper, rather than shopping.

None of these is certain. A shop owned by a person can be named after them, and a person's ID can include a business word.

How do you build the flags?

Assume the payee name is in E2 and your business words are in H2:H20, with no blank cells in that range.

ColumnPurposeFormula
FBusiness word found=SUMPRODUCT(--ISNUMBER(SEARCH($H$2:$H$20,E2)))>0
GWord count of the name=LEN(TRIM(E2))-LEN(SUBSTITUTE(TRIM(E2)," ",""))+1
HSuggested type=IF(F2,"Merchant",IF(G2<=3,"To people","Review"))

Keep the formula this simple. A merchant word wins, a short name with no business word becomes To people, and anything longer goes to Review. You can add digit checks later, but only if you find you need them.

What should the review column do?

Add column I, called Your choice, with a drop-down list from Data Validation holding Merchant, To people and Not sure. Fill it by hand for rows where the suggestion looks wrong. Then add column J, called Final, with =IF(I2<>"",I2,H2), which uses your choice when there is one and the suggestion otherwise. That way the formula never overwrites your judgement.

What do you do with the people bucket?

  1. Filter for To people and sort by payee name.
  2. Give each repeating payee one meaning, such as rent for the landlord.
  3. Move recurring ones into their own category.
  4. Leave genuine one-offs as To people, and total the bucket each month so it does not become a blind spot.

Invented example: say To people totals ₹14,000 in a month, of which ₹12,000 is rent to one payee and ₹2,000 is scattered. After review you have a clear Rent line of ₹12,000 and a small ₹2,000 remainder, instead of one lump of ₹14,000 that explains nothing.

Before you rely on it

  • Check that no real merchant lands in To people because its name has no business word, by scanning the longest names.
  • Sort the Merchant group and look for personal names that slipped through.
  • Keep the original narration beside every suggestion.
  • Check that the group totals add up to the statement total.

SpendixAI does not claim to tell people from shops either. It categorises in stages, starting with your own rules and your past corrections, and it lets you turn AI off if you prefer to review everything yourself.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports