1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup & Average Across Multiple Sheets

Discussion in 'Ask an Excel Question' started by ~Josh~, Nov 13, 2017.

  1. ~Josh~

    ~Josh~ New Member

    Messages:
    4
    Hey, new here, but already have found the forums and posts to be an excellent resource.

    Goal: Create a list of average unit prices for multiple occurring items from multiple worksheets.

    See attached xls file

    The 'Average Unit Price' worksheet is a standard list of items that will never change.
    The subsequent worksheets are from various projects with varying unit costs for the some of the standard items. In the future, additional projects will be included in the file as a new worksheet.

    What I am trying to do:

    Find all occurrences of the identifying item number from column A in sheet "Average Unit Price" in column A of all subsequent worksheets. There could be multiple occurrences in same worksheet.

    Then, for those identified rows, return the Average of the value from column E (Average Unit Price).

    Not sure if possible or where to start.

    Thanks,

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    884
    I suspect if you have Power Query this might be relatively simple, however I may look into that later.
    In the meantime, in the attached is a button (near cell F1 of the Average Unit Price sheet) which runs a macro to put the values in. It's very basic, no bells and whistles, no checks, t's not crossed and i's not dotted; consider it work in progress.
    As well as put the averages in, it also puts a count of how many values went towards that average in the next column. Easy to stop that happening.
    At the moment, I've got it to process only visible sheets between two new sheets I've added called Start and End. The idea being you put all sheets you want processed between those two. (If you know that you want ALL other sheets apart from Average Unit Price to be processed we can take these two new sheets out and use another, simpler method.)
    One thing to note, is that sometimes the value in column E is zero. Viz.:
    Average Unit Price D298 you might consider wrong because bid item no. 3123.0103 at cell E38 of 197.001 Park Vista - Bid No. 1 is 0 but is included in the average.
    Average Unit Price D596 you might consider wrong because bid item no. 3305.0109 at cell E93 of 197.001 Park Vista - Bid No. 1 is 0 but is included in the average.
    I've highlighted in yellow some of these cells.

    What the code does is to make a single list of all the Bid item nos. and their prices from all the relevant sheets, in memory, it then goes through each of the values in column A of the Average Unit Price sheet, and looks for all similar values in the list and averages them.

    Attached Files:

    Last edited: Nov 13, 2017
    Thomas Kuriakose likes this.
  3. ~Josh~

    ~Josh~ New Member

    Messages:
    4
    I found this tutorial which is almost exactly what I was looking for. Took me all day to find thought. lol.

    Code (vb):
    https://www.youtube.com/watch?v=wCE-oNGUmD4
    Last edited by a moderator: Nov 14, 2017
  4. ~Josh~

    ~Josh~ New Member

    Messages:
    4
    Don't see any attachments...
  5. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Yes, I accidentally pressed 'Post reply' while still composing. All finished now.
  6. ~Josh~

    ~Josh~ New Member

    Messages:
    4
    This is excellent. I think to get around the 0 issue, the data tabs need to be sure to delete any zero entries. Simple as that.
  7. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Your link doesn't show up, but it's this one I think:
    https : //www . youtube .com/watch?v=wCE-oNGUmD4
    (remove the spaces! It didn't show up when I tried either)
    Thomas Kuriakose likes this.
  8. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Or change:
    If ConsolidVals(1, i) = BidNo Then
    in the code to:
    If ConsolidVals(1, i) = BidNo And ConsolidVals(2, i) > 0 Then

Share This Page