Table of 1000 of rows and want result as total of each vendor in separate sheet

[Ibrarbutt]

Dear All

please see below a table of 1000 of rows and want result as total of each vendor in separate sheet.

Vendor name	Amount
A	100
B	200
C	300
A	250
A	450
D	450
E	400
G	400
a	200

Result required.

Vendor name	Total Amount
A	Total of A from Above
B	Total of b from Above
C	Total of c from Above
D	Total of d from Above
continue for vendor

 

[etaf]

a pivot table will do that for
also what version of excel do you have

Book4
	A	B	C	D	E	F	G
1	Vendor name	Amount
2	A	100
3	B	200			Row Labels	Sum of Amount
4	C	300			A	1000
5	A	250			B	200
6	A	450			C	300
7	D	450			D	450
8	E	400			E	400
9	G	400			G	400
10	a	200			Grand Total	2750
11
Sheet1

 

[kevin9999]

Would a simple Sumif to what you want?

Book1
	A	B
1	Vendor name	Amount
2	A	100
3	B	200
4	C	300
5	A	250
6	A	450
7	D	450
8	E	400
9	G	400
10	a	200
Sheet1

Book1
	A	B
1	Vendor name	Amount
2	A	1000
3	B	200
4	C	300
5	D	450
6	F	0
7	G	400
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

 

[Ibrarbutt]

a pivot table will do that for
also what version of excel do you have

Book4
	A	B	C	D	E	F	G
1	Vendor name	Amount
2	A	100
3	B	200			Row Labels	Sum of Amount
4	C	300			A	1000
5	A	250			B	200
6	A	450			C	300
7	D	450			D	450
8	E	400			E	400
9	G	400			G	400
10	a	200			Grand Total	2750
11
Sheet1

result table i need to accomulate more details. i dont want to use the pivot table. we using office 365

Vendor name	Total Amount	Contract Value	Balance Contract
A	Total of A from Above ( i want result here)	Fix amount	Contract Value - Total Amount
B	Total of b from Above	Fix amount	Contract Value - Total Amount
C	Total of c from Above	Fix amount	Contract Value - Total Amount
D	Total of d from Above	Fix amount	Contract Value - Total Amount

 

[Ibrarbutt]

Would a simple Sumif to what you want?

Book1
	A	B
1	Vendor name	Amount
2	A	100
3	B	200
4	C	300
5	A	250
6	A	450
7	D	450
8	E	400
9	G	400
10	a	200
Sheet1

Book1
	A	B
1	Vendor name	Amount
2	A	1000
3	B	200
4	C	300
5	D	450
6	F	0
7	G	400
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

Love it. Thank a lot it done.

 

[etaf]

as you have 365
you could use
in column A
=UNIQUE(FILTER(A:A,A:A<>""))
to get all the unique vendor names form your 1000's rows

Book4
	A	B	C	D	E	F	G
1	Vendor name	Amount			Vendor name	Total
2	A	100			A	1000
3	B	200			B	200
4	C	300			C	300
5	A	250			D	450
6	A	450			E	400
7	D	450			G	400
8	E	400
9	G	400
10	a	200
11
Sheet1

Cell Formulas
Range	Formula
E1:E7	E1	=UNIQUE(FILTER(A:A,A:A<>""))
F2:F7	F2	=SUMIF(A:A,E2,B:B)
Dynamic array formulas.

 

[mohadin]

total of each vendor in separate sheet.

Sub test()
    Dim a
    Dim i&
    a = Cells(1).CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .exists(a(i, 1)) Then
                .Add a(i, 1), a(i, 2)
            Else
                .Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 2)
            End If
        Next
        For Each k In .keys
            Sheets.Add.Name = k
            Cells(1, 1).Resize(, 2) = Array(a(1, 1), a(1, 2))
            Cells(2, 1).Resize(, 2) = Array(k, .Item(k))
        Next
    End With
End Sub

 

[Fluff]

we using office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

Another means to the end is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Vendor name"}, {{"Total", each List.Sum([Amount]), type nullable number}})
in
    #"Grouped Rows"

Book7
	A	B	C	D	E
1	Vendor name	Amount		Vendor name	Total
2	A	100		A	800
3	B	200		B	200
4	C	300		C	300
5	A	250		D	450
6	A	450		E	400
7	D	450		G	400
8	E	400		a	200
9	G	400
10	a	200
Sheet1