Excel help please guys.

Colbaker · 2 Dec 2015 at 22:15

Hi all.

I would like to pick your excel minds please.

Picture this. Column A has a list of names, these names could change at any time in a month. In Column B is how much money said person in Column A is Paid. These names will come up multiple times as there people will work multiple day.

Now what I am looking for on book 2 is a formula that in Column A will list all the names in book 1 but only once (not sure what formula could do this).
In Column B book 2 I want the amount of total money they have earned from Column B book 1 but only for each individual.

Any ideas on what formulas I should use? I hope I have made it clear

Thanks.

Uhtred · 3 Dec 2015 at 11:50

What about using an advanced filter?

http://www.contextures.com/xladvfilter01.html

Ethermaster · 3 Dec 2015 at 12:47

Pivot table should solve the issue

wesimmo · 3 Dec 2015 at 12:52

Ethermaster said:
Pivot table should solve the issue

Seconded.

You can do it using formulas, but a Pivot will do exactly what you want without you having to learn a load of new Excel functions.

Ethermaster · 3 Dec 2015 at 12:59

Highlight all your data the go to insert, then select pivot table. Drop the names in the field labeled 'ROWS' then the pay into VALUES. Right click the values field and select 'SUM' job done.

Colbaker · 3 Dec 2015 at 14:19

Thanks all for your reply's but i am really looking for formula's as they will be on a template, My boss wants to do as little as possible to get the information, formula's will do this automatically (hopefully).

Ethermaster · 3 Dec 2015 at 15:47

So will a pivot table if you place the data in a table you will just need to click refresh

GhostWKD · 3 Dec 2015 at 15:53

Passing glance from me as I'm exercising my own excel demons

but a SUMIF should suffice for this I'd have thought

Although that's assuming it's not ever changing names in which case a pivot table would be the cleanest way for it

Dunners · 3 Dec 2015 at 16:36

Definitely a SUMIF I reckon!

suenstar · 3 Dec 2015 at 18:41

I may be misunderstanding your question, but would the Consolidate function not work?

Try creating a new sheet within the workbook, open the Data tab and click Consolidate. Then set the area to look at the sheet with the data, including the entire area that is being used. Tick the boxes to use the labels in the top row and first column and you should be sorted.

Any duplicate names will automatically have their values merged so that they only occur once in the table on the new sheet.

me227 · 3 Dec 2015 at 18:47

Pivot table is much better for this than formulas.

Turn your big list of name and money into a table and create a pivot table from it. You can have it so it automatically refreshes every time you open the spreadsheet.

Mysterae_ · 3 Dec 2015 at 19:01

Dunners said:
Definitely a SUMIF I reckon!

Pivot table would be good if there's a lot of data, otherwise a quick SUMIF would suffice.

Ethermaster · 4 Dec 2015 at 07:28

I still think that a pivot table is the way to do this simply.

Below is the tracker were I would enter the data I have linked this via a way of vlookup to another tab so that it kinda makes it easier with a drop down menu.

I then created the pivot table and this the outcome.

If you want I have also shared the workbook so you can download it here.

EDIT:

Oh to add a new record to the table just press tab in the last cell written in. To insert names insert a new cell between "TOM" and "DICK" on the configuration tab