Converting account data in spreadsheet: single to dual column format

Other Questions Google Sheets
1

Hey everyone, I’m working on an accounting spreadsheet and I need some help with a formula. Right now, I’ve got all the account info in one column, but I want to split it into two columns based on some rules.

Here’s what I’m trying to do:

  • If the voucher number is 2, I need the account in the new column to be the opposite of what’s in the original column.
  • If the voucher number is 3, the new column should just show the first account.

I’ve set up column G to show the accounts that should correspond to column C. What I’m looking for is a formula to put in column G that’ll give me the results I want (which I’ve shown as an example in column H).

Has anyone done something like this before? Any ideas on how to write this formula? I’m not great with complex spreadsheet stuff, so any help would be awesome. Thanks!

2

hey, i’ve got an idea that might work for u. try this formula in G:

=IF(C2=2, -D2, IF(C2=3, LEFT(D2, FIND(‘-’, D2)-1), ‘’))

it should split ur accounts based on the voucher #. copy it down G and see if that does the trick. lemme know if u need more help!

3

I’ve encountered similar spreadsheet issues before and found a workable solution. One formula that might help you in column G is:

=IF(C2=2, -D2, IF(C2=3, LEFT(D2, FIND(“-”, D2&“-”)-1), “”))

This formula first checks if the voucher number in column C is 2. If so, it negates the value from column D. If the voucher number is 3, it returns the portion of the account data before the first hyphen. If neither condition is met, it leaves the cell empty. I’ve used this approach to simplify data conversion in spreadsheets, and it reliably handles the logic required. Just copy this formula down column G to apply it to all your rows.

4

I’ve dealt with similar spreadsheet challenges before. Here’s a formula that should work for you in column G:

=IF(C2=2, -D2, IF(C2=3, LEFT(D2, FIND(“-”, D2&“-”)-1), “”))

This checks if the voucher number in C is 2 or 3 and acts accordingly. For voucher 2, it negates the value from D. For voucher 3, it extracts the first account (everything before the hyphen). For other vouchers, it leaves the cell blank.

Copy this formula down column G to apply it to all rows. It should give you the desired output shown in column H. Let me know if you need any clarification on how this works.