I have tried to build a formula that should give you the desired result. To explain the logic of what I have done:
1. Created an additional column called count in both the source (VBAP) and destination(E1P) sheets. The formula in all the cells of the Count column in the VBAP sheet gives the occurence number of the value in the "Sales Document" column. Occurrence number means whether it is the first occurence, second occurrence and so on of the "Sales Document" value in the sheet.
2. The formula in the Count column of the E1P sheet is more complicated. It checks to see if the "Document Head" value in E1P sheet exists in the "Sales Document" column of VBAP or not. If not, it returns 0. If the value exists, it counts the total number of occurrences of that value in VBAP sheet, and assigns the occurrence number to each value in the E1P sheet until it reaches the maximum number of occurrences in the VBAP sheet, and then starts from 1 again. E.g. if 13427653 occurs twice in VBAP, and occurs 4 times in E1P, the formula assigns occurrence number 1 to first occurrence, 2 to second occurrence, 1 to third occurrence, and 2 fourth occurrence.
3. The formula in Item column of E1P is a basic Index and Match formula that matches the values in the 2 columns of E1P with two columns of VBAP and returns the value from the Item column when there is a match.
I am attaching the file with the formulas.
|