Excel Help

hellarda · 17 Jul 2018 at 11:55

Hi all,

I'm trying to streamline my invoice/receipts/tax spreadsheet and wondered if someone could point me in the right direction of what I want to achieve....

The current format I have (and I'd quite like to keep) is columns for Invoice, date, Company, sub total, VAT, total and payment date.

I input entries here from the top down in Invoice order. I have a seperate sheet for use as Calculations sheet, as I want to generate graphs/data sets of total pay by company, number of days taken to pay by company etc.

Currently I use an assortment of inefficent formulae and pivot tables to obtain the information and graph it up. I'd quite like to streamline this process as its quite time consuming, and I am finding less and less time for this admin stuff (yes yes get an accountant etc. but I'd quite like this information anyway).

What I'd like to do is, everytime I input a company name into that field, excel checks the rest of the column for the same company name, if it does not find the company name, then it creates a new entry for that company in the calcsheet, for each metric (total amount, days taken to pay etc), which I can then easily put into a graph.

If the inputted company name is already present in the column I want it to add the new information to that companies entry within the calcsheet.

Probably not explained myself very well.... is this something that looks like it might be possible?

Cheers

Firestar_3x · 17 Jul 2018 at 12:13

Use a formula which finds the unique names in the list, this will update constantly, use this formula on the clacsheet then getting all the other data should be easy, vlookup etc.

https://exceljet.net/formula/extract-unique-items-from-a-list

hellarda · 17 Jul 2018 at 12:52

Thanks! that seems to have worked... trying to work out what to use to add up the total income from multiple invoices from the same company (vlookup only seems to return one value, presumably as its just 'looking up' a value, not actually performing any calc on it) is there a way to lookup all the values from the same company and sum them invisibly?

thanks

wesimmo · 17 Jul 2018 at 12:55

That's probably a SUMIF.

Firestar_3x · 17 Jul 2018 at 13:59

Yea, you can actually use a combined sum and vlookup formula too.

Born2BSlow · 17 Jul 2018 at 15:17

I usually create a unique reference number in column A, then use vlookup on the number to bring the company name onto the summary table (calcsheet) and sumif on values as required.

in Cell A1 enter 0 (zero), then in cell A2 next to each entry (assuming you use column B) I'd use:
=IF(COUNTIF($B$1:$B1,$B2)>=1,0,MAX($A$1:$A1)+1)

Every unique entry then takes the next sequential number. A new entry will automatically get included assuming you vlookup on every number up to the =Max(A:A) in column A...

Edit: I'm so old school, Firestar's solution does the same thing in a single formula lol. I really need to get down with all the new functionality in Excel.

hellarda · 24 Jul 2018 at 17:10

Thanks for all the replies - @Firestar_3x I've used your solution and it works great!

Next question, I'd like to see how long it takes for each company to pay me, I've got a column containing the number of days taken to pay, but I'd like to average this data by company (if they've paid me three times, I'd like to add up the number of days and divide by 3), and put this in a column next to my list that automatically updates with new companies.

Obviously I now know how to extract the number of days for each entry and SUM that, but how would I extract the number of times that company appears in the list in order to divide the total days by it?

Thanks

Firestar_3x · 24 Jul 2018 at 18:45

You could use a countif statement