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

ABCDEFGH
PURCHASE MATERIAL STOCK(in Nos)
Purschase DateGasket 12"Gasket 8"Gasket 6"8" Elbow 90 DEG8" Elbow 45 DEG8" Elbow 30 DEG6" Elbow 90 DEG
01-07-2017000105655
02-07-2017034010265
03-07-201723205005
04-07-20170402005
05-07-20175601210005
06-07-20170635012455
07-07-20172501401245
08-07-201700003045
09-07-2017470035005
10-07-201765006125
11-07-20173254355528
12-07-20176523951025
13-07-2017240452610
14-07-2017250322015
15-07-20176903222015
16-07-20174704012015
17-07-2017015001010

<colgroup><col><col><col span="2"><col span="4"></colgroup><tbody>
</tbody>


Require output another sheet :

Date MaterialQty
01-07-20178" Elbow 90 DEG10
01-07-20178" Elbow 45 DEG56
01-07-20178" Elbow 30 DEG5
01-07-20176" Elbow 90 DEG5
02-07-2017Gasket 8"34
02-07-20178" Elbow 90 DEG10
02-07-20178" Elbow 45 DEG2
02-07-20178" Elbow 30 DEG6
02-07-20176" Elbow 90 DEG5
03-07-2017Gasket 12"23
03-07-2017Gasket 8"2
03-07-20178" Elbow 90 DEG5
03-07-20176" Elbow 90 DEG5

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Excel 2010
A
B
C
D
E
F
G
H
1
PURCHASE MATERIAL STOCK(in Nos)
2
Purschase Date
Gasket 12"
Gasket 8"
Gasket 6"
8" Elbow 90 DEG
8" Elbow 45 DEG
8" Elbow 30 DEG
6" Elbow 90 DEG
3
7/1/2017
10
56
5
5
4
7/2/2017
34
10
2
6
5
5
7/3/2017
23
2
5
5
6
7/4/2017
4
2
5
7
7/5/2017
56
12
10
5
8
7/6/2017
6
35
12
45
5
9
7/7/2017
25
14
12
4
5
10
7/8/2017
30
4
5
11
7/9/2017
47
35
5
12
7/10/2017
65
6
1
2
5
13
7/11/2017
32
54
35
5
5
2
8
14
7/12/2017
65
23
95
1
2
5
15
7/13/2017
24
45
2
6
1
16
7/14/2017
25
32
2
1
5
17
7/15/2017
69
32
2
20
1
5
18
7/16/2017
47
40
1
20
1
5
19
7/17/2017
1
50
10
1

<tbody>
</tbody>
Sheet4


​
Excel 2010
A
B
C
1
Date
Material
Qty
2
7/1/2017
8" Elbow 90 DEG
10
3
7/1/2017
8" Elbow 45 DEG
56
4
7/1/2017
8" Elbow 30 DEG
5
5
7/1/2017
6" Elbow 90 DEG
5
6
7/2/2017
Gasket 8"
34
7
7/2/2017
8" Elbow 90 DEG
10
8
7/2/2017
8" Elbow 45 DEG
2
9
7/2/2017
8" Elbow 30 DEG
6
10
7/2/2017
6" Elbow 90 DEG
5
11
7/3/2017
Gasket 12"
23
12
7/3/2017
Gasket 8"
2
13
7/3/2017
8" Elbow 90 DEG
5
14
7/3/2017
6" Elbow 90 DEG
5

<tbody>
</tbody>
Sheet5​

Copy the formula down

Worksheet Formulas
Cell
Formula
C2
=INDEX(Sheet4!$B$3:$H$19,MATCH(A2,Sheet4!$A$3:$A$19,0),MATCH(B2,Sheet4!$B$2:$H$2,0))

<tbody>
</tbody>

<tbody>
</tbody>

 
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
A
B
C
D
E
F
G
H
I
22
Required Output
Output by our formula
23
Date
Material
Qty
24
01-07-2017
8" Elbow 90 DEG
10
01-07-2017
Gasket 12"
0
25
01-07-2017
8" Elbow 45 DEG
56
01-07-2017
8" Elbow 90 DEG
10
26
01-07-2017
8" Elbow 30 DEG
5
01-07-2017
8" Elbow 45 DEG
56
27
01-07-2017
6" Elbow 90 DEG
5
01-07-2017
8" Elbow 30 DEG
5
28
02-07-2017
Gasket 8"
34
01-07-2017
6" Elbow 90 DEG
5
29
02-07-2017
8" Elbow 90 DEG
10
02-07-2017
Gasket 12"
0
30
02-07-2017
8" Elbow 45 DEG
2
02-07-2017
8" Elbow 90 DEG
10
31
02-07-2017
8" Elbow 30 DEG
6
02-07-2017
8" Elbow 45 DEG
2
32
02-07-2017
6" Elbow 90 DEG
5
02-07-2017
8" Elbow 30 DEG
6
33
03-07-2017
Gasket 12"
23
02-07-2017
6" Elbow 90 DEG
5
34
03-07-2017
Gasket 8"
2
03-07-2017
Gasket 12"
23
35
03-07-2017
8" Elbow 90 DEG
5
03-07-2017
8" Elbow 90 DEG
5
36
03-07-2017
6" Elbow 90 DEG
5
03-07-2017
8" Elbow 45 DEG
0

<tbody>
</tbody>
 
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))



A​


BC
DEFGHII
22
Required Output
Output by our formula
23
Date
Material
Qty
24
01-07-2017
8" Elbow 90 DEG
10
01-07-2017
Gasket 12"
0
25
01-07-2017
8" Elbow 45 DEG
56
01-07-2017
8" Elbow 90 DEG
10
26
01-07-2017
8" Elbow 30 DEG
5
01-07-2017
8" Elbow 45 DEG
56
27
01-07-2017
6" Elbow 90 DEG
5
01-07-2017
8" Elbow 30 DEG
5
28
02-07-2017
Gasket 8"
34
01-07-2017
6" Elbow 90 DEG
5
29
02-07-2017
8" Elbow 90 DEG
10
02-07-2017
Gasket 12"
0
30
02-07-2017
8" Elbow 45 DEG
2
02-07-2017
8" Elbow 90 DEG
10
31
02-07-2017
8" Elbow 30 DEG
6
02-07-2017
8" Elbow 45 DEG
2
32
02-07-2017
6" Elbow 90 DEG
5
02-07-2017
8" Elbow 30 DEG
6
33
03-07-2017
Gasket 12"
23
02-07-2017
6" Elbow 90 DEG
5
34
03-07-2017
Gasket 8"
2
03-07-2017
Gasket 12"
23
35
03-07-2017
8" Elbow 90 DEG
5
03-07-2017
8" Elbow 90 DEG
5
36
03-07-2017
6" Elbow 90 DEG
5
03-07-2017
8" Elbow 45 DEG
0

<tbody>
</tbody>
 
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

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
235
etaf
etaf
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
369
mrshuck
M
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
337
awoohaw
awoohaw
M
  • Solved
Formula Help.
Replies
3
Views
119
Fluff
Fluff
P
  • Question
User to select data
Replies
2
Views
275
pastorkc
P

Forum statistics

Threads
1,215,743
Messages
6,126,615
Members
449,322
Latest member
Ricardo Souza

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