Pick up the data in the second row of a same car inventory #

Question

If 'Car Inventory #' is identical, always take take the second row in [Bought for / Resale price] minus the first row in [Bought for / Resale price] to get the markup amount, then multiply by the sales tax rate to get the tax owed to government. What formula could I use to solve the situation? Thanks.

Car Inventory #StatusTransactionBought for / Resale priceSales Tax 7%123456In stockInventory bought6000 123456Available for resalePriced for resale8000 234567In stockInventory bought500 234567Available for resalePriced for resale800 345678In stockInventory bought2000 345678Available for resalePriced for resale3500 456789In stockInventory bought8000 456789Available for resalePriced for resale12000 567890In stockInventory bought5000 567890Available for resalePriced for resale6800

TUSHAR050392 · Answer

Hey @tsao88 Another approach -

You can use the multi row formula directly and create a new column with double data type and tick group by to Car Inventory #. Use the below formula -

IF Car Inventory # = Row-1: Car Inventory # THEN (Bought for / Resale price - Row-1:Bought for / Resale price)*0.07 ELSEIF

Car Inventory # = Row+1: Car Inventory # THEN (Row+1:Bought for / Resale price - Bought for / Resale price)*0.07 ELSE 0 ENDIF

This is solution will work only if you have 2 or less rows for each car inventory #. Hope this helps

Qiu · Answer

@tsao88 
Combination of Tile and CrossTab should be suitable for this case.

Tile will generate the Sequence Number for your 1st and 2nd row, then bring them horizontally with CrossTab tool

0904-tsao88.yxmd