Register
Welcome to the Stormspire.net WoW Gold Forums!

Greetings, guest! Welcome to Stormspire.net, your #1 resource for WoW and Diablo 3 Gold, Professions, and everything in between!


Enjoy what the site offers? Consider Premium Membership if you wish to get the most out of the site. Premium Membership benefits include the acclaimed Goblineer's World of Warcraft Gold Guide, private forum access, a complimentary high-quality avatar, and much more!


If this is your first visit, make sure to check out the Stormspire.net Survival Guide for a quick site orientation. You may also register to unlock all standard forum features, and to get rid of these lovely ads!


We proudly host the Official forums for TradeSkillMaster, The Undermine Journal, and WoWuction.

Results 1 to 5 of 5
  1. #1
    Ethereal Contributor
    Jarnow's Avatar
    Posts
    101
    Reputation
    1
    Tagged in
    24 Posts
    Add to this user's reputation

    Duplicate TSM Resale Tab in Excel

    Hi all,

    A question from an intermediate Excel user regarding how to manipulate the data exported from TSM Accounting to Excel.

    So I have a tab in my Excel file with all the auctions I've bought, and another tab with all the auctions I've sold. Some items appear on both tabs (i.e. stuff I've bought and resold) whereas most will not, such as mats bought and crafted items sold.

    I would like to develop a formula (or maybe a macro?) to provide the data presented in the Resale tab in TSM Accounting. That is, extract only the items that appear in both tabs (bought AND sold), and summarize the total numbers.

    How can this be done? An example formula would be great. If it needs to be a macro, I can probably figure out how to create one, once I know the macro to create.

    Thanks!

  2. #2
    Ord
    US-Zul'jin (H)
    Wind Trader
    Ord's Avatar
    Posts
    477
    Reputation
    72
    Tagged in
    185 Posts
    Add to this user's reputation
    I would use VLOOKUP in either (or both) tables to bring in the value you want to see. For example, on your auctions sold worksheet you can add a vlookup to show the value from your purchased worksheet. After that, you can add more formulas to compare the two values or a filter to exclude ones without data.

    EDIT:
    Data is much easier to work with in excel when it's for formatted as a table (aka XML Maps). If you format both sheets as a table you can refer to the table name and column names instead of hard-coded sheets and columns. Eg. Table_Sold_Auctions[AvgSell] instead of 'Sheet1'!E:E

    I can't really give more specific example formulas without seeing the workbook, though.
    Last edited by Ord; July 2nd, 2014 at 02:49 PM.

  3. #3
    Ethereal Contributor
    Jarnow's Avatar
    Posts
    101
    Reputation
    1
    Tagged in
    24 Posts
    Add to this user's reputation
    Ah ok filtering may be the way to go. It would be nice to have it in a table that auto-populates only those items that have been re-sold, but that may be beyond my modest Excel skills. Whereas your suggestion is well within my abilities. Thanks.

  4. #4
    Ord
    US-Zul'jin (H)
    Wind Trader
    Ord's Avatar
    Posts
    477
    Reputation
    72
    Tagged in
    185 Posts
    Add to this user's reputation
    While it might seem annoying to have data you "don't want" in a table, Excel scales very well with filtering. Creating a pivot table or pivot report, for example can incorporate filters into the data it presents in a way that you wouldn't even know it's there. Also, creating a chart based on a table with rows that are filtered out should exclude those rows from the data-points on the chart, as well.

    If you really wanted to, you can join two Excel tables together to form a 3rd using SQL-like syntax in Microsoft Query. You could even set up that data-source in a separate Excel file that references back to the source file(s) for it's data (so you could, for example, have your raw-data export workbooks that you update as needed that will automatically get sourced in your "frontend" Excel file with the final joined table and charts and whatever else you want in it).

  5. #5
    Ethereal Contributor
    Jarnow's Avatar
    Posts
    101
    Reputation
    1
    Tagged in
    24 Posts
    Add to this user's reputation
    I got the first paragraph. Second one completely lost me Pivot tables actually might work real well, I haven't ever used them much, but when I have they've been pretty handy. Thanks for the tips.

 

 

Similar Threads

  1. Duplicate Sales Recorded
    By Arex in forum General TradeSkillMaster Discussion
    Replies: 2
    Last Post: October 29th, 2013, 04:39 AM
  2. An addon that finds duplicate items in gbank
    By Ramrad in forum Archive (Addons and Macros)
    Replies: 3
    Last Post: September 6th, 2012, 08:08 PM
  3. Strange resale bug
    By theomi in forum Archive (Addons and Macros)
    Replies: 0
    Last Post: September 29th, 2011, 04:45 PM
  4. Accounting error with duplicate Auctioning name
    By kryo in forum General TradeSkillMaster Discussion
    Replies: 1
    Last Post: June 5th, 2011, 12:27 PM