# Party monthly unique billing count with unique item

#### deepak30

##### New Member
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

#### Attachments

• sales bills-page-001.jpg
67 KB · Views: 4

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### AhoyNC

##### Well-known Member
Try:

Book1
ABCD
1Cust.ItemCust.Count
21231 shirt1231
31231 shirt3342
43341 shirt
53341 pant
63341 pant
Sheet2
Cell Formulas
RangeFormula
D2:D3D2{=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

##### New Member
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

##### Well-known Member
Hopefully someone can help you with a VBA solution, but here is a formula way that may work.

Book1
ABCDE
1Cust.ItemAmountCountTotal
21231 shirt5.75111.75
31231 shirt6.00
43341 shirt12.00232.25
53341 pant15.00
63341 pant5.25
Sheet4
Cell Formulas
RangeFormula
D2:D6D2=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:E6E2=IF(COUNTIF(\$A\$2:A2,A2)=1,SUMIF(\$A\$2:\$A\$6,\$A2,\$C\$2:\$C\$6),"")

#### deepak30

##### New Member
RangeBill NoBill 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)
A126159031181881100400
A2261590318188100
A3261590318188100
A4261590374482150
A5261590374503150
A626225922622621200200
A7262259262262200
A829686133740041200200
A931356494346691200400
A103135649347402200

Replies
22
Views
905
Replies
4
Views
75
Replies
1
Views
72
Replies
1
Views
114
Replies
0
Views
61