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
Bookmarks