Party monthly unique billing count with unique item

deepak30

New Member
Joined
May 11, 2020
Messages
38
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
    sales bills-page-001.jpg
    67 KB · Views: 4

Some videos you may like

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
Joined
Oct 10, 2011
Messages
4,687
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 11, 2020
Messages
38
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
Joined
Oct 10, 2011
Messages
4,687
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 11, 2020
Messages
38
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,713
Members
412,613
Latest member
EFRATA
Top