+ Reply to Thread
Results 1 to 4 of 4

index/match for varying prices!

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    index/match for varying prices!

    Hi,

    I've been stuck for a while trying to find the best way to perform this task through vba and figured I'd give it a try here.

    I was trying to find the best way to do the following in the attached workbook:

    1. index/match all of the prices for the serial numbers on sheet1 from the produce tab; (assuming you put the values in column2 on sheet1)

    2. For any remaining serial numbers that did not have a price (N/A), lookup the prices for those serial numbers using the alternate tab. Please note the alternate tab has 2 sets of prices that must be referenced when looking up the remaining serial numbers (columns C:H - prices and types will always be identical) (assuming you put the values in column3 for the first set of prices and column 4 for the second set of prices on sheet1)

    3. Then index/match the 'type' (rice, eggs, etc.) on the alternate tab (eg rice, lettuce etc) for ALL and ONLY serial numbers that are purple on sheet1 (assuming you put the values in column 5 on sheet1)

    4. for any items that do not have a price (columns 2:4) at this point, clear contents (do not delete row) up until column 9 (do not clear contents in column 10)

    5. For any serial numbers that have either a price, or price and security type (in columns 2:5), Paste special values into columns 8 & 9 (price and type columns) on sheet1

    6. clear all index/match values in col. 2:5

    Please note that the number of rows will always be dynamic and changing so the number of different colored rows will always be different.




    I have attached the workbook for your reference, any and all help is greatly appreciated in advance!



    Attachment 289434

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: index/match for varying prices!

    Your request is hard to answer (for me) for a couple of reasons. One, you refer a lot to column numbers but leave us guessing which sheet you're talking about, and two, throwing in the twists with the colored cells just comes across as wacky (no offense). You could just as easily add another column alongside the serial #s with a code for whatever it was that the colors were supposed to mean. After that, formulas with a combination of Vlookups and If statements should get you what you need without any VBA necessary. Provide that, and you're more likely to get a response (assuming you still need it).
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: index/match for varying prices!

    Hi,

    Sorry if I was unclear. I am trying to essentially vlookup the prcies from the 'produce' and 'alternate' tab to sheet1 for each serial number. I am currently using a nested if index/match function which says look up all prices for the serial numbers on sheet1 against the 'produce' tab, if there are any missing prices then look on the alternate tab.

    I was just curious if there was a way I could essentially automate it and put it into vba. The tricky part becomes when I need to clear the contents of any remaining purple cells on sheet1 if they do not have a price in either the 'produce' or 'alternate' tabs up until column 10. I am not familiar with using color as a criteria so perhaps you may able to help with that.

    Separately, if I wanted to use a dynamic range in VBA for a Countif function how could I do so? For example, on sheet1 in Column A I have a bunch of serial numbers with some of them repeating x number of times. One sheet2 I paste the values from sheet1 and used remove duplicates to obtain a unique list in Column A. How can I use countif function on sheet 2 in Column B to give me the frequency that each of the serial numbers in Column A occurred on Sheet1 in Column A? I was able to do it for a fixed range but I instead of B1:B28 I want it to be B1 until the first empty row on sheet2 since the length of the unique values will change (code snippet below).

    Again apologies if I was unclear, I appreciate all of your help.

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: index/match for varying prices!

    Background colors can be referred to or updated with either (range).interior.color or (range).interior.colorindex. You can do research to find the differences, but it probably makes no difference which one you use. I would suggest starting by opening a code window, and in the Immediate pane, enter
    Please Login or Register  to view this content.
    Select various background-colored cells and press Enter on the above code lines to see the results. You can use those in your code, or there are vb constants (vbRed, vbBlue, etc) available for the Color property, which you can experiment with:
    Please Login or Register  to view this content.
    I don't know how far that will get you. I still think background colors as a criteria is a little wacky

+ 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: 6
    Last Post: 04-30-2014, 02:42 AM
  2. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  3. index/ match on varying criteria
    By tim hem in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-18-2013, 09:00 AM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Excel 2007 : Index and Match-prices and catalog numbers
    By Hlowmaster in forum Excel General
    Replies: 2
    Last Post: 10-30-2010, 04:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1