Solved

How can I modify this Excel worksheet with a script?

Posted on 2016-11-09
15
89 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 50

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 21

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 21

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 21

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 21

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 21

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 21

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 21

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

828 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