contiue sum based on a row and header

[dvsdasari]

"Hi,

I have the following problem, here we need to sum continuously find the duplicates in MP and get sum based on unique MP code on header.

511001 511105

MP BASIC HOUSE RENT ALLOWANCE

115 1 2

115 1 2

115 1 2

115 1 2

115 1 2

181 1 2

181 1 2

191 1 2


Result should be


ACCOUNT NUMBER AMOUNT DESCRIPTION

H115511001 5 Basic May-2011

H181511001 2 Basic May-2011

H191511001 6 Basic May-2011


after that the next header like 511105 or HRA (with the same departments).

Here "H" is common letter


Please help me, your help is most appreciate.

 

[Luke M]

You're post didn't come out very clear, can you try to explain further? (like, where did the date of May come from? How do columns line up? where does teh "11001" in account number come from?)


It sounds like the 2 basic questions are:

1) How to generate list of unique account numbers from list that contains duplicates?

2) How to get sum of (something...unclear of what) based on account number?


Rough draft of answer, you can get unique numbers quickly using the AdvancedFilter, and then concatenating on the other info (like the H and 11001). Then, you can do a SUMIF possibly, like:

=SUMIF(B:B,VALUE(MID(AccountNumberCell,2,3)))

 

[dvsdasari]

Hi Luke,


Thanks for your reply. Sorry for the confussion.


1. Each employee comes under different dep (like 115,181,191). We need to get unique depts from the list.


Finally.....


We need Basic for all depts Like....

Dept Basic BasicCode

115 10 511001

115 10

115 10

181 20

181 20

181 20

Result -- H11551101 30

H181551001 60

Dept HRA BasicCode

115 10 511105

115 10

115 10

181 20

181 20

181 20

Result -- H11511105 30

H18151105 60


Like the same all headers will come automatically (May is just a example i.e, narration for the month)


Hope you understand the criteria.


Thanks for your reply agian and awaiting for your reply. You need more clarity i'll send the worksheet to you.


Please pody yout email ID here (if you don't mind).


Thanks-Sekhar

 

[Luke M]

Side thought...would a PivotTable work for you? Based on how your data is laid out, the easiest way to get the sums you want, with only uniques, would be to use a PT. You could have the BasicCode as left most column in the Row Area, followed by Dept (Causing it to list unique depts for each BasicCode, and then do a sum in the data area.


I understand it's not the answer to your exact question, but maybe it's an acceptable alternative?