I have created 6 row
BILL NO. ITEM NAME ITEM VALUE Count Bill No Count Item Sum Value
123 AAA 50 1 1 100
123 AAA 50
123 BB 50 2
133 AA 50 2 1 50
133 AA 50
134 AA 50 3 1 50
1. Fill Series of (Count Item Column) with 1,2,3 till bill no is same also it should look for only unique item name, duplicate should not be counted
2. if Bill No. Changes then (Count Item Column) fill series should start from 1
3. I want to sum amount but criteria will Bill No wise plus unique Item Name (it item is repeated in same bill no it should not be valued in sum)
Please up load an example workbook rather than paste in text, it makes it much easier to review
Purfleet
Hi Deepak,
Please refer to this https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first
Please supply a clear explanation of your problem and include a workbook and some examples of the expected result(s). From your initial post I am not clear at all on what you are after or how to implement a solution.
Regards
Phil
1.In Col E if I have four bill No mention in column B then it should give result in col E with unique No. as mention below
2. In Col F it should serial No.( items in a bill No.) and if bill no changes then it should start with 1
3 In Col G it should sum amount of only Bill Wise (col A) Unique Item wise (column B) and give total in col G
Hope you will be able to understand my problem now
| S.N. (Column A) |
B NO. Column B) |
Item Code (Column C) |
Item Amount (Column D) |
Col E (Count of Col B and Fill Series) | Col F | Col G | Col H |
| 1 | 2615903 | MRKU55 18188 | 2240 | 1 | 1 | 4240 | |
| 2 | 2615903 | MRKU55 18188 | 2240 | ||||
| 3 | 2615903 | MRKU55 18188 | 2240 | ||||
| 4 | 2615903 | MRSU39 74448 | 2000 | 2 | |||
| 5 | 2615903 | MRSU39 74448 | 2100 | ||||
| 6 | 2615903 | MRSU39 74448 | 2100 | ||||
| 7 | 2622592 | TCNU28 62226 | 2300 | 2 | 1 | 2300 | |
| 8 | 2622592 | TCNU28 62226 | 2300 | ||||
| 9 | 2968613 | MSKU97 81297 | 2240 | 3 | 1 | 2240 | |
| 10 | 2968613 | MSKU97 81297 | 2240 | ||||
| 11 | 2968613 | MSKU97 81297 | 2240 | ||||
| 12 | 2968613 | MSKU97 81297 | 2240 | ||||
| 13 | 2968613 | MSKU97 81297 | 2240 | ||||
| 14 | 3136549 | SUDU68 44585 | 2500 | 4 | 1 | 2500 | |
| 15 | 3136549 | SUDU68 44585 | 2500 |
Hi Deepak,
Without your workbook we have to enter everything by hand. That not only takes time, it makes things prone to error, and it means we are entering data into rows, columns, sheets, tables etc which probably do not accurately reflect your own data.
Which means that when we come up with a solution, it will probably have to be modified to fit your exact situation.
Our time is limited so, can you please attach a workbook with your data as this will mean we can devote more time to helping you find a solution and be able to help others too.
Phil
So you want column W to display the total of column O multiplied by the number in V?
If so try =IF($V2<>"",SUMPRODUCT(--($V$2:$V$16<>"")*($O$2:$O$16)*($A$2:$A$16=$A2)),"")
Purfleet
Thanks sir for the reply
now this is actually I wanted
1. Column A consist bill no and column G consist item So, it will me give me unique Serial of both (Column A and Column G) in Column V
i.e A2 value is from A2 to A7 and in column G it has found only 2 unique value in G2 and G5 so column V2 value is 1 and column V5 value is 2
A8 value is from A8 to A9 and in column G it has found only 1 unique value in G8 so column V8 value is 1
A10 value is from A10 to A14 and in column G it has found only 1 unique value G10 so column V10 value is 1
A15 value is from A15 to A16 and in column G it has found only 1 unique value G15 so column V14 value is 1
2 Column W will sum only column O of the unique value Column A and column G
i.e A2 value is from A2 to A7 and in column G it has found only 2 unique value in G2 and G5 so it will sum value mention in O2+O5 in column W1 "Rs.44800
A8 value is from A8 to A9 and in column G it has found only 1 unique value in G8 so it will sum value mention in O8 in column W8 "Rs.22400
A10 value is from A10 to A14 and in column G it has found only 1 unique value in G10 so it will sum value mention in O10 in column W10 "Rs.22400
A15 value is from A15 to A16 and in column G it has found only 1 unique value in A15 so it will sum value mention in O15 in column W15 "Rs.22400
I thought that is what i done, except the total showed where there was any number (so 2645903 showed at 1 & 2 (rows 2 & 5).
If you only want it showing at the 1's you can change the formula to
=IF($V2=1,SUMPRODUCT(--($V$2:$V$16<>"")*($O$2:$O$16)*($A$2:$A$16=$A2)),"")
Is that it?
Purfleet
Yes got the 2nd point answer but what about 1st point
1. Column A consist bill no and column G consist item So, it will me give me unique Serial of both (Column A and Column G) in Column V
i.e A2 value is from A2 to A7 and in column G it has found only 2 unique value in G2 and G5 so column V2 value is 1 and column V5 value is 2
A8 value is from A8 to A9 and in column G it has found only 1 unique value in G8 so column V8 value is 1
A10 value is from A10 to A14 and in column G it has found only 1 unique value G10 so column V10 value is 1
A15 value is from A15 to A16 and in column G it has found only 1 unique value G15 so column V14 value is 1
Hello Deepak,
In order to keep things simple I have added a helper column as a base to count unique TEU per SB NO.
See attached file (a modified copy of the file Purfleet uploaded).
Br,
Anders
Great Sir Mr.Anders for the help its working
Great, glad I could give some help.
