Thread: Lookup and sum per station

Instructionsdetailed instructions on attachment)

1. Column A lists part numbers
2. Column B lists total quantities of each part number used
3. X1, X2,….. Are stations where the part numbers listed in column A are consumed
4. The tabs at the bottom shows the details of the build for the part numbers in column A
5. I want on sheet 1 in columns (D - I) it will show how many of each part numbers is used in each station

6. Also, want to create a list to check total quantities for each part is being used up in all stations - back check numbers . Thanks.

AS far as I have understood you I have prepared a macro . You experiment this macro on your sample.xls file and check whether the results are what you want then you can use the macro in your original file.

ALWAYS KEEP THE ORIGINAL FILE SAFELY SOMEWHERE SO THAT IT CAN BE RETRIEVED IF THERE IS PROBLEM.

The macro UNDO is embedded in the macro "test". so it is enough if you run the macro "test" only.

Code:

Sub test()
Dim rng As Range, c As Range, x As String, cfind As Range
Dim y As String, c1 As Range, z As Integer, rng2 As Range, cfind1 As Range, xp As String
On Error Resume Next
undo
With Worksheets("sheet1")

Set rng = Range(.Range("c3"), .Range("c3").End(xlDown))
For Each c In rng
x = c.Value
xp = .Cells(c.Row, "a")
For Each c1 In Range(.Range("d2"), .Range("d2").End(xlToRight))
y = c1.Value


With Worksheets(x)
Set cfind = .Cells.Find(what:=y, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
Set rng2 = Range(.Cells(cfind.Row, "B"), .Cells(cfind.End(xlDown).Row, "b"))
Set cfind1 = rng2.Cells.Find(what:=xp, lookat:=xlWhole)
If cfind1 Is Nothing Then GoTo line1
z = cfind1.Offset(0, 3).Value
End With
.Cells(c.Row, c1.Column) = z

line1:
Next c1

Next c
End With
End Sub

Code:

Sub undo()
Dim j As Integer
With Worksheets("sheet1")
j = .Range("a2").End(xlDown).Row
Range(.Cells(3, 4), .Cells(j, "I")).Clear
End With
End Sub

Works but too complicated... I still have to try on the original file. Is there any other way to solve this (like with functions). Thanks.