+ Reply to Thread
Results 1 to 3 of 3

Array based on matching Row & Column

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    England
    MS-Off Ver
    Mac 2011
    Posts
    12

    Array based on matching Row & Column

    Hi All,

    I have a spreadsheet with a products list in the first column, then sales on each day going across.

    Product Case Size Units Per Sale Sales Per Case Sunday 30/08/15 Monday 31/08/15 Tuesday 01/09/15 Wednesday 02/09/15 Thursday 03/09/15 Friday 04/09/15 Saturday 05/09/15
    Product 001 30 5 6 6 2 7 3 3 3 9
    Product 002 30 5 6 2 3 3 6 7 8 3

    Those dates continue across for the full year as headers. Each day will have the products sales recorded. Then on a separate sheet I'm allowing the user to find a product (at the minute from a drop down box in B2), then have its information listed, along with the average sales for each day.

    I can find the sales from the 1st week by searching for the product and the corresponding day. i.e
    Please Login or Register  to view this content.
    but I don't know how to fit them into an array to calculate the average for all Sundays or all Mondays etc or calculate the average for the last 4 Sundays entered.

    I tried
    Please Login or Register  to view this content.
    but I don't know where to tell it to look for the result as the criteria gives the placement, like in my first formula.

    Any help would be appreciated. It's going to be 365 columns of dates and around 100-200 products, so getting this automated and not hard coded would be great for expansion.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Array based on matching Row & Column

    If you post a workbook with some sample data it will be much easier to help you.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    12-04-2013
    Location
    England
    MS-Off Ver
    Mac 2011
    Posts
    12

    Re: Array based on matching Row & Column

    Thanks Jacc

    Bakery Project.xlsx

    Only 2 worksheets at the moment, one for data entry and the other for calling the data to view as a single set of information. Unsure on how to keep going after finding the first Sundays information, to then go to the next Sunday and pull that data etc.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 09-12-2014, 03:05 AM
  2. Replies: 2
    Last Post: 04-11-2014, 12:18 PM
  3. [SOLVED] Removing specific elements from an Array based on matching elements in a different Array
    By jamesdeuce in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2014, 10:35 PM
  4. [SOLVED] Match existing column name, copy based based on matching name, drag till end
    By Amolvijay in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-19-2014, 04:20 PM
  5. Concatenate a column based on a column with matching rows
    By excelsior51 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2013, 11:13 AM
  6. Replies: 1
    Last Post: 04-23-2013, 12:46 AM
  7. Replies: 1
    Last Post: 12-18-2007, 01:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1