Guidance for A Logical Approach to Automate a Task

Java Junkie

New Member
Joined
Jan 17, 2018
Messages
3
Hey, everyone. I would like to open some discussion on the best approach to a task I have been given for creating a solution to simplify a process. I am not Excel savvy. I can use it, do nifty things when I need to, but I am not a guru or fluent in VBA or formulas.

I have been asked to help with a project at work where a current manual process is considerably time consuming and leaves a lot of room for human error. The long convoluted process is outlined below. *(Note: Do not read until your caffeine levels are high enough to prevent falling asleep at your desk!) :)

Background information:
A standalone program is used to manage many types of details and information for ingredients used in all of our recipes. We are required to monitor the recipes for ingredients that are considered allergens in order to make sure consumers are warned in advance before consumption.

When recipes change or new ones are added to the master database, we use a built-in tool to export the entire list of ingredients being used in new recipes or new ingredients being used in existing recipes. The list exports into a new workbook (which is created as 'Book1' onto the user's local drive) and contains only the data relevant to the specified recipe retrieved in the query.

At present, we are manually copying and pasting the freshly exported data from 'Book1' into a common workbook ('Recipes') that tracks each recipe revision and ingredient property in one spreadsheet. 'Recipes' uses conditional formatting to highlight the specific allergens we wish to monitor if/when they are present.

As it is now, in order to populate the remainder of the ingredient info, we copy the new ingredient data from a third worksheet ('Ingredients') that tracks details per ingredient, rather than the entire recipe, and then paste it into the common worksheet to fill in gaps not covered by the exported info. 'Ingredients' contains static information that never changes (formal name, identification information, etc.).

My task is to figure out a method to streamline the whole process to eliminate as many manual steps as possible.

Limitations:
-have to write it to work with a common filename on the exported worksheet (exports as Book1)
-have to write it to work with a common location of the exported worksheet (exports to local computer)
-have it insert data at bottom of destination worksheet and apply the conditional formatting
-number of items in each export/import is not consistent - based on the individual recipes ("bottom" is relative)
-need to be able to select specific target columns/cells to call data from and paste data into

Summary:
There is data in a program that is exported into 'Book1". The new data is manually copied and pasted into 'Recipes' but only some of the columns are filled in. The remaining data for the individual ingredients is copied and pasted in from a third workbook, 'Ingredients'. This process needs to be simplified, either using VBA or built in functions/formulas.

I am hopeful that some chatting here can help send me in the right direction for how to approach this, a better idea for what would work best and how to set about doing it.

I appreciate everyone's input but am not just asking for blocks of code. I would actually like to discuss the pros and cons of what works best and why. I would greatly like to learn what it is that I am going to have to do here to solve this task.

Thanks in advance for all replies...I apologize for the essay post, but I want to make sure the entire scenario is clearly explained.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
I'll bite. I've reached my caffeine limit and I have $.02. Have U tried the macro recorder? Any manual copying and pasting can be replicated by VBA. U already have a stand alone program that produces XL output in the form of a new workbook. U could request the developer to adjust it to suit your needs. Failing that, U do have the output info in a format that U can access and manipulate. I would create a userform based solution for adding/removing recipes, adjusting ingredient and allergy information. It would also displaying whatever output is required. HTH.Dave
 

Java Junkie

New Member
Joined
Jan 17, 2018
Messages
3
Thank you for jumping in, NdNoviceHlp! That is definitely food for thought!! I am slightly familiar with userforms; I will try to look into them more and see if something can be tweaked to fit this need.

I am wondering how to work in the step of populating the leftover details of the ingredient info into the recipe sheet, after adding the new stuff (exported from the standalone program) into the recipe sheet with the userform.

I appreciate your time and thoughts.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
I'm guessing an update recipe sheet button that transfers the data would also be coded to transfer the leftover details transfer. Using the macro recorder will provide you general code to get started with. From that U can develop code to find the leftover details and transfer them to the appropriate place. Again, anything that U manually do with XL can be replicated with VBA. A userform will allow U to do all of your stuff in 1 place instead of jockeying between worksheets/workbooks. I'm sure others with more experience and/or caffeinination may have alternate suggestions. Good luck. Dave
 

Java Junkie

New Member
Joined
Jan 17, 2018
Messages
3
Thank you for the follow up, Dave. I think I get it. Like I said earlier, I am not fluent so what I thought I had a grasp of was not nearly as straight-forward as what you explained. I am going to see what kind of fun I can get into with this tonight and tomorrow; I will ultimately learn some things along the way so it is not a total loss.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,774
Learning is good. Start a new thread if you have more specific questions. Have fun with it and know that I'm quite certain that U will succeed. U have a great start putting it to paper what U want to achieve. Now U need to convert that to a computer speak. ie. use transfer recipe data from wb.somename sheet.whatever to column & row something to someotherplace sheet column and row; etc. This is where the macro recorder can be helpful for the beginner. It records a lot of useless crap but we can help U get rid of the useless stuff and keep the nb stuff. Anyway, usually google has the answers and if not I'll pour another cup of coffee and put on my reading glasses. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,919
Messages
5,627,624
Members
416,257
Latest member
salomon

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
Top