+ Reply to Thread
Results 1 to 4 of 4

Formula/macro - Apply certain formula as per duplicate cells in column L

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Formula/macro - Apply certain formula as per duplicate cells in column L

    Hi all
    I need formula or macro solution to populate some information to column AB. Achieving this via formula should be possible I think (but any solution will do as long as it gets the job done.) Formula (or macro) should check all the duplicate cells in column L.

    In column L there are some part numbers. So this data in column L is the one that should used to determine which lines are the duplicates. Lets say like a snap shot below


    L57952222222
    L57951111111
    L57951111111
    L57952222222
    L57951111111
    L57952222222
    L57951111111
    L57952222222
    L57951111111


    For the sake of simplicity lets assume I have filtered in part L57951111111 so I can explain more easily what I'm trying to achieve. Also row markings on the left are figurative just to explain what I'm trying to achieve (they do not represent the real rows on spreadsheet.)
    This below is shot how it is Before formula/macro.

    -------------- ------------L ------------------- --AB-- ---------------AC ---------------AD ---------------AE ----------------AF
    Row 2---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ----------------- 0 ----------------- 0
    Row 3---- L57951111111 ------------ empty ---------------- 0 ---------------- 5 ---------------- -2 ----------------- 3
    Row 4---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ---------------- -2 ---------------- -2
    Row 5---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ---------------- -2 ---------------- -2
    Row 6---- L57951111111 ------------ empty ---------------- 0 ---------------- 5 ----------------- 0 ----------------- 5
    Row 7---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ---------------- -2 ---------------- -2

    So column AB is where we want to populate the information. As you can see its currently empty. Now I would like the formula to always populate number 0 (zero) to the top cell (AB row 2) of of these duplicate lines,
    then AB row 3 = AF Row 2
    then AB row 4 = AF Row 3
    then AB row 5 = AF Row 4
    then AB row 6 = AF Row 5
    then AB row 7 = AF Row 6


    So essentially with the exception of the very first row of these duplicate rows, from AB row 2 downwards (inclusive) it takes the value from column AF one row above. Remember, top line of the duplicate lines has to be 0.

    Columns AC to Column AF all contain some formulas. AE and AF will change according to the values in column AB. Results in column AE is the real data that I'm after.


    The below is mock example of how it would look like after AB would be populated:


    After
    -------------- ----------L ------------------ AB ---------------AC ---------------AD ----------------AE ----------------AF
    Row 2---- L57951111111 ------------ 0 ---------------- 0 ---------------- 0 ------------------ 0 ----------------- 0
    Row 3---- L57951111111 ------------ 0 ---------------- 0 ---------------- 5 ----------------- -2 ----------------- 3
    Row 4---- L57951111111 ------------ 3 ---------------- 0 ---------------- 0 ------------------ 1 ----------------- 1
    Row 5---- L57951111111 ------------ 1 ---------------- 0 ---------------- 0 ----------------- -1 ---------------- -1
    Row 6---- L57951111111 ----------- -1 ---------------- 0 ---------------- 5 ----------------- -1 ----------------- 4
    Row 7---- L57951111111 ------------ 4 ---------------- 0 ---------------- 0 ------------------ 2 ----------------- 2


    And below is example how it would look unfiltered

    L57951111111 ------------ 0 ---------------- 0 ---------------- 0 ------------------ 0 ----------------- 0
    L57952222222 ------------ 0 ---------------- 0 ---------------- 0 ------------------ 0 ----------------- 0
    L57952222222 ------------ 0 ---------------- 0 ---------------- 0 ------------------ 0 ----------------- 0
    L57951111111 ------------ 0 ---------------- 0 ---------------- 5 ----------------- -2 ----------------- 3
    L57951111111 ------------ 3 ---------------- 0 ---------------- 0 ------------------ 1 ----------------- 1
    L57952222222 ------------ 0 ---------------- 0 ---------------- 0 ------------------ 0 ----------------- 0
    L57951111111 ------------ 1 ---------------- 0 ---------------- 0 ----------------- -1 ---------------- -1
    L57951111111 ----------- -1 ---------------- 0 ---------------- 5 ----------------- -1 ----------------- 4
    L57951111111 ------------ 4 ---------------- 0 ---------------- 0 ------------------ 2 ----------------- 2


    I have attached spreadsheet with real data. It has sheet "before" and "after". On sheet "after" I have filtered in one part (L57553243210) and manually filled in the cells in column AB. So if you would insert some sort of formula in column AB on sheet before, filter in this same part number (L57553243210), then if you flick between the two sheets they should look the same.

    I hope someone knows how to achieve this. I would be very grateful.


    Cheers
    Attached Files Attached Files
    Last edited by rain4u; 09-21-2011 at 03:56 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula/macro - Apply certain formula as per duplicate cells in column L

    You can achieve this with a formula, but your current formula in column AF actually REFERS to column AB, so if we put values in that column the AF values change and there's no way to actually replicate your AFTER sheet.

    Anyway, the formula in AB2 that works after you remove the +zflex_mid_balance from the AF formula is:

    =IF(ISERROR(LOOKUP(2,1/($L1:$L$1=$L2),$AF$1:$AF1)), 0, LOOKUP(2,1/($L1:$L$1=$L2),$AF$1:$AF1))

    ...copied down.
    _________________
    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!)

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Formula/macro - Apply certain formula as per duplicate cells in column L

    I finally have had time to continue with my macros. God bless for weekends!
    JBeaucaire, you actually did solve it! Brilliant!!!!!

    Values in AF are supposed to change. Same goes with with AE. This was presented in the example though I did not mention it in explanations.
    Note how values in AE and AF and change.

    Before
    -------------- ------------L ------------------- --AB-- ---------------AC ---------------AD ---------------AE ----------------AF
    Row 2---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ----------------- 0 ----------------- 0
    Row 3---- L57951111111 ------------ empty ---------------- 0 ---------------- 5 ---------------- -2 ----------------- 3
    Row 4---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ---------------- -2 ---------------- -2
    Row 5---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ---------------- -2 ---------------- -2
    Row 6---- L57951111111 ------------ empty ---------------- 0 ---------------- 5 ----------------- 0 ----------------- 5
    Row 7---- L57951111111 ------------ empty ---------------- 0 ---------------- 0 ---------------- -2 ---------------- -2



    After
    -------------- ----------L ------------------ AB ---------------AC ---------------AD ----------------AE ----------------AF
    Row 2---- L57951111111 ------------ 0 ---------------- 0 ---------------- 0 ------------------ 0 ----------------- 0
    Row 3---- L57951111111 ------------ 0 ---------------- 0 ---------------- 5 ----------------- -2 ----------------- 3
    Row 4---- L57951111111 ------------ 3 ---------------- 0 ---------------- 0 ------------------ 1 ----------------- 1
    Row 5---- L57951111111 ------------ 1 ---------------- 0 ---------------- 0 ----------------- -1 ---------------- -1
    Row 6---- L57951111111 ----------- -1 ---------------- 0 ---------------- 5 ----------------- -1 ----------------- 4
    Row 7---- L57951111111 ------------ 4 ---------------- 0 ---------------- 0 ------------------ 2 ----------------- 2

    The real information that I am after is in AE. I just needed a formula to go into AB to complete it. And you formula works like charm. See attached spreadsheet where your formula produces exactly the result required on as per sheet "after",


    Could you be so kind and break the formula down and explain me in simple English how it works or what it does.
    Can talk me through step by step with LOOKUP(2,1/($L1:$L$1=$L2),$AF$1:$AF1)

    I just want to learn this technique for future references.


    Thank you for the formula.
    Amazing stuff
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula/macro - Apply certain formula as per duplicate cells in column L

    This is a popular "trick" formula, it is designed to find the LAST cell in the lookup range that meets the criteria, then return the value from the return-range in the same position.

    LOOKUP(2,1/($L1:$L$1=$L2),$AF$1:$AF1)


    The easiest way to understand it is to watch it in action. Pick a cell about 5-6 rows down, click on it, then use the Evaluate Formula icon from the Formula Auditing toolbar. Step through the formula one step at a time to watch it unfold.

    In a nutshell, the lookup range is a test to see how many of the cell = a specific cell. When that part calculate, the result is a bunch 1s and errors. Then we divide that array into the number 1 to get a bunch of fractions, and the errors become DIV/0 errors. Since the LOOKUP() is looking for the number 2 in the list, and the array we created is all numbers less than zero, it will never find that number. LOOKUP() by design always returns the last number in the array that is less than the searched number, since ALL the numbers will be less than 2, we always get a result that equals the LAST cell in column L that matches our current column L value.

    That gives us a "position" in column L.

    Then we get back the value from the return-range in the same row.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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