Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I modify this Excel worksheet with a script?

Posted on 2016-11-09
15
Medium Priority
?
107 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 53

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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
 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

 

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 23

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 23

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 23

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 23

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 23

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 23

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

WatchGuard Case Study: NCR

With business operations for thousands of customers largely depending on the internal systems they support, NCR can’t afford to waste time or money on security products that are anything less than exceptional. That’s why they chose WatchGuard.

Question has a verified solution.

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

A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Screencast - Getting to Know the Pipeline
Suggested Courses

877 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