Solved

How can I modify this Excel worksheet with a script?

Posted on 2016-11-09
15
98 Views
Last Modified: 2016-12-01
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
0
Comment
Question by:100questions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41881833
Hi,

the file is empty

Regards
0
 

Author Comment

by:100questions
ID: 41882363
Hi, sorry about that, I will try to reattach as per below.

Also, here is what it looks like as well:
ReportSample.xlsx
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41882588
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:100questions
ID: 41882757
@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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41882925
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
 

Author Comment

by:100questions
ID: 41883987
Ejgil Hedegaard19 - Thanks, however I don't see the area to select the data range.
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41884069
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
 

Author Comment

by:100questions
ID: 41884081
@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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41884696
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
 

Author Comment

by:100questions
ID: 41884699
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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41884883
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
 

Author Comment

by:100questions
ID: 41886489
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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41886554
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
 

Author Comment

by:100questions
ID: 41886564
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
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41886652
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

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question