Need Formula based solution

lesliewheeler · Dec 3, 2016

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.		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

lesliewheeler · Dec 8, 2016

Re: Urgent- Need Help- Need Formula based solution

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

AlanY · Dec 8, 2016

Re: Urgent- Need Help- Need Formula based solution

try this

Excel 2012

A

B

C

D

14

Result Summary required

15

Example of the result

16

Article Code

Sq.ft

Cost

17

From Column B

720101774

41.34

20666.7

18

From Column B

720101779

288

28800

19

From Column B

720101782

24

288

20

0

21

From Column Q

720102379

353.34

2031.66

22

0

Sheet1

Cell Formulas
Range	Formula
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.

lesliewheeler · Dec 8, 2016

Re: Urgent- Need Help- Need Formula based solution

AlanY said:
try this

Excel 2012

A B C D
14 Result Summary required
15 Example of the result
16 Article Code Sq.ft Cost
17 From Column B 720101774 41.34 20666.7
18 From Column B 720101779 288 28800
19 From Column B 720101782 24 288
20 0 0
21 From Column Q 720102379 353.34 2031.66
22 0 0

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

Worksheet Formulas

Cell Formula
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

Cell Formula
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>

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.

AlanY · Dec 8, 2016

Re: Urgent- Need Help- Need Formula based solution

ok, try this

Excel 2012

A

B

C

D

15

Example of the result

16

Article Code

Sq.ft

Cost

17

From Column B

720101774

41.34

20666.7

18

From Column B

720101779

288

28800

19

From Column B

720101782

24

288

20

720102379

353.34

2031.66

Sheet1

Cell Formulas
Range	Formula
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.

lesliewheeler · Dec 8, 2016

Re: Urgent- Need Help- Need Formula based solution

AlanY said:
ok, try this

Excel 2012

A B C D
15 Example of the result
16 Article Code Sq.ft Cost
17 From Column B 720101774 41.34 20666.7
18 From Column B 720101779 288 28800
19 From Column B 720101782 24 288
20 720102379 353.34 2031.66

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

Worksheet Formulas

Cell Formula
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

Cell Formula
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>

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

AlanY · Dec 8, 2016

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

lesliewheeler · Dec 8, 2016

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.

AlanY · Dec 8, 2016

Re: Urgent- Need Help- Need Formula based solution

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

before

Excel 2012

A

B

C

D

E

F

1

Sr. No.

Description

Other Details

Dimensions (inches)

2

Article Code

Width

Height

Width

Height

Total Sq Ft

3

1

720101774

32

62

4

2

720101779

24

5

3

720101782

24

6

4

720101779

24

7

5

720101779

24

8

6

24

9

7

24

10

8

24

11

9

32

62

12

10

32

62

13

14

Result Summary required

15

Example of the result

16

Article Code

Sq.ft

Cost

17

720101774

13.78

6888.9

18

720101779

144

14400

19

720101782

24

288

20

0

21

From Column Q

720102379

353.34

2031.66

Sheet1

after

Excel 2012

A

B

C

D

E

1

Sr. No.

Description

Other Details

Dimensions (inches)

2

Article Code

Width

Height

Width

Height

3

1

720101774

32

4

2

720101779

24

5

3

720101782

24

6

4

720101779

24

7

5

720101779

24

8

6

*

24

9

7

*

24

10

8

*

24

11

9

*

32

12

10

*

32

13

14

Result Summary required

15

Example of the result

16

Article Code

Sq.ft

Cost

17

720101774

13.78

6888.9

18

720101779

144

14400

19

720101782

24

288

20

720102379

353.34

2031.66

21

From Column Q

0

Sheet1

Cell Formulas
Range	Formula
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.

lesliewheeler · Dec 9, 2016

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

Need Formula based solution

lesliewheeler

New Member

Excel Facts

lesliewheeler

New Member

AlanY

Well-known Member

lesliewheeler

New Member

AlanY

Well-known Member

lesliewheeler

New Member

AlanY

Well-known Member

lesliewheeler

New Member

AlanY

Well-known Member

lesliewheeler

New Member

Similar threads

Forum statistics

Share this page

Need Formula based solution

New Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock