Problem in getting complete recipe/ Comprehensive recipe

Hello Guys,

I am facing a query for some time, I need your assistance in sorting this out.

I maintain recipe given by kitchen dept in order for cost control.

Pls see the attached file for the way i maintain the recipe info.

Now this recipe are presented in terms on item name and ingredients mentioned as the usage pattern.

However they are represented by an unique item code on the left side


It is very difficult to sort recipe based on the their unique item code.

Some time there is a requirement of only 50 recipes, it means the complete recipe, with all ingredients, not one ingredient of the recipes.

Thus it becomes very difficult to go in the complete file and remove each items, esp when there are more than 1000 recipes like this.

So, we need to remove all the recipe one by one. it almost take a day to compile a report

However, I have a method that I can collect the code of all these items, as they are all unique.(highlighted in color)

Is there some macros, possible where i can just type in the item codes and all the recipe will appear in the output sheet.

Kindly see the attached file & assist.

Best Regards
AT

Hi..

Here is some basic code to get you started.. i am short on time right now..

Note: I removed a blank line in each recipe that was was between the last recipe item and the row that had "Overheads @.." in column B.. did this so CurrentRegion method could be used..

Like i say.. this is just to get you started..

If it were me.. I would make it so the user selected a Recipe code from a combo and then pressed the button...

Private Sub CommandButton1_Click()
Dim fVal As String
fVal = [B3]
With Sheets("Total Recipe")
  .Range(.Columns(1).Find(fVal).Address).CurrentRegion.Copy
  Sheets("Results").[G3].PasteSpecial Paste:=xlPasteAll
End With
End Sub

Try the attached..

Hello Apo,
Thanks, This is great start.
However I face two issues with this.

The format of the recipe is such, that we need to have one row between the final ingredient and overheads, this is the format for all the recipes. I cant change the same.

and this mode allows the user only to select one recipe at all time.

I want a code that will search for multiple code some time 100 or more. to do this over 100 times will mean huge effort.

Please see the attached file. (highlighted in Yellow)


Rgds
AT

Hi..

The format of the recipe is such, that we need to have one row between the final ingredient and overheads, this is the format for all the recipes. I cant change the same.

Understood.. no worries.. sometimes if the structure of the 'raw data' can be changed it makes everything easier... no matter.. I will find an alternative..

and this mode allows the user only to select one recipe at all time.

I want a code that will search for multiple code some time 100 or more. to do this over 100 times will mean huge effort.

ok.. so.. you want the user to enter ALL the required recipe codes into B3 downwards?

Like i say.. but short on time atm.. so i will look at it tomorrow.. someone else might chime in too which will be good..

"ok.. so.. you want the user to enter ALL the required recipe codes into B3 downwards?"

Yes i do want that. that is the whole purpose as i can get one recipe any time by search.

"Regarding rows"

I cant change that there are more than 1000 recipes, I am sorry.

Rgds
AT

Hi apo & all.

Kindly try to assist me on the above query.

Rgds
AT

Super Genius, excellent, Marvelous, Man... I owe you a lot....

U literally have saved my day's work and turned into minutes...

Thanks a zillion ... Highly appreciated.

Rgds
AT

Super Genius, excellent, Marvelous, Man... I owe you a lot....

U literally have saved my day's work and turned into minutes...

Thanks a zillion ... Highly appreciated.

Rgds
AT

You're welcome.. I am happy it helped..