vlookup

[greg.begin]

i am working on a spreadsheet that is part number based. each part number gets a work order assigned. I use Vlookup or Index/match to find the work order associated. however, sometimes they need to make a new work order and no longer use the first work order, but they need both in view and they can't manually enter them in, it needs to be referenced. Help please? also, too many lines for an array formula unless it can calculate fast.


thank you very much

 

[SirJB7]

Hi, greg.begin!

Consider uploading a sample file. Thanks.

Regards!

 

[greg.begin]

due to the nature of my work, the security paramaters don't allow uploads. sorry, but i can try in here.


part number Work Order

ABC vlookup formula to find first instance

ABC vlookup formula to find second instance.

DEF 100-112


data is on network, but lets say the raw data imported is on the next tab. column A is part number and column B is work order.


part numbers are created often but not more than 3 times per part number. so basically only need it to find the next, and in some rare cases the third instance.

 

[SirJB7]

Hi, greg.begin!


I'm afraid I still can't understand your requirements. Please elaborate a bit more, indicating worksheet structure, detailed formulas, and manual examples of desired output. That's to say, everything that can replace the non-uploadable file.


Regards!

 

[greg.begin]

sure.


i have list of part numbers. each part number is an installation that needs to be completed. a work order will be issued with the part number on it, so that, the person will know what to install. lets call the part number ABC. with work order 123. I need a spreadsheet that will track the part number and its associated work order.


however, sometimes there is an error with the work order. they still need the part number but they now need to fix the work order. so they fix the work order and send a new one with the same part number associated. so now part number ABC is now work order 456.


a report gets exportedl, it has a list of all part numbers and all work orders associated issued. i now need to find the part number and its work order and show it on my spread sheet. if they create a new work order, like in the above example, i need to have the part number listed twice and the work orders issued against that part number. for example, the report


part number........Work Order..

ABC................123

ABC................456


i need to show both work orders and it has to be referenced not hand jammed into the document.


currently my vlookup does this.


part number........Work Order..

ABC................123

ABC................123


i lose sight of work order 456.


does this help?

 

[SirJB7]

Hi, greg.begin!


I've just answered a post from Me-Ted, where I solved the same issue as yours:

http://chandoo.org/forums/topic/scheduling-tile-chart-from-work-sheets-info#post-29733


Please refer to sheet GSR, array formula of column J and formulas of columns K:L. I hope you could manage to handle that by yourself. If not, check this link:

http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

If still not, well, just advise and I'll try to give a look later.


Regards!

 

[greg.begin]

You are awesome, got it to work. thanks

 

[greg.begin]

OK, need your help again. I completed the formula for all the cells in my list needed. about 1254. the calculation time is about 5 minutes everytime i make a change. any suggestions?

 

[greg.begin]

Oh, never mind, i just changed the ranges to a much smaller range and it calculates in about 9 seconds. Thanks again. you all who support us are Awesome. thanks again SirJB7.

 

[SirJB7]

Hi, greg.begin!

Glad you solved it. I was just about writing that you might have two options, delete 1252 cells or buying a HPC workstation, in order to accelerate processing...

Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!