Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sum of quantities associated with changing duplcate values in a remote Excel Spreadsheet

Posted on 2015-02-22
8
Medium Priority
?
45 Views
Last Modified: 2015-03-22
I need a result list that contains the sum of quantities associated with changing duplcate values in a remote Excel Spreadsheet.
Source data changes frequently as data is pulled from a large database.  
Names in the list are a subset of a larger database.  
The source record count will change frequently.
namedata.xlsmnametotals.xlsm
0
Comment
Question by:AMixMaster
[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
8 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624956
Hi AMixMaster

I would simply go with a Pivot Table.

It will also be easy to adjust everyday.
namedata-Pivot-table.xlsm
0
 

Author Comment

by:AMixMaster
ID: 40624959
The result must be in a seperate workbook.
I want to avoid a pivot table and use linking formulas.
The sheet is distributed to many users and a remote pivot table does not always work.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624967
Will you always like to 1 single path to a specific workbook or multiple path and then merge all together into a master workbook before you create your final summary?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:AMixMaster
ID: 40625058
single path, master workbook is open when data is read and closed after breaking links and copy paste values.
master workbook is not editable as it is rewritten frequently by the database program.
i linked a large range that includes all the source data and placed it on a hidden sheet with the pivot table.  
it works for now but liinking the report output to the pivot table data does not guarantee that the cell references will be the same whenever the data changes.
I thought of using index, sumif or frequency but can't resolve the many to many relasionship without the pivot table.  
it;s complicated.....
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40625111
There is a way I think to use SQL compact edition to process you data and get results from source using ordinary SQL statement and save it to target. It will be easer to implement

Open in new window

0
 

Accepted Solution

by:
AMixMaster earned 0 total points
ID: 40627217
A collegue provided this and it solved the problem:
The solution uses "INDEX" in a different formula in each of the 3 columns.

{=IFERROR(VLOOKUP((INDEX('[Workbook.xlsx]worksheet'!$B$1:$P$50,SMALL(IF('[Workbook.xlsx]worksheet'!$I$1:$I$50="crteria one",ROW('[Workbook.xlsx]worksheet'!$H$1:$H$50)),ROW(1:1)),1))&" criteria two",'[Workbook.xlsx]worksheet'!$BR$5:$BU$500,4,FALSE),"")}
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40680771
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

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.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

715 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