Party monthly unique billing count with unique item

[deepak30]

Hi
i have made a sheet where A row is Bill No. and B Row is item code i am not concern about how much amount is been charges of items
I have made datasheets like if A party has purchase 2 shirts in a bill I have made 2 entry in a single bill like
A Row B Row
123 1 shirt
123 1 shirt
334 1 shirt
334 1 pant
334 1 pant
this is the only way we do our entry now I want to unique count in A row as well as B row i.e as per above example I should get value as such
C Row D Row
123 1
334 2

Can any one help me please I have uploaded sample file also

 

[AhoyNC]

Try:

Book1
	A	B	C	D
1	Cust.	Item	Cust.	Count
2	123	1 shirt	123	1
3	123	1 shirt	334	2
4	334	1 shirt
5	334	1 pant
6	334	1 pant
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	{=SUM(IF(FREQUENCY(IF($A$2:$A$6=$C2,MATCH($B$2:$B$6,$B$2:$B$6,0)),ROW($B$2:$B$6)-ROW($B$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[deepak30]

That's Great. Thanks for your guidance. function is working absolutely fine. as per my question
I am trying to learn and created a macro's where pdf will be converted to word and there it will pickup only specific data and all tables from word file into excel
table as attached earlier its been done but i am stuck up for the reason mention below.

Sir its totally my mistake that i cannot explain to you in my earlier comment
Actually its aRow Party Name, bRow is Item, cRow is Amount

1. When i used this function in "dRow" upto to lastrow with same criteria it should give me value only in d2 ,d4 else if same criteria come then value should be EMPTY
2. Same criteria but it should sum "cRow (amount)" in eRow i.e in e2 it will give me sum of 1 shirt only (result Rs.1) and e4 it should sum 1 pant and 1 shirt (result Rs.2)

This function i want to use in VBA
I hope that this time i am able to put my query in proper way

 

[AhoyNC]

Hopefully someone can help you with a VBA solution, but here is a formula way that may work.

Book1
	A	B	C	D	E
1	Cust.	Item	Amount	Count	Total
2	123	1 shirt	5.75	1	11.75
3	123	1 shirt	6.00
4	334	1 shirt	12.00	2	32.25
5	334	1 pant	15.00
6	334	1 pant	5.25
Sheet4

Cell Formulas
Range	Formula
D2:D6	D2	=IF(COUNTIF($A$2:A2,A2)=1,SUM(IF(FREQUENCY(IF($A$2:$A$6=$A2,MATCH($B$2:$B$6,$B$2:$B$6,0)),ROW($B$2:$B$6)-ROW($B$2)+1),1)),"")
E2:E6	E2	=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$6,$A2,$C$2:$C$6),"")

 

[deepak30]

Range	Bill No	Bill Count (it should fill series and skip if same bill no repeated)	Item Code	Item Count (It Should fill Series and skip if same item is repeated in same bill No)	Item Amount	Amount function (sum amount and skip if same Item is repeated in same Bill)
A1	2615903	1	18188	1	100	400
A2	2615903		18188		100
A3	2615903		18188		100
A4	2615903		7448	2	150
A5	2615903		7450	3	150
A6	2622592	2	62262	1	200	200
A7	2622592		62262		200
A8	2968613	3	74004	1	200	200
A9	3135649	4	34669	1	200	400
A10	3135649		34740	2	200