RedM Posted June 12, 2009 Share Posted June 12, 2009 I have two columns. One is for finished products, the other lists the components that appear in the products listed in col 1. eg. BOX1 Part 1 BOX1 Part 2 BOX1 Part 3 BOX2 Another Part 1 BOX2 Another Part 2 BOX2 Part 2 etc etc. The finished products in 1 can have between 2 and 20 components in them and some finished products may contain components from other finished products, as seen in the list above. Now, I want to be able to enter the number of a finished part (BOX X) on another spreadsheet and have it VLOOKUP this sheet and return the identity (Part X) of each component. However, all I get is the first component. EG for BOX2 I'd get 'Another part 1' but not 'Another part 1, Another part 2 and Part 2'. It doesn't list anything else. WHY? Quote Link to comment Share on other sites More sharing options...
SteveR Posted June 12, 2009 Share Posted June 12, 2009 I think Excel's lookup functions are a tad crummy: I think they only work if the list they are looking at is organised alphabetically. Quote Link to comment Share on other sites More sharing options...
RedM Posted June 12, 2009 Author Share Posted June 12, 2009 I think Excel's lookup functions are a tad crummy: I think they only work if the list they are looking at is organised alphabetically. I've tried that. It still only returns the first hit when it looks down col1. I need it to keep looking and return what it finds. Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 V-look ups only work on unique identifiers - you know that You can do what you need to do - but it will involve setting it up properly. What you need to use is "H-Lookup" Rather than looking across the columsn you look at the rows - which means you can select the rows you want - hence why you have to set the spreadsheet up in advance See attached it may well explain better In the data tab - this is where you need to store the information - so you will need to set it up this way In the Hlookup tab - you have to build the formulas across the columns as I have done - with vlook up you select the column number, in this formula you set the row number, and you just make sure that formula is set up across the spreadsheet properly In my example if you change the product id to "Box 2" - it will change the part numbers as you require I hope this helps Quote Link to comment Share on other sites More sharing options...
SteveR Posted June 12, 2009 Share Posted June 12, 2009 Evening Sabrina - I thought you'd abandoned these parts? Is is your birthday today? Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 It is indeed my birthday Abandoned the club? Never. Quote Link to comment Share on other sites More sharing options...
SteveR Posted June 12, 2009 Share Posted June 12, 2009 It is indeed my birthday Abandoned the club? Never. Cool, get out and on the beers then! On reflection, I dunno where I got that idea from as I was only readin your number plate scandal thread the other day. Ignore me! Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 I don't know about you but I can't open the xls on here So screen shots it is Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 Cool, get out and on the beers then! On reflection, I dunno where I got that idea from as I was only readin your number plate scandal thread the other day. Ignore me! No not tonight. Tomorrow is London Zoo and stay over in the City with BF and Sunday through to Tuesday evening is Alton Towers with me and my best mate Tonight is the eve before the storm............ Quote Link to comment Share on other sites More sharing options...
Homer Posted June 12, 2009 Share Posted June 12, 2009 It works fine here Sabrina. Very useful as well! Alton towers, you lucky devil, have a good one Quote Link to comment Share on other sites More sharing options...
SteveR Posted June 12, 2009 Share Posted June 12, 2009 Tonight is the eve before the storm............ ok, have it your way - excel help on your birthday it is then! Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 It works fine here Sabrina. Very useful as well! Alton towers, you lucky devil, have a good one I'm sure there's another excel guru on here who will tell me there is an easier way of doing this - but this is how I do it I'm so looking forward to AT....... Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 ok, have it your way - excel help on your birthday it is then! I can't resist a bit of excel logic, love it... Quote Link to comment Share on other sites More sharing options...
SteveR Posted June 12, 2009 Share Posted June 12, 2009 I can't resist a bit of excel logic, love it... I used to think that, until I inherited the development of the company reporting "system" Quote Link to comment Share on other sites More sharing options...
how_supra Posted June 12, 2009 Share Posted June 12, 2009 Ah well, thats way outside my patience too...............talk to me about data cubes, XOLAP, Paralel SQL Data loads, and I go blank...... Quote Link to comment Share on other sites More sharing options...
JustGav Posted June 12, 2009 Share Posted June 12, 2009 .... with BF..... This isn't special cuddles boy I hope [hijack over] Quote Link to comment Share on other sites More sharing options...
RedM Posted June 12, 2009 Author Share Posted June 12, 2009 Thanks Sabrina. That'll work although it requires tinkering with every time a product is updated or a a new product is introduced. The list we're working from is produced from ODBC. Is there a way of getting that to output the result horizontally rather than vertically? Quote Link to comment Share on other sites More sharing options...
Jake Posted June 13, 2009 Share Posted June 13, 2009 This any use ? .... Quote Link to comment Share on other sites More sharing options...
RedM Posted June 13, 2009 Author Share Posted June 13, 2009 Nice one, Jake. That looks a hell of a lot like what we need. I'm going to have to sit and work out how that all works over the weekend. Thank you very much. Quote Link to comment Share on other sites More sharing options...
Jake Posted June 13, 2009 Share Posted June 13, 2009 Nice one, Jake. That looks a hell of a lot like what we need. I'm going to have to sit and work out how that all works over the weekend. Thank you very much. No probs mate. Actually I notice there's a mistake with the formula. The part here ... =IF(COUNTIF(DataSheet!$A$1:$A$100,$A2)>=COLUMNS($A:A),INDEX(DataSheet!$B$1:$B$100,SMALL(IF(DataSheet!$A$1:$A$100=$A2,ROW($A$2:$A$101),""),COLUMNS($A:A))),"") should be ... =IF(COUNTIF(DataSheet!$A$1:$A$100,$A2)>=COLUMNS($A:A),INDEX(DataSheet!$B$1:$B$100,SMALL(IF(DataSheet!$A$1:$A$100=$A2,ROW($A$1:$A$100),""),COLUMNS($A:A))),"") NB: it's an array formula so you have to press Ctrl Shift Enter when you've finished editing it - you tell if you did it right because it will have curly braces around the formula like this {=IF(COUNTIF(DataSheet!$A$1:$A$100,$A2)>=COLUMNS($A:A),INDEX(DataSheet!$B$1:$B$100,SMALL(IF(DataSheet!$A$1:$A$100=$A2,ROW($A$1:$A$100),""),COLUMNS($A:A))),"")} (You mustn't enter the curly braces manually) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.