Checking Accounting: Copy all "Gas" amounts to new column

Discuss the spreadsheet application

Checking Accounting: Copy all "Gas" amounts to new column

Postby BJW86 » Sun Apr 19, 2015 7:59 pm

I got my check book on a spread sheet. So in column C is How the check is for "Gas Company" in column D is the amount for that check. I want to find all amounts for Gas Company and put them in column F. I have tried LOOKUP but to no luck any help. Brad
Last edited by MrProgrammer on Tue Dec 03, 2019 1:03 am, edited 1 time in total.
Reason: Edited title, was: Checking Accounting
OpenOffice4.0.1 on windows 8.1
BJW86
 
Posts: 14
Joined: Thu Aug 07, 2014 4:47 am

Re: Checking Accounting

Postby MrProgrammer » Sun Apr 19, 2015 8:07 pm

BJW86 wrote:I want to find
Do you mean that you want to sum all of the Gas Company amounts? If so, use SUMIF. Read about that function in the Help, in this forum, or in the Wiki.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3955
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Checking Accounting

Postby BJW86 » Sun Apr 19, 2015 8:12 pm

I was wanting all the amount for Gas Company's to be put in column F1 the next in F2 and so on. Brad
OpenOffice4.0.1 on windows 8.1
BJW86
 
Posts: 14
Joined: Thu Aug 07, 2014 4:47 am

Re: Checking Accounting

Postby jrkrideau » Sun Apr 19, 2015 8:34 pm

Try a pivot table : Data < Piviot Table > etc.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3740
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Checking Accounting

Postby Richarda44 » Mon Apr 20, 2015 11:23 am

What about an IF formula copied down column F
=IF(C2="Gas Company";D2;0)
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
Richarda44
 
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

Re: Checking Accounting

Postby MrProgrammer » Wed May 06, 2015 6:27 am

BJW86 wrote:I was wanting all the amount for Gas Company's to be put in column F1 the next in F2 and so on.
If you want view all of the amounts in column D with "Gas Company" in column C, this is done with a filter. You should use Data → Filter → AutoFilter. You could copy this data to column F, but it is not simple. See [Tutorial] Sorting and Filtering data with formulas.

[Tutorial] Ten concepts that every Calc user should know

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3955
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Checking Accounting

Postby pallavi » Fri Nov 29, 2019 3:29 pm

You can have as many columns as you like in your Table, just so long as you follow the ‘left to right’ rule above.

The ‘Table’ you are looking up can be in the same spreadsheet. Or a different sheet in the same workbook. Or in a different workbook altogether.

The table doesn’t have to be sorted in any particular order, but you must not have duplicates. Unless the information on each duplicate is exactly the same. For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, VLOOKUP would return the rate on the first instance of Doug.

The formula isn't case sensitive, so 'Doug' could be 'doug' or 'Doug', in either column B or the table.

What does it mean when my VLOOKUP returns a #N/A? It means Excel can't find the value you're trying to look up in your table. If you get this, but you can ‘plain as day’ see it's there in the table, then it’s likely you’ve got one prefixed with an apostrophe. To check this go to each cell you're referencing and look in the formula bar and see if there is an apostrophe in either cell ‘. You can only see the apostrophe from the formula bar. See example below.
Basically, Excel reads text prefixed with an apostrophe as different to text without. Even though on the face of the spreadsheet they might look the same. You need to make sure both the value you're looking up, and the value in the table either both have the apostrophe, or both don't. The quickest way to get rid of the apostrophes is to do ‘Text to Columns’. Or run it through the VALUE function, which converts numbers formatted as text to actual numbers.
Regards
Last edited by RusselB on Fri Nov 29, 2019 9:03 pm, edited 1 time in total.
Reason: Advertising URL removed to make the post eligible for posting.
openoffice 2.4 on ububtu 9.04
pallavi
 
Posts: 2
Joined: Fri Nov 29, 2019 3:15 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 9 guests