I have data with 4 columns A:Ref, B:Cost Centre, C:Name, D:£.
How can I return the £ data that share a common Ref, Cost Center and Name?
Many thanks
Michael Ung
Replies (6)
Please login or register to join the discussion.
Is it a table that you require?
Or the sum of the £ column that matches the criteria?
Or would the matching criteria always fall into a single unique row (or no match)?
You need one field
You need to create another column from the three of interest, e.g. =a1 & b1 & c1, then copy down the whole table. Now move this column in front of column A, sort by it and use it as the lookup column, or create subtotals by it.
Depends what you want and where you want it
for a table of totals you could try using a pivot table, or instead Advanced Filter to extract Unique entries for cols A:C by copying the headings to another area on the sheet then with the cursor in the main table use Advanced Filter to extract unique records to another area specifying the cells you copied the column heading to.
Copy the result to a new sheet
Create 4 range names for the data columns called iRef, iCentre, iName and iAmount.
If it is a fixed data set that will not expand then the ranges will be:
iRef = A2:An
iCentre = B2:BAn
iName =C2:Cn
iAmount = D2:Dn
Where n is the last cell, and the same for each range
If the data will increase you can make the ranges dynamic and expand as you add data by instead using the formula
=OFFSET($A$2,0,0,COUNTA($A:$A)) changing $A$2 to $B$2, $C$2, or $D$2 depending which range name you are setting, but leave COUNTA unchanged in each.
Now for totals for each unique set of Ref, Cost Center and Name go to the sheet you copied the unique entries to. Assuming that the unique entries are in cols A:C and start at row 2 put the following formula in D2 then copy it down to the rest of the cells
=SUMPRODUCT((iRef = A2)*(iCentre = B2)*(iName = C2)*iAmount)
Mind you to look up those values you will still need to insert a column in front and concatenate what will now be B:D as =B2&C2&D2 , but if you just want the total in one place in a spreadsheet then you can just use the relevant SUMPRODUCT formula changing A2 etc to the specific Ref, Centre and Name
use a pivot table
This is what a pivot table is for.
Put £ in the Data area.
Put Ref. Cost centre, and Name in the Row area
If you don't know pivot tables, Five Minute Tips on Pivot Tables will get you started.
Thank you to all
My grateful thanks to all that commented. I will try out all suggestions but for the meantime:
David: Thanks, I was expecting someone to suggest pivot table which of course is the obvious answer, but the person to whom I pass on the results is not comfortable with pivot tables .... No - please don't ask!!
Alan & Joyce: Thanks, I'll give it a go
Clint: Thanks - I would "preset" a table which would have ready all the combination, possibilites and permutation of Ref, Cost Centre and Name listed in col A, B & C of the results table, ready to receive the SUM £ in col D. So, in the result table, no combination data in column A, B & C would repeat in any given row. Does this make sense?