+ Reply to Thread
Results 1 to 13 of 13

Help with Macro/Indexing

  1. 10-16-2014, 08:47 PM #1
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Help with Macro/Indexing

    Hi guys I am currently working on a macro for my school project and would like some assistance
    It involves multiple criteria and giving an estimate based off of those criteria given there is a master database for pricing

    For example

    If Line 1 has Criteria: W, X, Y, Z under respective columns A, B, C, D
    Then the formula will pull a price from a master list with the given criteria, W, X, Y, Z
    and input that price next to Z under column E

    I tried using indexing with a match but I can't seem to draw a connection between using the index/match which would result into a pull
    from the master price sheet and inputting it next to the line with the corresponding data

    Any insight would be appreciated,

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 10-16-2014, 10:05 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with Macro/Indexing

    It's a lot of work for one formula to do unassisted. It can do it, and I could show you, but I can tell you in advance it would be a very ugly formula.


    Better, with that many points of comparison, concatenate those values row by row in the original data into a new column to make the search much more straightforward.

    Let's assume column AZ is empty in sheet1. Starting in AZ2 the formula to concatenate those search values would be:
    =W2&"-"&X2&"-"&Y2&"-"&Z2
    Copy that formula down the whole dataset to get your new KEY column. You can hide this column if you wish.


    Now back on your search sheet, the formula is a less traumatic INDEX/MATCH:
    =INDEX(Sheet2!AA:AA, MATCH(A2&"-"&B2&"-"&C2&"-"&D2, Sheet2!AZ:AZ, 0))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 10-16-2014, 10:16 PM #3
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Re: Help with Macro/Indexing

    Okay, so what you are saying is that by using concatenate we will condense the criteria into one unique value? Then use an index based off of that concatenated to search/match?

    Please correct me if I am wrong ^^ I am learning
    Reply With QuoteRegister To Reply
  4. 10-16-2014, 10:17 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with Macro/Indexing

    That's 100% correct.
    Reply With QuoteRegister To Reply
  5. 10-16-2014, 10:29 PM #5
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Re: Help with Macro/Indexing

    Okay so I got the formulas down but I'm a bit confused about the index function itself

    =INDEX(Sheet2!AA:AA, MATCH(A2&"-"&B2&"-"&C2&"-"&D2, Sheet2!AZ:AZ, 0))

    For Sheet!AA:AA > this will reference the database correct or will this reference the master price sheet vice versa for the second sheet reference?
    Reply With QuoteRegister To Reply
  6. 10-16-2014, 10:46 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with Macro/Indexing

    You said the prices were next to column Z, so I presumed that meant column AA.
    Reply With QuoteRegister To Reply
  7. 10-16-2014, 11:00 PM #7
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Re: Help with Macro/Indexing

    What I mean to say was that to the right of criteria(W,X,Y,Z) next to Z is where I want price to be inputted based on the criteria
    The price itself will be pulled from a separate sheet(a master database) and then put next to Z or "AA"

    AA in this case would be an empty cell

    For example
    Location1, City1, Supplier1, ProductType, <-- corresponding information to that would have an identical match in price sheet that has a corresponding price,
    I want to make a macro that pulls that price based on those criteria and places it next to the product type in the next column
    Last edited by Lujin; 10-16-2014 at 11:03 PM.
    Reply With QuoteRegister To Reply
  8. 10-17-2014, 02:55 AM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with Macro/Indexing

    So I got it all backwards, easy to do when you have discussion like this in the air rather than in a workbook we look at together. The concept is the same.

    1) Put a concatenation formula in the price list table as shown, adjusted for your actual columns, and copy down.
    2) Use an INDEX of the pricing column in the list table, then MATCH() the concatenated cells from your order sheet row to the new concatenation column you added to the pricing table.
    Reply With QuoteRegister To Reply
  9. 10-17-2014, 11:22 AM #9
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Re: Help with Macro/Indexing

    Okay so I got that and it works my only last concern is how do I make a macro that will input these formulas based on a macro?
    IE if the "Order Sheet" has 50 entries versus 30 entries versus 100 entries, how would the macro look like? What kind of formula would I need for the range name?
    Since column "AA" technically will never have any data(the data is derived from the amount of orders and then taken from the pricing table) would an offset still work?
    Reply With QuoteRegister To Reply
  10. 10-17-2014, 05:05 PM #10
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Re: Help with Macro/Indexing

    Here is what I am trying to accomplish

    https://www.mediafire.com/?fhvkn3061zhlvkt

    I am trying to pull the correct price from the AssumptionData over to the first sheet.
    Reply With QuoteRegister To Reply
  11. 10-17-2014, 09:56 PM #11
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with Macro/Indexing

    Record a macro for me... turn on the recorder, switch sheets a couple of times, then enter your working formula in the first cell.

    Turn off the recorder and post that code here, I'll show you how to convert that to a "paste formula in all used rows" thing.
    Reply With QuoteRegister To Reply
  12. 10-18-2014, 02:12 PM #12
    Lujin
    Lujin is offline
    Registered User
    Join Date
    10-16-2014
    Location
    California
    MS-Off Ver
    2011
    Posts
    7

    Re: Help with Macro/Indexing

    =INDEX(Orders, MATCH(F2, AssumptionData, 0))

    Orders=Orders Sheet
    F2=Concatenated Output(F being the column where all concatenations are on the order sheet)
    AssumptionData has the concatenated criteria under column A, with the associated price in column B starting at row 2

    How is it that I am still getting a NAME? error from this?

    also thank you for your time and help so far
    Reply With QuoteRegister To Reply
  13. 10-18-2014, 06:48 PM #13
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with Macro/Indexing

    The #NAME? is happening in the file above because your named ranges are not existing in that workbook.

    Meanwhile this version of the same formula would work:

    =INDEX(AssumptionData!$B:$B, MATCH(F2, AssumptionData!$A:$A, 0))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. help indexing something
    By scrum533 in forum Excel General
    Replies: 1
    Last Post: 12-22-2011, 08:28 PM
  2. data indexing macro
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-15-2011, 01:36 PM
  3. Indexing macro
    By ad9051 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-06-2011, 08:05 AM
  4. Tab indexing
    By juliejg1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-10-2006, 04:10 PM
  5. Indexing of Name
    By Charlie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-05-2006, 08:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1