+ Reply to Thread
Results 1 to 2 of 2

filter to only rows where a value in a column meets a condition

  1. #1
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    147

    filter to only rows where a value in a column meets a condition

    Problem - I have to sift through a large table of sales data and find the most recent selling point for each SKU. Here is what I have to work with:

    Column A - SKU
    Column B - Sale # (integer values 1, 2, 3....) highest value = most recent sale
    Column C - Selling Price

    Basically I need to find the largest value in column B per SKU, then return the SKu, Price for that row

    Thought I could do this with Subtotal using "For each change in SKU find MAX of column B" but that doesn't show me the price that corresponds to the row which contained the highest value in B.

    Seems like such a simple problem...

  2. #2
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    147
    Well I came up with a solution, but still interested in something more efficient then what I did.

    Basically I accomplished this by the following steps:

    1) Sorted table by SKU, then Order #
    2) Subtotaled list for every change in SKU find Max Order #
    3) Copy/pasted "Special - Values only" the subtotaled results into a new workbook
    4) Sorted the workbook to only show me the subtotaled rows, used text to columns to rip out the words "MAX" after the SKU
    5) Concatenated the SKU with the Order#
    6) Did a vlookup against the modified SKU value against the original data (made another column in original table with modded sku numbers) to return the price

    Didn't take long but still thinking there was a more efficient way

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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