How can I modify this Excel worksheet with a script?

I would like to modify the attached spreadsheet.
I would like a script to look into each field which contains the UPC code, and compare Year 1 to Year 2 to Year 3.
If some UPC codes are not shown in Year 1 and Year 3 for instance, then I would like the script to add a line so that is also shows in Year 2 however the Price would be blank, and so forth.
Is there a script which can do this so that after running it, every UPC code is accounted for for all the years, however if there was nothing to report for a given year for that UPC Code, then the price would be blank.
Thanks. ReportSample.xlsx
100questionsAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
Another method could be to assemble all in one list, with the year as a field, see Sheet2 in attached.
Then make a pivot table, where the price is blank if none.
ReportSample.xlsx
0
 
Rgonzo1971Commented:
Hi,

the file is empty

Regards
0
 
100questionsAuthor Commented:
Hi, sorry about that, I will try to reattach as per below.

Also, here is what it looks like as well:
ReportSample.xlsx
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
100questionsAuthor Commented:
@Ejgil Hedegaard - Thank you.  Yes this seems like it might work.
There will be about 600 or more items thought.. How can I adjust the Pivot Table, or where can I go to adjust or refresh it for example if I add more items?
0
 
Ejgil HedegaardCommented:
When you select a cell in the pivot table, you get some extra menus, specifically for pivot tables.
There you can adjust the data range, update etc.
If you change the list to a table, then the pivot table range will automatically adjust, and you just need to update.
Right click somewhere in the table, and you get a small menu for pivot table, where update is one of the options.

Or make the complete list, change it to a table, select a single cell in the list, and then make the pivot table again.
0
 
100questionsAuthor Commented:
Ejgil Hedegaard19 - Thanks, however I don't see the area to select the data range.
0
 
Ejgil HedegaardCommented:
When you select a cell in the pivot table, you get 2 extra menus, probably named Options and Design.
Don't know exactly since my version is not English.
On the option menu, there is a button for "Change Datasource"
0
 
100questionsAuthor Commented:
@Ejgil Hedegaard - The Change Data Source will take me to Sheet 2, where the data seems to be sorted using the Year, Product Name, Pack Size UPC Code and Costs..
How did you transfer the data from Sheet 1 to that spreadsheet?
0
 
Ejgil HedegaardCommented:
Data on Sheet2 are copied from the 3 "tables" on Sheet1, adding the year in column A.
If you do something similar with the real data (600 rows), then you can make a pivot table on that.
The data does not need to be sorted, it is just so, since I copied Year1, Year2 and Year3 in that order.
But when the data are sorted, the pivot table will pick up the data in that order, but sorting is also possible within the pivot table.
0
 
100questionsAuthor Commented:
Thanks Ejgil Hedegaard, very much appreciated... Can you describe how you were able to do that..
I want to add much more data, and refresh the Data on Sheet 2..  
Sorry I am not sure how that was done.. but I would like to.
Thanks kindly for your help.
0
 
Ejgil HedegaardCommented:
You just add the new data below the existing data, and change the data range.

Pivot tables are a smart way of making sum and count (and much more) without making any formulas, and for large datasets much faster than formulas.
It works by drag and drop.
The disadvantage is that when data are changed, it does not automatically use the changed data.
Then the pivot cache has to be updated.

When you get familiar with pivot tables, you will see how easy it is.
But everything is difficult the first time.
I think you should Google "Excel pivot tables".
In the search result, find a YouTube video of how to get started.
I guess that is better than you get it bit by bit here.
0
 
100questionsAuthor Commented:
Thanks.  Regarding your comment:  "You just add the new data below the existing data, and change the data range."
My question then is, how do I add the add the data below the existing data?
If I add data to Sheet 1, by adding another row with data, it will not refresh the Data on Sheet2, where the Data Range is being applied.
Can you elaborate, or should I ask another question with this specific inquiry?
0
 
Ejgil HedegaardCommented:
Since the pivot table use data on Sheet2, you must add the data on Sheet2 so you get a list with all data.
There are no connection between Sheet1 and Sheet2.
0
 
100questionsAuthor Commented:
Thanks again.  That's a lot of manual work.  I will open another question to see if some of the experts can help me to take the Data in Sheet 1 and output it to Sheet 2 in the format it's currently in.
0
 
Ejgil HedegaardCommented:
If the complete lists are like the sample, a simple copy and paste 3 times, and adding the year after each paste can be done in a couple of minutes.
I don't see it as a lot of manual work, except if it has to be done many times.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.