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

[tsao88]

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 #	Status	Transaction	Bought for / Resale price	Sales Tax 7%
123456	In stock	Inventory bought	6000
123456	Available for resale	Priced for resale	8000
234567	In stock	Inventory bought	500
234567	Available for resale	Priced for resale	800
345678	In stock	Inventory bought	2000
345678	Available for resale	Priced for resale	3500
456789	In stock	Inventory bought	8000
456789	Available for resale	Priced for resale	12000
567890	In stock	Inventory bought	5000
567890	Available for resale	Priced for resale	6800

[Qiu]

@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

[TUSHAR050392]

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