Summary sheet from data in other sheets

[FIREBABE]

Wondering if anyone can help me create a formula, command and/or macro for this problem...

I have several sheets within one excel document - each has several columns of data pertaining to customers. Particularly, I would like to list all the customers (multiple duplicate listings) from three separate sheets, only one time, on a fourth sheet, along with the sum of the values attached to those customers - in other words like a summary - how much does each customer owe in total?

FOR EXAMPLE:
Sheet 1:
A1 SMITH_______H1 $3.00
A2 JACKSON_____H2 $0.00
A3 SMITH_______H3 $1.00
A4 GONZALEZ____H4 $0.00
A5 PEREZ________H5 $0.00

Sheet 2:
A1 JACKSON______H1 $3.00
A2 GARCIA_______H2 $1.00
A3 SMITH________H3 $0.00
A4 GARCIA_______H4 $4.00
A5 GOLDSTEIN____H5 $1.00

Given this example, I would like Sheet 3 to have an alphabetical list of the names listed only once with the TOTAL amount they each owe.

A1 GARCIA________B1 $5.00
A2 GOLDSTEIN_____B2 $1.00
A3 GONZALEZ______B3 $0.00
A4 JACKSON_______B4 $3.00
A5 PEREZ_________B5 $0.00
A6 SMITH_________B6 $4.00

Is this possible?

Also, if I keep adding customers to either sheet 1 or sheet 2, as well as make any changes in amounts owed, I would like sheet 3 to reflect those changes by adding the new customers and adding or subtracting the proper amounts as well.

Am I asking for too much?

I hope I didn't confuse anyone.

I've tried building some functions, but did not get the result I wanted.

Thank you sooooo much in advance.

 

[rallcorn]

Since I'm a pivot table addict, I'd do it with a pivot table using multiple consolidation ranges (sheet 1 & sheet 2), no page fields & sort the row labels ascending.

Then any time you refresh the pivot table you'll automatically pick up any changes in amts owed and also any customer additions (or deletions).

 

[jbeaucaire]

1) If there are TITLES in row1 on each of your data sheets so the data actually starts at row 2, this macro will work as is.

If there are no titles, adjust the A2 references to A1

2) In sheet 3, put NAME in A1 and QTY or TOTAL in B1.

3) Right-click the sheet tab and select VIEW CODE

4) Paste in this activation macro:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Range("A2:B" & Rows.Count).Clear

For Each ws In Worksheets
    If ws.Name <> Me.Name Then
        LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        ws.Range("A2:A" & LR & ",H2:H" & LR).Copy _
            Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next ws

'Consolidate copied data
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("C1") = "Total"
    With Range("C2:C" & LR)
        .FormulaR1C1 = _
            "=IF(COUNTIF(R1C1:RC1,RC1)=1, SUMIF(C1, RC1,C2 ), """")"
        .Value = .Value
        .Style = "Currency"
    End With
    
    With Range("C1:C" & LR)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="="
        .Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
        .AutoFilter
    End With
    Columns("B:B").Delete Shift:=xlToLeft
    Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    Columns.AutoFit
    Beep
    
Application.ScreenUpdating = True
End Sub

5) Close the VBEditor and save your workbook as a macro-enabled workbook.

========

Now switch to your data sheets and make some edits. When you bring up the summary sheet, the activation macro will collect all the data immediately and present the summary to you. A "Beep" will remind you the data just updated.

 

[FIREBABE]

OMG!!! How did I not know about pivot tables before? I think I'm on my way to being an addict too. Excel will never be the same for me. I am totally geeking out. LOL.
Thanks so much for the reply.

 

[FIREBABE]

Haven't had a chance yet to check out jbeaucaire's solution. That's next...if I can stop using pivot tables for everything. LOL.
Thanks again for the replies.

 

[rallcorn]

Welcome to the geek club! I sent you a private message.

 

[cimple]

hii and how to subtract these 2 sheets

 

[cimple]

hi all is excellant but i want to subtract these sheet no add plzz reply

 

[jbeaucaire]

Cimple,

Start a thread of your own and ask your question from beginning to end with all details as if we don't know what's on your mind, because we don't.

 

[cimple]

I have several sheets within one excel document - each has several columns of data pertaining to products. Particularly, I would like to list all the products (multiple duplicate listings) and want to know what is the stock

FOR EXAMPLE:
Sheet 1: = SALES
A1 PRODUCT 1_______E1 1000
A2 PRODUCT 2_____ E2 500
A3 PRODUCT 2_______E3 700
A4 PRODUCT 3____ E4 200
A5 PRODUCT 4_______E5 400

Sheet 2: = PURCHASE
A1 PRODUCT 1______ E1 1200
A2 PRODUCT 2_______E2 500
A3 PRODUCT 2 _______E3 800
A4 PRODUCT 3_______E4 100
A5 PRODUCT 4____ E5 400

A6 PRODUCT 5 ------ E6 300
Given this example, I would like Sheet 3 to have an alphabetical list of the names listed only once with the BAL REMAIN OF AMOUNT

A1 PRODUCT 1________B1 200
A2 PRODUCT 2_____B2 100
A3 PRODUCT 3______B3 -100 MINUS DIGITS IN RED FONTS COLOUR
A4 PRODUCT 4_______B4 0

A5 PRODUCT 5 ----- B5 300


Also, if I keep adding PURDUCTS to either sheet 1 or sheet 2, as well as make any changes in UNIT SALE OR PURCHASE, I would like sheet 3 to reflect those changes by adding the new PRODUCT and adding or subtracting the proper amounts as well. I WANT PURCHASE - SALE IN SAME FORMAT U GAVE ANSWER ABOVE
thanks a lot for ur reply