+ Reply to Thread
Results 1 to 11 of 11

Search Multifacitated Table for Values

  1. #1
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Search Multifacitated Table for Values

    I'm attempting to search values from a set of data that's downloaded every day. Example below. I need to find out how much of item category (food or bev) was sold during lunch and dinner in both the bar and the dining room. I tried using index match but I'm having trouble since there essentially 2 tables located on top of each other.


    Things to note:

    1. The format sucks, I know. But it is downloaded this way...

    2. Goal is to pull data from "Feb-26" (daily tab) to the "Week 1" tab.

    3. There are 3 criteria

    Meal time: Lunch or Dinner (There sometimes are more)

    Revenue Center: Bar or Dining Room (There sometimes are more)

    Item Category: Food or Beverage


    Can you suggest a formula I could use to scan all the data and find the correct values?

    The range I'd be searching for, Bar or Dining room, would always begin with the cell containing Bar/Dining Room and end with the cell containing "Total Net Sales". Could I somehow define the range using that and then use index/match?

    example.xlsx
    Attached Files Attached Files
    Last edited by ksayet; 05-15-2015 at 04:59 PM.

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,866

    Re: Search Multifacitated Table for Values

    Without seeing your excelfile usually you solve this with a pivot tabel
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Search Multifacitated Table for Values

    Popipipo,

    I've attached an example of the data download I do every day. "Feb-26" contains the download and "week 1" is the sheet that I have to manually enter the values into. Ideally I'd like the "week 1" tab to read the "Feb-26" data when I paste it in there.
    Last edited by ksayet; 05-15-2015 at 04:01 PM. Reason: tried to delete duplicate reponse, sorry.

  4. #4
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Search Multifacitated Table for Values

    Here's an example of the data

    Attachment 395231

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Search Multifacitated Table for Values

    Here are a couple of different solutions that you may be able to adapt to your situation. This uses a vertical arrangement of dates so that the data can be more easily read and manipulated.

    Horizontal arrangements are more difficult to read and are more cumbersome to calculate with.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,866

    Re: Search Multifacitated Table for Values

    The formulas in worksheet week1 refer to another workbook.
    The index formula don't like that.

    I've been looking for more 15 min to the file and don't know what you exactly want.

  7. #7
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Search Multifacitated Table for Values

    Thank you for your work. I really appreciate it. Unfortunately, I need the information pulled from one sheet into a different format on the other. I've attached an example which may eliminate some confusion.

    The formats need to stay the same. I just need to somehow read the download "Feb-26" and pull it into "week 1"




    Quote Originally Posted by newdoverman View Post
    Here are a couple of different solutions that you may be able to adapt to your situation. This uses a vertical arrangement of dates so that the data can be more easily read and manipulated.

    Horizontal arrangements are more difficult to read and are more cumbersome to calculate with.

  8. #8
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Search Multifacitated Table for Values

    example.xlsx

    Sorry. Please ignore the formulas. I have deleted them.

    How do I pull the information from "feb-26" to "week 1"?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,979

    Re: Search Multifacitated Table for Values

    You are looking for these items...
    Food
    Liquor
    Beer
    Wine
    Cold Bevs

    Could you change those names to something actually matches what you want from the douwnload? You ask for "Liquor", but what you actually want (based on your sample) is "Beverage Total Net Sales" and you say Beer, but your total is for LIQUOR


    They seem to be in the same relative position in each "Table" of data. Is that true for all the downloads?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Search Multifacitated Table for Values

    Yes, you're right. I wrote bev and food for simplicity's sake. The download matches those categories as shown on "feb-26" tab.

    The downloads are always in that order. But sometimes there is no lunch data. And sometimes not every category (food,liq,beer etc.) is represented.

  11. #11
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Search Multifacitated Table for Values

    Any luck FDibbins?

+ 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. [SOLVED] How to Search Table for Intersecting X and Y Axis Values
    By JNEWBURY2280 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2013, 08:24 PM
  2. [SOLVED] Search for multiple string values in the first row of a table and format column values.
    By Excel_junky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2013, 12:48 PM
  3. Pivot Table - Search Values from another sheet... possible ?
    By enceladus in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-18-2013, 10:55 AM
  4. [SOLVED] Search values in data table that are greater than cell value
    By sambuka in forum Excel General
    Replies: 10
    Last Post: 07-04-2012, 05:27 PM
  5. Search table values
    By kraddark in forum Excel General
    Replies: 3
    Last Post: 05-22-2011, 02:51 PM

Tags for this Thread

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