Solved

How can I modify this Excel worksheet with a script?

Posted on 2016-11-09
15
43 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
  • 7
  • 7
15 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

the file is empty

Regards
0
 

Author Comment

by:100questions
Comment Utility
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 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
Comment Utility
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
 

Author Comment

by:100questions
Comment Utility
@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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
Ejgil Hedegaard19 - Thanks, however I don't see the area to select the data range.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:100questions
Comment Utility
@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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
Comment Utility
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 21

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Set OWA language and time zone in Exchange for individuals, all users or per database.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now