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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Part number[/TD]
[TD]Locations[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]6417[/TD]
[TD]NN.34.03.A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6417[/TD]
[TD]NN.34.03.B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6419[/TD]
[TD]NN.31.02.A[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]6419[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]SS.07.01.A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]7189[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Q.22.06.B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8260[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl66, width: 95"]W.14.01.A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11059[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl66, width: 95"]W.14.01.A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]11059[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl66, width: 95"]W.14.01.B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]31846[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]R.30.02.A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

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?