Thanks Bernie & another question

  • Thread starter Thread starter Glen Sharpe
  • Start date Start date
G

Glen Sharpe

Your solution worked great! I'm now trying to come up with a
spreadsheet as follows.
Say I have a group of listings of parts we call assemblies in column
B. These groups would each have a different name such as group1 etc. Each
assembly would consist of 4 different parts in column C.
What I would like to do is enter "1" for assembly 1 or "2" for assembly
2 and have excel find the 4 different parts for this assembly and bring them
into cells d4 thru d8 along with their costs into column e. I've been trying
different functions for this but just can't seem to get it. Thanks!
 
Glen,

Assumptions: Your data table is in columns B, C, and D, with assembly name in B, part name in C, and
price in D, and the data starts in row 2 and continues down to row 1000. Row 1 has headers.

In F4:F7, enter the numbers 1,2,3, and 4.
In G3, enter the assembly name of interest. This must match exactly one of the values in column B.
In G4, array enter this formula (enter using Ctrl-Shift-Enter)

=INDEX(C:C,SMALL(IF(Sheet1!$B$2:$B$1000=$G$3,ROW(Sheet1!$B$2:$B$1000),10000),$F4),1)

Copy this formula, and paste in G4:H7.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumproduct on a variable range 2
Vlookup in multiples columns 1
lookup mutliple values to return one value 1
problem with assembly signing and strong names 1
Group List by Values in Column A and combine related row values inColumn B into 1 Cell 7
Split column contents (de-concantenate) 7
IF statement with CONCATENATE (I think) 2
vba code to filter the data like a group with group header 5

Back
Top