Combine Show

[kilianli]

I have make the record as the following
if the acc no. same, then i use"-" instead of the same payee and not
input the total amount, until the last one. then i would like to know
how can i have the reust just have the filed Acc no. , Payee and total
amount
"5687 WW $400"

-input fomat::
Acc no. Payee Dept Amount Total Amout
152 ABC HR $100.00 $100.00
124 AA Account $500.00 $500.00
5687 WW Admin $100.00 -
5687 - IT $100.00 -
5687 - HR $200.00 $400.00
4153 ABC IT $500.00 $500.00

 

[Max]

Here's a play using non-array formulas to auto-extract
the required results into cols adjacent to the source table

A sample construct is available at:
http://savefile.com/files/7237432
Auto-Extract Unique Acc Summary n Payee - Amt.xls

Assume source table in cols A to E, data from row2 down

We'll use adjacent cols G to J

Put labels into H1:J1 : Acc no., Payee, Total Amount

In G2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))

In H2:
=IF(ISERROR(SMALL($G:$G,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:$G,0)))
Copy H2 to I2

In J2: =IF(H2="","",SUMIF(A:A,H2,D))

Select G2:J2, copy down to say, J10
to cover the max expected extent of data in col A

Cols H to J will return the desired results,
all neatly bunched at the top, viz.:

Acc no. Payee Total Amount
152 ABC $100.00
124 AA $500.00
5687 WW $400.00
4153 ABC $500.00

 

[kilianli]

Thank you for your help
but i don't understand how to write......
suppose i need to make the record again and again, is that i just copy
the cell in the following row??
i'm the new user in excel, so i don't know how to do...pls help

 

[Max]

Thank you for your help
but i don't understand how to write......
suppose i need to make the record again and again,
is that i just copy the cell in the following row??

If you're referring to this part of the set-up steps
With the formulas in G2:J2,
just copy down as far as required
to cover what you have in col A

You can actually copy beyond the last row of data in col A
if you think that the data may extend further in future

Download and see the sample file provided earlier,
it shows the implemented construct ..

---