Need Formula based solution

L

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. 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

Sr. No. DescriptionOther DetailsDimensions (inches)Dimensions (Feet)AreaUnitsQty. Prodn CostService CostG. TOTAL (Rs.)
Article CodeWidthHeightWidthHeightTotal Sq FtRate (Rs.)Amount (Rs.)Tax %Total Amt. (Rs.)Article CodeSERVICE ELEMENTRate (Rs.)Amount (Rs.)Tax %Total Amt. (Rs.)
1720101774 32.062.02.75.213.8sq.ft.1 13.785006888.95.00% 7,233.33720102379 568.915.00% 79.22 7,312.56
2720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
3720101782 24.024.02.02.04.0sq.ft.6 24.0012288.05.00% 302.40720102379 5120.015.00% 138.00 440.40
4720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
5720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
6720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
7720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
8720101779 24.024.02.02.04.0sq.ft.12 48.001004800.05.00% 5,040.00720102379 5240.015.00% 276.00 5,316.00
9720101774 32.062.02.75.213.8sq.ft.1 13.785006888.95.00% 7,233.33720102379 568.915.00% 79.22 7,312.56
10720101774 32.062.02.75.213.8sq.ft.1 13.785006888.95.00% 7,233.33720102379 568.915.00% 79.22 7,312.56
Result Summary required
Example of the result
Article CodeSq.ftCost
From Column B72010177441.3321700
From Column B72010177928828800
From Column B72010178224288
From Column Q720102379353.332031.67
Removes duplicates and Return Unique Values from Column B and Column QReturns Total Sum of the article code from column LReturns 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>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
L

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

Pls help me someone....need solution desparately!!!!
 
A

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

try this


Excel 2012
ABCD
14Result Summary required
15Example of the result
16Article CodeSq.ftCost
17From Column B72010177441.3420666.7
18From Column B72010177928828800
19From Column B72010178224288
2000
21From Column Q720102379353.342031.66
2200
Sheet1
Cell Formulas
RangeFormula
C17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$L$3:$L$12),SUMIF($B$3:$B$12,B17,$L$3:$L$12))
C21=IF(ISERROR(MATCH(B21,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B21,$L$3:$L$12),SUMIF($B$3:$B$12,B21,$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$V$3:$V$12),SUMIF($B$3:$B$12,B17,$N$3:$N$12))
D21=IF(ISERROR(MATCH(B21,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B21,$V$3:$V$12),SUMIF($B$3:$B$12,B21,$N$3:$N$12))
B17{=IFERROR(INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$16:B16, $B$3:$B$12), 0)),"")}
B21{=IFERROR(INDEX($Q$3:$Q$12, MATCH(0, COUNTIF($B$20:B20, $Q$3:$Q$12), 0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
L

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

AlanY said:
try this

Excel 2012
ABCD
14Result Summary required
15Example of the result
16Article CodeSq.ftCost
17From Column B72010177441.3420666.7
18From Column B72010177928828800
19From Column B72010178224288
2000
21From Column Q720102379353.342031.66
2200

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
C17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$L$3:$L$12),SUMIF($B$3:$B$12,B17,$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$V$3:$V$12),SUMIF($B$3:$B$12,B17,$N$3:$N$12))
C21=IF(ISERROR(MATCH(B21,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B21,$L$3:$L$12),SUMIF($B$3:$B$12,B21,$L$3:$L$12))
D21=IF(ISERROR(MATCH(B21,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B21,$V$3:$V$12),SUMIF($B$3:$B$12,B21,$N$3:$N$12))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B17{=IFERROR(INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$16:B16, $B$3:$B$12), 0)),"")}
B21{=IFERROR(INDEX($Q$3:$Q$12, MATCH(0, COUNTIF($B$20:B20, $Q$3:$Q$12), 0)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...
HI
Thank you so much for the response this is working in a way
However I would need 1 single formula in a cell that determines unique values from 2 columns and return the values. Here I have to paste 2 formulas.
(Basically the formula should first check unique value from column and return it when no further values are there is returns values from column Q)
This has to be done with 1 formula which I can copy paste down.

Thanks for your help and support.
 
A

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Re: Urgent- Need Help- Need Formula based solution

ok, try this


Excel 2012
ABCD
15Example of the result
16Article CodeSq.ftCost
17From Column B72010177441.3420666.7
18From Column B72010177928828800
19From Column B72010178224288
20720102379353.342031.66
Sheet1
Cell Formulas
RangeFormula
C17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$L$3:$L$12),SUMIF($B$3:$B$12,B17,$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$V$3:$V$12),SUMIF($B$3:$B$12,B17,$N$3:$N$12))
B17{=IFERROR(IFERROR(INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$16:B16, $B$3:$B$12), 0)), INDEX($Q$3:$Q$12, MATCH(0, COUNTIF($B$16:B16, $Q$3:$Q$12), 0))), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
L

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

AlanY said:
ok, try this

Excel 2012
ABCD
15Example of the result
16Article CodeSq.ftCost
17From Column B72010177441.3420666.7
18From Column B72010177928828800
19From Column B72010178224288
20720102379353.342031.66

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
C17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$L$3:$L$12),SUMIF($B$3:$B$12,B17,$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,$B$3:$B$12,0)),SUMIF($Q$3:$Q$12,B17,$V$3:$V$12),SUMIF($B$3:$B$12,B17,$N$3:$N$12))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B17{=IFERROR(IFERROR(INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$16:B16, $B$3:$B$12), 0)), INDEX($Q$3:$Q$12, MATCH(0, COUNTIF($B$16:B16, $Q$3:$Q$12), 0))), "")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...


Hey Thanks,
Just one bit of help required.
When I am copying the formula down after B17 then after all unique values in column B which has approx 50 rows (currently we are using only 10 rows) it return 0 and then the column q values.
Is there any way I can get serial wise information without the 0
Example is below

Article Code
720101774
720101779
720101782
0
720102379

<colgroup><col></colgroup><tbody>
</tbody>
 
A

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Re: Urgent- Need Help- Need Formula based solution

one way to rid of the 0 is to match the exact no of rows to the ranges $B$3:$B$12 & $Q$3:$Q$12.

e.g. for 100 rows, the ranges should be $B$3:$B$102 & $Q$3:$Q$102 etc
 
L

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

I have done the same mapped the required ranges incase any data is filled and in doing so this gives the 0 value.
If we are keeping only till value entered it works like a charm but in a given range if blank cells it returns 0.
Can you sort of make addition in the formula that if it encounters blank cell in range B then it automatically start reference from range Q .

Thank you so much for your patience and support.
 
A

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

this is another work around, fill the empty cells with *.

before


Excel 2012
ABCDEF
1Sr. No.DescriptionOther DetailsDimensions (inches)
2Article CodeWidthHeightWidthHeightTotal Sq Ft
317201017743262
427201017792424
537201017822424
647201017792424
757201017792424
862424
972424
1082424
1193262
12103262
13
14Result Summary required
15Example of the result
16Article CodeSq.ftCost
1772010177413.786888.9
1872010177914414400
1972010178224288
20000
21From Column Q720102379353.342031.66
Sheet1


after


Excel 2012
ABCDE
1Sr. No.DescriptionOther DetailsDimensions (inches)
2Article CodeWidthHeightWidthHeight
3172010177432
4272010177924
5372010178224
6472010177924
7572010177924
86*24
97*24
108*24
119*32
1210*32
13
14Result Summary required
15Example of the result
16Article CodeSq.ftCost
1772010177413.786888.9
1872010177914414400
1972010178224288
20720102379353.342031.66
21From Column Q00
Sheet1
Cell Formulas
RangeFormula
C17=IF(ISERROR(MATCH(B17,Sheet1!$B$3:$B$12,0)),SUMIF(Sheet1!$Q$3:$Q$12,B17,Sheet1!$L$3:$L$12),SUMIF(Sheet1!$B$3:$B$12,B17,Sheet1!$L$3:$L$12))
D17=IF(ISERROR(MATCH(B17,Sheet1!$B$3:$B$12,0)),SUMIF(Sheet1!$Q$3:$Q$12,B17,Sheet1!$V$3:$V$12),SUMIF(Sheet1!$B$3:$B$12,B17,Sheet1!$N$3:$N$12))
B17{=IFERROR(IFERROR(INDEX(Sheet1!$B$3:$B$8, MATCH(0, COUNTIF($B$16:B16, Sheet1!$B$3:$B$8), 0)), INDEX(Sheet1!$Q$3:$Q$8, MATCH(0, COUNTIF($B$16:B16, Sheet1!$Q$3:$Q$8), 0))), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
L

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
Re: Urgent- Need Help- Need Formula based solution

OMG it works like a charm... I am so obliged to you..I love you man..

Thank you so much dear friend.

I am short of words to thank you enough.

Thanks for your patience and appreciate the time you have dedicated towards for my queries
 
You must log in or register to reply here.

Similar threads

B
  • Solved
Average based on time
2
Replies
11
Views
599
baqer
B
S
  • Question
Help Needed with Excel
Replies
2
Views
328
Skybluekid
Skybluekid
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,836
Messages
5,833,908
Members
430,243
Latest member
madcock83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top