increment a number by amount of times a value exists in another column

[spyrule]

Hello,

I realize this will likely require an excel macro, but I'm not sure how to write this one...

I've got the following kind of data in one worksheet:

[TABLE="width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]SKU[/TD]
[TD="align: center"]PARENT SKU[/TD]
[/TR]
[TR]
[TD="align: center"]OB1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]PR1[/TD]
[TD="align: center"]OB1[/TD]
[/TR]
[TR]
[TD="align: center"]FR3[/TD]
[TD="align: center"]OB1[/TD]
[/TR]
[TR]
[TD="align: center"]X8[/TD]
[TD="align: center"]OB1[/TD]
[/TR]
[TR]
[TD="align: center"]LT[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]IT1[/TD]
[TD="align: center"]LT[/TD]
[/TR]
[TR]
[TD="align: center"]OI4[/TD]
[TD="align: center"]LT[/TD]
[/TR]
[TR]
[TD="align: center"]LK-4855[/TD]
[TD="align: center"]OB1[/TD]
[/TR]
[TR]
[TD="align: center"]IT-9855[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

I would like to build a destination worksheet like :

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]code[/TD]
[TD="align: center"]SKU[/TD]
[TD="align: center"]Linked_sku[/TD]
[TD="align: center"]Position[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]OB1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]OB1[/TD]
[TD="align: center"]PR1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]OB1[/TD]
[TD="align: center"]FR3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]OB1[/TD]
[TD="align: center"]X8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0.00
[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]OB1[/TD]
[TD="align: center"]LK-4855[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.00
[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]IT1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]CP[/TD]
[TD="align: center"]LT[/TD]
[TD="align: center"]OI4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

Most of this content I have working, the only thing I cannot figure out how to do easily, is to populate the position column. I have several thousand rows, and no way to "group" by parent sku before creating the list, so I need some kind of either macro or formula to create an incremental value based on how many times the parent SKU is listed besides a SKU, and then show that sequence in the destination worksheet.

Thanks in advance.

 

[Jeffrey Mahoney]

Assuming your SKU column in the bottom table is column B. Put this formula in Cell D2 and copy down
=IF(AND(B2<>"",C2<>""),COUNTIFS(B$2:B2,B2,C$2:C2,"<>"),"")

Jeff