need to total several lines of info and have it inserted on one line

[brlcarol99]

I have a list of several thousand customers and their account information.
Several of the customers have job accounts. I need somehow to combine all the totals of balances owed.
See the attached sheet for what I mean - I have highlighted several of the accounts in question.

Usually we manually would add up all the info and add it to the main account row, then delete all the job rows.
I guess my question is, is there a better way?

testing.csv

[hoyasaxa215]

I'm not seeing any highlighted accounts. Can you provide a couple example account numbers that you'd like consolidated?

[brlcarol99]

sorry- I think when I saved and uploaded it, the formatting changes. Not sure why.

Accounts 100 and 180 are ones like I'm talking about

Account No Balance
10-10 $40
20-10 $30
100-0 $50
100-1 $50
100-2 $75
100-3 $80
105 $40
106 $50
107 $60
180-0 $20
180-1 $25
180-2 $30
185 $35
186 $40

[hoyasaxa215]

I just ran a test for duplicate accounts (assuming that's Column A) and was unable to locate any. Perhaps the file you uploaded was missing more than just formatting.

Regardless, seems you'd want to use the "Subtotals" feature. Click on "Data" then "Subtotals". You would then select "At each change in" account number, then you'd select the "Sum" function from the dropdown, and pick the "Total Balance" column (or whatever column you want to add up for duplicate account numbers).

[brlcarol99]

it seems when i save it all goes wrong, i need it as a csv file

ok that's great, i'll try that!
thank you!!

[brlcarol99]

how do i run the test for the duplicates?

[brlcarol99]

I changed it to save as an excel instead of csv.
I'm not sure the data subtotal will work?
Do I click on the range of all the jobs for that account before doing this?

Let me upload the new file

[brlcarol99]

p test.xlsx

this is the new file in excel not csv

[hoyasaxa215]

What do the hyphens mean? Are 100-0, 100-1, 100-2, and 100-3 all the same account? I think maybe that's why I'm not finding duplicates. Can you please provide some additional explanation about how the account numbering works?

[brlcarol99]

the first part of the number is the main account number
the second part (after the hyphen) is the job number
accounts, 10, 20, 30 and 40 are excluded

100-0, 100-1, 100-2 are the same account number the 0,1,2 are their job accounts - i need a total for all 100 together main account (0) and the jobs (1,2)

[brlcarol99]

here's a thought:
originally there were two columns, one for customer number and one for job account number; i merged those two columns and added the hypen

do you think this would be easier if we did it before merging the two?

[hoyasaxa215]

Definitely. Took a crack at it. Let me know if this isn't adequate.

[brlcarol99]

ok i can't have the word total after the number. and how did you do it? I'll have to create this each month.
Thank you!!!

[hoyasaxa215]

First off, I separated the account column back into two separate columns without the hyphen (as it was before you merged the two). Then just go to Data > Subtotals. You would then select "At each change in" account number, then you'd select the "Sum" function from the dropdown, and check the boxes for the columns you want to be added. I selected 1-30, 31-60, etc. Let me know if this doesn't work.

You may want to consider a pivot table, if you're familiar with them.

[brlcarol99]

ok great, thanks! i will try that then!

[brlcarol99]

so I'm working on this now, this does do what I need, but....I now have to go in and delete out ALL the TOTAL lines it inserted, the Total lines I don't need - the accounts without Job numbers. The job number totals is great, I just make the total line the main line and delete all the others.

[brlcarol99]

I'm working on it now, in the rows with no job number, it's still inserting a line that reads TOTAL, that I now have to delete.
With the job accounts, i keep the total line and delete all the detailed info above.

is there a way to exclude the totals from just the regular account numbers too, it's time consuming having to delete about 2,000 lines.