What Excel function could I use?

Hi, need a bit of help. I don't know if there is a simple solution to this or whether it requires a more complex function/programming.

I have a workbook with a number of worksheets. On each worksheet is something like:

- id - value - type

In 'type' I can enter anything, it's a bit like a category.

What I'd like to do is on a separate worksheet to collate all the entries in 'type' and then sum the 'values' against them.

How can I do this?

Thanks :thumb:

of course you can

is there only one entry on each sheet and is it always in the same location

does your summary sheet have a special name

here to help

The 'type' field will always be in the same column on each worksheet, but the number of entries per worksheet is variable.

The summary sheet is simply called 'summary'.

Thanks,

use formula sumif

I'm not entirely sure how to use this function.

Example:

w/sheet 1:

- id - value - type
----------------------
- aa - 10 - business
- xx - 20 - pleasure

w/sheet 2:

- id - value - type
---------------------
- a - 10 - pleasure
- b1 - 1 - holiday
- b2 - 1 - other

w/sheet 3:

- id - value - type
--------------------
- 11 - 100 - new
- 22 - 5 - holiday



what i want to output on the summary w/sheet is something like:

business - 10
pleasure - 30
holiday - 6
other - 1
new - 100


the entries in type can be anything so as i add in a new type (eg. 'car') it should automatically update in the summary list.

but do you always only have 3 sheets?

There are actually 12 worksheets, always 12.

I think I've worked out half of the problem. On the sheet3, I'm using formula from http://www.cpearson.com/excel/ListFunctions.aspx which outputs the unique entries in column b from sheets 1 and 2. Then I do another filter to get the unique entries from sheets 1 and 2 combined. Then I do another filter to get the non-blank entries at the top and the blank entries after that.

The next stage I need to work out is how to use the entry in the list to sum the values from sheets 1 and 2.

would really appreciate some help on this,

thanks,

adding attachment.

there are a number of different ways to answer you question

you could use the 3-d reference syatem to bring the entries to you...

you could simply copy the entries to an temp sheet and do the calculations there...

what ever you do you will need to wirte some code

simplisity says "do dont ask and then clean up!"

its always the fastest method and gives you loads of data to play with!

so copy all the sheets to a temp-sheet

dont wory about blanks and headers they will get cleaned out

bring all the data to the top sorting will organise your data

apply the sumation formula to every line

to stop values changing select whole sheet copy and paste-special (values only)

as things will not change

remove column "A"

remove any line that is below a line that is identical


now you have a set of sumations for all the sheets and all the types

column "a" is type column 2 is sumation

done.

that piece of origami will always work reguardless of number of types

most of the code is macro recorder based

the difficult bit is the removal of the mutliple type lines

in a database the unique record would have solved that for you automatically.

here to help

code if needed

the piece of origami as sugested works fine in mso2007

Code:

---

Sub sort_all(sheet As Worksheet)

i = freespace(sheet)

    Cells.Select
    sheet.Sort.SortFields.Clear
    sheet.Sort.SortFields.Add Key:=Range("b1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With sheet.Sort
        .SetRange Range("A1:B" & i)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

Function freespace(sheet As Worksheet) As Long

sheet.Select
Range("b1").Select


While Selection.Value > ""
Selection(2, 1).Select
'Debug.Print Selection.Address


Wend
freespace = Selection.Row
End Function
Sub copy_values(sheet1 As Worksheet, sheet2 As Worksheet)

i = freespace(sheet1) - 1
    sheet1.Select
    Range("A2:B" & i).Select
    Selection.Copy
   
    sheet2.Select
j = freespace(sheet2)
    Range("A" & j).Select
    sheet2.Paste

End Sub

Sub copy_sheets(sheet1 As Worksheet, sheet2 As Worksheet, sheet3 As Worksheet)
i = sheet1.Index
j = sheet2.Index

For s = i To j
copy_values Sheets(s), Sheets("summary")
Next s

sort_all sheet3

End Sub
Sub sumation()
copy_sheets Sheets("sheet1"), Sheets("sheet2"), Sheets("summary")

i = freespace(Sheets("summary")) - 1

For j = 1 To i
Range("c" & j).Value = "=sumif(b:b,b" & j & ",a:A)"
Next j

'do cut and paste_special- force values only
Range("a1:c" & i).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'remove column"a"
Range("a:a").Select
Selection.Delete
Stop
'remove duplicates
j = 1
While j < i
    If Range("a" & j).Value = Range("a" & j + 1).Value Then
        Range(j + 1 & ":" & j + 1).Delete
        i = i - 1
    Else
        j = j + 1
    End If
Wend

End Sub

---

call using sumation with the sheet names or sheet index

sumation sheet1,sheet2,summationsheet

process will produce sumation of types in 2 columns at head of summationsheet

you may need to modify to make pretty

notes available (if needed)

here to help
do not forget to rate post that are helpful and close the thread using tools from the top bar

thanks for the code but i just couldn't get it to work. could you put it into a working excel file to show me a working example please? thanks,

what are the names of your sheets?

here to help

sheet1, sheet2, and sheet3

so youo have data on sheet1 sheet2 and sheet3 and you want ot do summations on new sheet or

you have data on sheet1 and sheet2 and want summary on sheet3

which?

here to help

the 2nd..

I want summary on sheet 3, and data is on sheets 1 and 2,

ok!

so you can rename sheet3 to summary

and run the macro "summation"

and it will work

You need to understand a little more about the code to other things

here to explain