clever look up please !

Paula F

New Member
Joined
Nov 10, 2005
Messages
17
I'll try to explain what I need.......
I plan a production line. Each product has a set of routers (stages) that need to happen in order to make it, eg dispensary, filling, packaging, micro testing and then shipping.
I have all the products to be made in a month on an excel sheet, in a column (all the part numbers). I will then have all part numbers with their respective router list in another file. I need to have some sort of look up which gives me (on another sheet) the part number on the plan and then the list of routers underneath it, and then the next part number on the plan with it's routers and so on. I can only do basic VLOOKUPS which look for one reference and then report back what is in another column in the same row. How do you say "look for this part number and tell me what is in the (for eg) 8 rows beneath it ?
thanks
Paula
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Possible answer

If you are looking at having the anwsers to the lookup appearing in the same cell please see the attached code.

Place code into the code sheet of the sheet where your partnumbers are listed and change the variables to accommodate. I had "Part Numbers" in column A on a sheet called SHEET 1 referred as "activesheet" and a list of part numbers and routers on SHEET 2 in columns A and B respectively

Hope this helps

Aaron

:oops:

_______________________________________________________________________

Public Sub newmacro()
Dim N_1 As Integer ' USED TO INCREMENT THE ROW NUMBER ON SHEET 2
Dim N_2 As Integer ' USED TO IDENTIFY FIRST RUN OF CODE FOR ITEMS ON SHEET 1
Dim VAL As Variant ' LOOKUP VALUE
Dim VAR As Variant ' ANSWER
Dim X_VAR As Variant ' CUMULATIVE ANSWER
N_2 = 0
While ACTIVESHEETS.Cells(X_ROW, "A") <> 0
N_1 = 1
VAL = ActiveSheet.Cells(X_ROW, "A")
While Sheets("SHEET 2").Cells(N_1, "A") <> 0
If VAL = Sheets("SHEET 2").Cells(N_1, "A") Then
VAR = Sheets("SHEET 2").Cells(N_1, "B")
If N_2 = 0 Then
ActiveSheet.Cells(X_ROW, "B") = VAR
Else
X_VAR = ActiveSheet.Cells(X_ROW, "B") & ", " & VAR
ActiveSheet.Cells(X_ROW, "B") = X_VAR
End If
N_2 = N_2 + 1
End If
N_1 = N_1 + 1
Wend
N_2 = 0
X_ROW = X_ROW + 1
Wend

End Sub
 
Upvote 0
Ignore previous posts

Sorry but I messed up the code a little please see attached

Public Sub newmacro()
Dim N_1 As Integer ' USED TO INCREMENT THE ROW NUMBER ON SHEET 2
Dim N_2 As Integer ' USED TO IDENTIFY FIRST RUN OF CODE FOR ITEMS ON SHEET 1
Dim VAL As Variant ' LOOKUP VALUE
Dim VAR As Variant ' ANSWER
Dim X_VAR As Variant ' CUMULATIVE ANSWER
Dim X_ROW As Integer

N_2 = 0
X_ROW = 1
While ActiveSheet.Cells(X_ROW, "A") <> 0
N_1 = 1
VAL = ActiveSheet.Cells(X_ROW, "A")
While Sheets("SHEET 2").Cells(N_1, "A") <> 0
If VAL = Sheets("SHEET 2").Cells(N_1, "A") Then
VAR = Sheets("SHEET 2").Cells(N_1, "B")
If N_2 = 0 Then
ActiveSheet.Cells(X_ROW, "B") = VAR
Else
X_VAR = ActiveSheet.Cells(X_ROW, "B") & ", " & VAR
ActiveSheet.Cells(X_ROW, "B") = X_VAR
End If
N_2 = N_2 + 1
End If
N_1 = N_1 + 1
Wend
N_2 = 0
X_ROW = X_ROW + 1
Wend

End Sub

My apologies

Aaron
 
Upvote 0
Hi Aaron
thank you very much for your help. Before I try to do this (I am not very good with all this code !), would you be able to tell me how I could get it so that each router is put into its own cell ? eg I would have a part number in one row, then the next however many rows would have the routers in them... then it would be the next part number and then all the routers for that. The reason they have to be in separate rows is because I then need to paste them into Microsoft Project, and it will only take things if they are in separate rows.
thank you again
Paula
 
Upvote 0
Aaron
I put in the code and ran it and it said "subscript out of range"
what does that mean ?

thanks


Paula
 
Upvote 0
Hi Paula

Subscript out of range usually refers to sheet names (run-time error 9).

If you change the SHEET 2 in the code to the sheet where your list of parts and routers is it should be ok.

Aaron
 
Upvote 0

Forum statistics

Threads
1,212,149
Messages
6,106,256
Members
448,010
Latest member
wbarkwell

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