lesliewheeler
New Member
- Joined
- Dec 1, 2016
- Messages
- 32
- Office Version
- 2013
- Platform
- Windows
I have attached the sample work sheet below have given the detailing for the desired result below the summary.
You can copy paste the below contents
Pls help me need this very urgently need a formula based solution
<colgroup><col><col><col><col><col span="4"><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
You can copy paste the below contents
Pls help me need this very urgently need a formula based solution
| Sr. No. | Description | Other Details | Dimensions (inches) | Dimensions (Feet) | Area | Units | Qty. | Prodn Cost | Service Cost | G. TOTAL (Rs.) | ||||||||||||
| Article Code | Width | Height | Width | Height | Total Sq Ft | Rate (Rs.) | Amount (Rs.) | Tax % | Total Amt. (Rs.) | Article Code | SERVICE ELEMENT | Rate (Rs.) | Amount (Rs.) | Tax % | Total Amt. (Rs.) | |||||||
| 1 | 720101774 | 32.0 | 62.0 | 2.7 | 5.2 | 13.8 | sq.ft. | 1 | 13.78 | 500 | 6888.9 | 5.00% | 7,233.33 | 720102379 | 5 | 68.9 | 15.00% | 79.22 | 7,312.56 | |||
| 2 | 720101779 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 12 | 48.00 | 100 | 4800.0 | 5.00% | 5,040.00 | 720102379 | 5 | 240.0 | 15.00% | 276.00 | 5,316.00 | |||
| 3 | 720101782 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 6 | 24.00 | 12 | 288.0 | 5.00% | 302.40 | 720102379 | 5 | 120.0 | 15.00% | 138.00 | 440.40 | |||
| 4 | 720101779 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 12 | 48.00 | 100 | 4800.0 | 5.00% | 5,040.00 | 720102379 | 5 | 240.0 | 15.00% | 276.00 | 5,316.00 | |||
| 5 | 720101779 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 12 | 48.00 | 100 | 4800.0 | 5.00% | 5,040.00 | 720102379 | 5 | 240.0 | 15.00% | 276.00 | 5,316.00 | |||
| 6 | 720101779 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 12 | 48.00 | 100 | 4800.0 | 5.00% | 5,040.00 | 720102379 | 5 | 240.0 | 15.00% | 276.00 | 5,316.00 | |||
| 7 | 720101779 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 12 | 48.00 | 100 | 4800.0 | 5.00% | 5,040.00 | 720102379 | 5 | 240.0 | 15.00% | 276.00 | 5,316.00 | |||
| 8 | 720101779 | 24.0 | 24.0 | 2.0 | 2.0 | 4.0 | sq.ft. | 12 | 48.00 | 100 | 4800.0 | 5.00% | 5,040.00 | 720102379 | 5 | 240.0 | 15.00% | 276.00 | 5,316.00 | |||
| 9 | 720101774 | 32.0 | 62.0 | 2.7 | 5.2 | 13.8 | sq.ft. | 1 | 13.78 | 500 | 6888.9 | 5.00% | 7,233.33 | 720102379 | 5 | 68.9 | 15.00% | 79.22 | 7,312.56 | |||
| 10 | 720101774 | 32.0 | 62.0 | 2.7 | 5.2 | 13.8 | sq.ft. | 1 | 13.78 | 500 | 6888.9 | 5.00% | 7,233.33 | 720102379 | 5 | 68.9 | 15.00% | 79.22 | 7,312.56 | |||
| Result Summary required | ||||||||||||||||||||||
| Example of the result | ||||||||||||||||||||||
| Article Code | Sq.ft | Cost | ||||||||||||||||||||
| From Column B | 720101774 | 41.33 | 21700 | |||||||||||||||||||
| From Column B | 720101779 | 288 | 28800 | |||||||||||||||||||
| From Column B | 720101782 | 24 | 288 | |||||||||||||||||||
| From Column Q | 720102379 | 353.33 | 2031.67 | |||||||||||||||||||
| Removes duplicates and Return Unique Values from Column B and Column Q | Returns Total Sum of the article code from column L | Returns Sum of all article codes, Condition:-First it checks if the article code in present in column B if yes then returns sum from cloumn N for the the article code. If the code is not in column B then it checks in cloumn Q and returns the sum in column V, else retuns blank value |
<colgroup><col><col><col><col><col span="4"><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
