Need help to create a new List

[pbhogi]

I have....

Column A) Part Number,
Column B) Locations of that parts and
Column C) Available qty per location for each part

The locations (Column B) I have are divided into A(Right side of the rack) and B (Left side of the rack) for each Rack.

Ex. location NN.34.03.A and NN.34.03.B are physically just a single rack but A and B indicates the sides of that particular rack.

What I want to do is to

1. Identify such locations which has SAME PART on both A & B location,
2. Change the A location and remove the "A" from the end. ( Ex. NN.34.03.A to NN.34.03)
3. Delete the B location
4. Sum the qty in column C for both the locations.

There're more than 6300 Parts and 23,000 locations so no way to do it manually.

Part number	Locations	Qty
6417	NN.34.03.A	5
6417	NN.34.03.B	4
6419	NN.31.02.A	8
6419	SS.07.01.A <tbody> </tbody>	75
7189	Q.22.06.B <tbody> </tbody>	1
8260	W.14.01.A <tbody> </tbody>	4
11059	W.14.01.A <tbody> </tbody>	9
11059	W.14.01.B <tbody> </tbody>	30
31846	R.30.02.A <tbody> </tbody>	4

<tbody>
</tbody>

Any help would be much appreciated.
Thank you so much in advance.

 

[sijpie]

What are your column letters (for the complete table and for the three columns shown in your example)? Are they really only Column A, B and C, or part of a larger table?