Which formula is used to get output

B

binubaby

New Member
Joined
Nov 8, 2017
Messages
4
Hi Guys

Here I added my input and output you given, please let me know which formula used to calculate output

Binu Baby

[TABLE="width: 744"]
<colgroup><col><col><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="colspan: 8"] PURCHASE MATERIAL STOCK(in Nos)[/TD]
[/TR]
[TR]
[TD]Purschase Date[/TD]
[TD]Gasket 12"[/TD]
[TD]Gasket 8"[/TD]
[TD]Gasket 6"[/TD]
[TD]8" Elbow 90 DEG[/TD]
[TD]8" Elbow 45 DEG[/TD]
[TD]8" Elbow 30 DEG[/TD]
[TD]6" Elbow 90 DEG[/TD]
[/TR]
[TR]
[TD="align: right"]01-07-2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]02-07-2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]03-07-2017[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]04-07-2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]05-07-2017[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]06-07-2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]07-07-2017[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]08-07-2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]09-07-2017[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10-07-2017[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]11-07-2017[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]12-07-2017[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]13-07-2017[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14-07-2017[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]15-07-2017[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]16-07-2017[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]17-07-2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Require output another sheet :

[TABLE="width: 251"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"] Material[/TD]
[TD="align: center"]Qty[/TD]
[/TR]
[TR]
[TD="align: center"]01-07-2017[/TD]
[TD="align: center"]8" Elbow 90 DEG[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]01-07-2017[/TD]
[TD="align: center"]8" Elbow 45 DEG[/TD]
[TD="align: center"]56[/TD]
[/TR]
[TR]
[TD="align: center"]01-07-2017[/TD]
[TD="align: center"]8" Elbow 30 DEG[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]01-07-2017[/TD]
[TD="align: center"]6" Elbow 90 DEG[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]02-07-2017[/TD]
[TD="align: center"]Gasket 8"[/TD]
[TD="align: center"]34[/TD]
[/TR]
[TR]
[TD="align: center"]02-07-2017[/TD]
[TD="align: center"]8" Elbow 90 DEG[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]02-07-2017[/TD]
[TD="align: center"]8" Elbow 45 DEG[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]02-07-2017[/TD]
[TD="align: center"]8" Elbow 30 DEG[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]02-07-2017[/TD]
[TD="align: center"]6" Elbow 90 DEG[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]03-07-2017[/TD]
[TD="align: center"]Gasket 12"[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]03-07-2017[/TD]
[TD="align: center"]Gasket 8"[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]03-07-2017[/TD]
[TD="align: center"]8" Elbow 90 DEG[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]03-07-2017[/TD]
[TD="align: center"]6" Elbow 90 DEG[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Excel 2010[TABLE="class: grid, width: 700"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]PURCHASE MATERIAL STOCK(in Nos)
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Purschase Date
[/TD]
[TD]Gasket 12"
[/TD]
[TD]Gasket 8"
[/TD]
[TD]Gasket 6"
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]7/1/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]7/2/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]7/3/2017
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]7/4/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]7/5/2017
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]7/6/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]7/7/2017
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]7/8/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]7/9/2017
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"]7/10/2017
[/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"]7/11/2017
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]54
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD="align: right"]7/12/2017
[/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]95
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: right"]7/13/2017
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: right"]7/14/2017
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: right"]7/15/2017
[/TD]
[TD="align: right"]69
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: right"]7/16/2017
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: right"]7/17/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet4


​
Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Date
[/TD]
[TD]Material
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]7/1/2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]7/1/2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD="align: right"]56
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]7/1/2017
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]7/1/2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]7/2/2017
[/TD]
[TD]Gasket 8"
[/TD]
[TD="align: right"]34
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]7/2/2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]7/2/2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]7/2/2017
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]7/2/2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]7/3/2017
[/TD]
[TD]Gasket 12"
[/TD]
[TD="align: right"]23
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"]7/3/2017
[/TD]
[TD]Gasket 8"
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"]7/3/2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD="align: right"]7/3/2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet5​

Copy the formula down
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell
[/TD]
[TD="align: left"]Formula
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2
[/TH]
[TD="align: left"]=INDEX(Sheet4!$B$3:$H$19,MATCH(A2,Sheet4!$A$3:$A$19,0),MATCH(B2,Sheet4!$B$2:$H$2,0))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 
Upvote 0
Thanks to reply

In the previous thread you given me the formula to the calculate particular in column "C" mean while i also need the formula to calculate particular in column "A and B".
my Friend given me a formula, i think it's not successful
Here the formula i tried and value as in table

Cell F24 : INDEX(INPUT!$C$4:$C$20,INT((ROWS($G$24:G24)-1)/5+1))
Cell H24 : INDEX(INPUT!$D$3:$J$3,IF(MOD(ROWS($H$24:H24)-1,5)+1+2=3,1,MOD(ROWS($H$24:H24)-1,5
)+1+2))



0
[TABLE="width: 880, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD][/TD]
[TD="colspan: 2"]Required Output
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Output by our formula
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Material
[/TD]
[TD]Qty
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]Gasket 12"
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD]56
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD]56
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]Gasket 8"
[/TD]
[TD]34
[/TD]
[TD][/TD]
[TD]01-07-2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]Gasket 12"
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]8" Elbow 30 DEG
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]Gasket 12"
[/TD]
[TD]23
[/TD]
[TD][/TD]
[TD]02-07-2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]Gasket 8"
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]Gasket 12"
[/TD]
[TD]23
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]8" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]6" Elbow 90 DEG
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]03-07-2017
[/TD]
[TD]8" Elbow 45 DEG
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for reply

You given me the formula in particular Qty column I require the formula material and date column

My friend tried with formula here below the formula and calculated value, I didn't get the proper output


Cell G24: =INDEX(INPUT!$C$4:$C$20,INT((ROWS($G$24:G24)-1)/5+1))
Cell H24: =INDEX(INPUT!$D$3:$J$3,IF(MOD(ROWS($H$24:H24)-1,5)+1+2=3,1,MOD(ROWS($H$24:H24)-1,5)+1+2))
Cell I24: = INDEX(INDEX(INPUT!$D$4:$J$20,,MATCH(H24,INPUT!$D$3:$J$3,0)),MATCH(G24,INPUT!$C$4:$C$20,0))

[TABLE="width: 880, align: center"]
<tbody>[TR]
[TD]

A​


[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD="align: center"][/TD]
[TD="colspan: 2, align: center"]Required Output
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Output by our formula
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Material
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]8" Elbow 90 DEG
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]Gasket 12"
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]25
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]8" Elbow 45 DEG
[/TD]
[TD="align: center"]56
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]8" Elbow 90 DEG
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]8" Elbow 30 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]8" Elbow 45 DEG
[/TD]
[TD="align: center"]56
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]27
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]6" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]8" Elbow 30 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]28
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]Gasket 8"
[/TD]
[TD="align: center"]34
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]01-07-2017
[/TD]
[TD="align: center"]6" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]29
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]8" Elbow 90 DEG
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]Gasket 12"
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]8" Elbow 45 DEG
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]8" Elbow 90 DEG
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]8" Elbow 30 DEG
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]8" Elbow 45 DEG
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]6" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]8" Elbow 30 DEG
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]Gasket 12"
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]02-07-2017
[/TD]
[TD="align: center"]6" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]34
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]Gasket 8"
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]Gasket 12"
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]35
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]8" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]8" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]36
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]6" Elbow 90 DEG
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03-07-2017
[/TD]
[TD="align: center"]8" Elbow 45 DEG
[/TD]
[TD="align: center"]0
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks to reply

Let me know the formula to calculate particular column A and B (Date and Material)

my friend tried with a formula here the below is not proper working as per my previous thread output

Cell A2: INDEX(INPUT!$C$4:$C$20,INT((ROWS(A2:$A$2)-1)/5+1))
Cell B2: INDEX(INPUT!$D$3:$J$3,IF(MOD(ROWS(B2:$B$2)-1,5)+1+2=3,1,MOD(ROWS(B2:$B$2)-1,5)+1+2))
Cell C2: INDEX(INDEX(INPUT!$D$4:$J$20,,MATCH(B2,INPUT!$D$3:$J$3,0)),MATCH(A2,INPUT!$C$4:$C$20,0))
 
Upvote 0
If you are OK with VBA then this should work. You will need to adjust the sheet names and ranges to match your data. If your actual data has more items you will need to add more if statements to the look.

Code: 
Sub pulldata()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lr As Long
Dim lr2 As Long
Set ws = Sheets("input")
Set ws2 = Sheets("Sheet5") 'change to match your sheet name
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
If lr2 = 1 Then
Else
    ws2.Range("A2:C" & lr2).ClearContents
End If
For x = 4 To lr
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 4) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 4)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 4)
        End If
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 5) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 5)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 5)
        End If
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 6) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 6)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 6)
        End If
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 7) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 7)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 7)
        End If
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 8) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 8)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 8)
        End If
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 9) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 9)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 9)
        End If
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
        If ws.Cells(x, 10) > 0 Then
            ws2.Cells(lr2 + 1, 1) = ws.Cells(x, 3)
            ws2.Cells(lr2 + 1, 2) = ws.Cells(3, 10)
            ws2.Cells(lr2 + 1, 3) = ws.Cells(x, 10)
        End If

Next x
 
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
64
Bernie Galve
B
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
295
Skyybot
Skyybot
ExcelNewbie2020
  • Solved
Extract unique values and sum duplicates
Replies
3
Views
240
alansidman
A

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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
Back
Top