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: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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kilianli" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> kilianli
> ------------------------------------------------------------------------
> kilianli's Profile:
http://www.excelforum.com/member.php...o&userid=32328
> View this thread: http://www.excelforum.com/showthread...hreadid=521294
>

[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

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: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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kilianli" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> kilianli
> ------------------------------------------------------------------------
> kilianli's Profile:
http://www.excelforum.com/member.php...o&userid=32328
> View this thread: http://www.excelforum.com/showthread...hreadid=521294
>

[Max]

"kilianli" wrote:
> 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
> > Select G2:J2, copy down to say, J10
> > to cover the max expected extent of data in col A

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---