VBA question for text from isometric drawings

[oguztuna]

[h=2]VBA for edit text and remove blank rows[/h]

I m finding out <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> for merging text with a space from below cells which has blank at the next column , and then remove the row that has blanks, as shown below. How can I solve this problem?

First Form

[TABLE="class: cms_table, width: 753"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]1.500WT - PIPE SSAWL API 5L BE[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PPXC209620[/TD]
[TD]7.0M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]API-5L-X65M PSL-2 LTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]PPXC209619[/TD]
[TD]1.2M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PSL-2 LTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]PPXC209617[/TD]
[TD]2.3M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PSL-2 LTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]1.500WT - ELL 90 LR WLD MSSSP-75 (PSO)[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PCHB11545[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BE A860 MSS SP75-WPHY-65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]1.500WT - FLG WN MSS-SP-44 BE/RF CL 900[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PFWA265709[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A707-L5 CL4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


After Edit

[TABLE="class: cms_table, width: 910"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]1.500WT - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PPXC209620[/TD]
[TD]7.0M[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]PPXC209619[/TD]
[TD]1.2M[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]PPXC209617[/TD]
[TD]2.3M[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]1.500WT - ELL 90 LR WLD MSSSP-75 (PSO) BE A860 MSS SP75-WPHY-65[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PCHB11545[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]1.500WT - FLG WN MSS-SP-44 BE/RF CL 900 A707-L5 CL4[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PFWA265709[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks,

Tuna​

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this in a copy of your workbook.

Original data was in A1:G10.
Macro has produced the data in A14:G18.

Excel Workbook
	A	B	C	D	E	F	G
1	1		1.500WT - PIPE SSAWL API 5L BE		30	PPXC209620	7.0M
2			API-5L-X65M PSL-2 LTD
3	2		SCH80 - PIPE SSAWL API 5L BE API-5L-X65M		24	PPXC209619	1.2M
4			PSL-2 LTD
5	3		SCH80 - PIPE SSAWL API 5L BE API-5L-X65M		18	PPXC209617	2.3M
6			PSL-2 LTD
7	4		1.500WT - ELL 90 LR WLD MSSSP-75 (PSO)		30	PCHB11545	1
8			BE A860 MSS SP75-WPHY-65
9	5		1.500WT - FLG WN MSS-SP-44 BE/RF CL 900		30	PFWA265709	1
10			A707-L5 CL4
11
12
13
14	1		1.500WT - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD		30	PPXC209620	7.0M
15	2		SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD		24	PPXC209619	1.2M
16	3		SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD		18	PPXC209617	2.3M
17	4		1.500WT - ELL 90 LR WLD MSSSP-75 (PSO) BE A860 MSS SP75-WPHY-65		30	PCHB11545	1
18	5		1.500WT - FLG WN MSS-SP-44 BE/RF CL 900 A707-L5 CL4		30	PFWA265709	1
19
Rearrange

Sub Rearrange()
  Dim a, b
  Dim i As Long, j As Long, k As Long
  
  With Range("A1:G" & Range("C" & Rows.Count).End(xlUp).Row)
    a = .Value
    ReDim b(1 To UBound(a) / 2, 1 To 7)
    For i = 1 To UBound(a) Step 2
      k = k + 1
      For j = 1 To 7
        b(k, j) = a(i, j)
      Next j
      b(k, 3) = b(k, 3) & " " & a(i + 1, 3)
    Next i
    .Offset(.Rows.Count + 3).Resize(k).Value = b
  End With
End Sub

 

[oguztuna]

Hi, thanks for this sharing, I d like to know that how this formula works for over 1000 cells and count of blank row is sometimes 3 and sometimes 2 that was given in previous example.

 

[Peter_SSs]

... count of blank row is sometimes 3 and sometimes 2 that was given in previous example.

If that means that your previous sample data was not representative of your actual data, then please give another small sample that is representative and also give the expected results again.

 

[oguztuna]

Hi,sample that I mentioned is given below.

First Form

[TABLE="width: 753"]
<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SHOP MATERIAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PT[/TD]
[TD][/TD]
[TD]DESCRIPTION[/TD]
[TD][/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]1.500WT - PIPE SSAWL API 5L BE[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PPXC209620[/TD]
[TD] 7.0M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]API-5L-X65M PSL-2 LTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]PPXC209619[/TD]
[TD] 1.2M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PSL-2 LTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]PPXC209617[/TD]
[TD] 2.3M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PSL-2 LTD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]1.500WT - ELL 90 LR WLD MSSSP-75 (PSO)[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PCHB11545[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BE A860 MSS SP75-WPHY-65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]1.500WT - FLG WN MSS-SP-44 BE/RF CL 900[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD]PFWA265709[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A707-L5 CL4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]PIPE SUPPORTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]- Base Support 30-24-BS01-1[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]TT04.600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]24-TG01-E-225 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]24-TG01-E-130 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]- Trunnion on Straight Pipe[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]TT17.18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]30-18-WA06-1150-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]Welded Pipe Shoe for LTCS Pipe (-46C to[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]AT502-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]+150C) 18-SH04-100-550 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]Shoe Gusset Plates (Double)[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]GT510-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SG02-329-330-12-170-LTCS Low Temperature[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Carbon Steel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ERECTION MATERIALS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PT[/TD]
[TD][/TD]
[TD]DESCRIPTION[/TD]
[TD][/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]PIPE SUPPORTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]Acoustic Isolation Pad Without Backing[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Plate (Uninsulated Pipe) 24-IS06-200-230[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]Acoustic Isolation Pad Without Backing[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Plate (Uninsulated Pipe) 24-IS06-100-230[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]Special Support[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]SPECIAL.24[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]Pedestal Guide (Heavy Load)[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]24-BG06-250-255-20-2-3-10 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Pedestal Guide (Heavy Load)[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]24-BG06-160-255-20-2-2-6 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]Acoustic Isolation Pad (Type E) with[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]AT40E-18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Backing Plate 18-IS07-E-200-200 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]Shoe Line Stop (Heavy Load)[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]ST505-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]18-LS03-210-250-20-2-3-150-6 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]Special Support[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]SPECIAL.18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]Acoustic Isolation Pad Without Backing[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD]AT548-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Plate (Uninsulated Pipe Stop)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]18-IS06-160-200 .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Expected Result

[TABLE="width: 897"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]SHOP MATERIAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PT[/TD]
[TD]DESCRIPTION[/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD][/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]1.500WT - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD="align: right"]30[/TD]
[TD]PPXC209620[/TD]
[TD] 7.0M[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD="align: right"]24[/TD]
[TD]PPXC209619[/TD]
[TD] 1.2M[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD="align: right"]18[/TD]
[TD]PPXC209617[/TD]
[TD] 2.3M[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]1.500WT - ELL 90 LR WLD MSSSP-75 (PSO) BE A860 MSS SP75-WPHY-65[/TD]
[TD="align: right"]30[/TD]
[TD]PCHB11545[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1.500WT - FLG WN MSS-SP-44 BE/RF CL 900 A707-L5 CL4[/TD]
[TD="align: right"]30[/TD]
[TD]PFWA265709[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PIPE SUPPORTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]- Base Support 30-24-BS01-1[/TD]
[TD="align: right"]24[/TD]
[TD]TT04.600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad 24-TG01-E-225 .[/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad 24-TG01-E-130 .[/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]- Trunnion on Straight Pipe 30-18-WA06-1150-1[/TD]
[TD="align: right"]18[/TD]
[TD]TT17.18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Welded Pipe Shoe for LTCS Pipe (-46C to +150C) 18-SH04-100-550 .[/TD]
[TD="align: right"]18[/TD]
[TD]AT502-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Shoe Gusset Plates (Double) SG02-329-330-12-170-LTCS Low Temperature Carbon Steel[/TD]
[TD="align: right"]18[/TD]
[TD]GT510-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ERECTION MATERIALS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PT[/TD]
[TD]DESCRIPTION[/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD][/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PIPE SUPPORTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Acoustic Isolation Pad Without Backing Plate (Uninsulated Pipe) 24-IS06-200-230 .[/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Acoustic Isolation Pad Without Backing Plate (Uninsulated Pipe) 24-IS06-100-230[/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Special Support[/TD]
[TD="align: right"]24[/TD]
[TD]SPECIAL.24[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Pedestal Guide (Heavy Load) 24-BG06-250-255-20-2-3-10 .[/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Pedestal Guide (Heavy Load) 24-BG06-160-255-20-2-2-6 .[/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Acoustic Isolation Pad (Type E) with Backing Plate 18-IS07-E-200-200 .[/TD]
[TD="align: right"]18[/TD]
[TD]AT40E-18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Shoe Line Stop (Heavy Load) 18-LS03-210-250-20-2-3-150-6 .[/TD]
[TD="align: right"]18[/TD]
[TD]ST505-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Special Support[/TD]
[TD="align: right"]18[/TD]
[TD]SPECIAL.18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]Acoustic Isolation Pad Without Backing Plate (Uninsulated Pipe Stop) 18-IS06-160-200 .[/TD]
[TD="align: right"]18[/TD]
[TD]AT548-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Many Thanks

 

[Peter_SSs]

Without the borders like you posted in post #1, it's hard to tell what is in what column.

For the original data ..

1. What column is 'SHOP MARERIAL' in?

2. What column is 'DESCRIPTION' in?

3. What column is the heading 'NB' in?

4. Is 'SPM IDENT' in a single cell? Which cell?

5. Some data rows (eg 6 - Base Support...) only have one row. So what logic should we use to decide that 'A707-L5 CL4' is not a stand-alone line like 'PIPE SUPPORTS'?

6. What logic should we use to decide that 'PIPE SUPPORTS' - the first one - (being the second row in that area without an entry in the first column) goes on a line by itself in the results, but 'Carbon Steel' (also being the second row in that area without an entry in the first column) does NOT go on a line by itself but gets combined with the rows above?

 

[oguztuna]

Hi,

I specified the columns and rows at below,

The logic is ;

if it s just one data row, I wanna keep it same ,

if it s two data row and next column has one number and one blank cell under this cell , I wanna merge the data rows in one cell which has the number at next column cell.

if it s tree data row, the logic is same as 2 data row. I wanna delete 2 blank row and merge the 3 data rows in one cell.

Basically, I wanna keep the PT. No column numbers and delete blank cells at this column, and I wanna merge the datas which has the blank at next column as given below expected value.

First form

[TABLE="width: 1128"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SHOP MATERIAL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PT[/TD]
[TD] [/TD]
[TD]DESCRIPTION[/TD]
[TD] [/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No.[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]1.500WT - PIPE SSAWL API 5L BE[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD]PPXC209620[/TD]
[TD] 7.0M[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]API-5L-X65M PSL-2 LTD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]PPXC209619[/TD]
[TD] 1.2M[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PSL-2 LTD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]PPXC209617[/TD]
[TD] 2.3M[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]PSL-2 LTD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD]1.500WT - ELL 90 LR WLD MSSSP-75 (PSO)[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD]PCHB11545[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BE A860 MSS SP75-WPHY-65[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD]1.500WT - FLG WN MSS-SP-44 BE/RF CL 900[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD]PFWA265709[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A707-L5 CL4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD]PIPE SUPPORTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD]- Base Support 30-24-BS01-1[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]TT04.600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]24-TG01-E-225 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]24-TG01-E-130 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD]- Trunnion on Straight Pipe[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]TT17.18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]30-18-WA06-1150-1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD]Welded Pipe Shoe for LTCS Pipe (-46C to[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]AT502-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]+150C) 18-SH04-100-550 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD]Shoe Gusset Plates (Double)[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]GT510-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SG02-329-330-12-170-LTCS Low Temperature[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Carbon Steel[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]ERECTION MATERIALS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]PT[/TD]
[TD] [/TD]
[TD]DESCRIPTION[/TD]
[TD] [/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]No.[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD] [/TD]
[TD]PIPE SUPPORTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad Without Backing[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Plate (Uninsulated Pipe) 24-IS06-200-230[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD] [/TD]
[TD] [/TD]
[TD].[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD="align: right"]13[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad Without Backing[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Plate (Uninsulated Pipe) 24-IS06-100-230[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD="align: right"]14[/TD]
[TD] [/TD]
[TD]Special Support[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]SPECIAL.24[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD]Pedestal Guide (Heavy Load)[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]24-BG06-250-255-20-2-3-10 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD="align: right"]16[/TD]
[TD] [/TD]
[TD]Pedestal Guide (Heavy Load)[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]24-BG06-160-255-20-2-2-6 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD="align: right"]17[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad (Type E) with[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]AT40E-18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Backing Plate 18-IS07-E-200-200 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[TD]Shoe Line Stop (Heavy Load)[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]ST505-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]18-LS03-210-250-20-2-3-150-6 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[TD]Special Support[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]SPECIAL.18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad Without Backing[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]AT548-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Plate (Uninsulated Pipe Stop)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]18-IS06-160-200 .[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Expected Value

[TABLE="width: 1128"]
<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SHOP MATERIAL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PT[/TD]
[TD] [/TD]
[TD]DESCRIPTION[/TD]
[TD] [/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No.[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]1.500WT - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD]PPXC209620[/TD]
[TD] 7.0M[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]PPXC209619[/TD]
[TD] 1.2M[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD]SCH80 - PIPE SSAWL API 5L BE API-5L-X65M PSL-2 LTD[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]PPXC209617[/TD]
[TD] 2.3M[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD]1.500WT - ELL 90 LR WLD MSSSP-75 (PSO) BE A860 MSS SP75-WPHY-65[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD]PCHB11545[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD]1.500WT - FLG WN MSS-SP-44 BE/RF CL 900 A707-L5 CL4[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD]PFWA265709[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD]PIPE SUPPORTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD]- Base Support 30-24-BS01-1[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]TT04.600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad 24-TG01-E-225 .[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD]Trunnion Bearing Plate for Acoustic Pad 24-TG01-E-130 .[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT649-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD]- Trunnion on Straight Pipe 30-18-WA06-1150-1[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]TT17.18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD]Welded Pipe Shoe for LTCS Pipe (-46C to +150C) 18-SH04-100-550 .[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]AT502-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD] Shoe Gusset Plates (Double) SG02-329-330-12-170-LTCS Low Temperature Carbon Steel[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]GT510-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]ERECTION MATERIALS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]PT[/TD]
[TD] [/TD]
[TD]DESCRIPTION[/TD]
[TD] [/TD]
[TD] NB[/TD]
[TD]SPM IDENT[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]No.[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD](IN)[/TD]
[TD]CODE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD] [/TD]
[TD]PIPE SUPPORTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad Without Backing Plate (Uninsulated Pipe) 24-IS06-200-230 .[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD="align: right"]13[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad Without Backing Plate (Uninsulated Pipe) 24-IS06-100-230[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]AT533-600[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD="align: right"]14[/TD]
[TD] [/TD]
[TD]Special Support[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]SPECIAL.24[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD]Pedestal Guide (Heavy Load) 24-BG06-250-255-20-2-3-10 .[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD="align: right"]16[/TD]
[TD] [/TD]
[TD]Pedestal Guide (Heavy Load) 24-BG06-160-255-20-2-2-6 .[/TD]
[TD] [/TD]
[TD="align: right"]24[/TD]
[TD]GT665-600[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD="align: right"]17[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad (Type E) with Backing Plate 18-IS07-E-200-200 .[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]AT40E-18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[TD]Shoe Line Stop (Heavy Load) 18-LS03-210-250-20-2-3-150-6 .[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]ST505-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[TD]Special Support[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]SPECIAL.18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD]Acoustic Isolation Pad Without Backing Plate (Uninsulated Pipe Stop) 18-IS06-160-200 .[/TD]
[TD] [/TD]
[TD="align: right"]18[/TD]
[TD]AT548-450[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Try

Sub Rearrange_v2()
  Dim a, b
  Dim i As Long, j As Long, k As Long
  
  With Range("A1:G" & Range("C" & Rows.Count).End(xlUp).Row)
    a = .Value
    ReDim b(1 To UBound(a), 1 To 7)
    For i = 1 To UBound(a)
      Select Case True
        Case a(i, 3) = "", a(i, 1) <> ""
          k = k + 1
          For j = 1 To 7
            b(k, j) = a(i, j)
          Next j
        Case Else
          b(k, 3) = b(k, 3) & " " & a(i, 3)
      End Select
    Next i
    .Offset(.Rows.Count + 3).Resize(k).Value = b
  End With
End Sub

 

[oguztuna]

Thanks , it worked perfect.

 

[Peter_SSs]

Thanks , it worked perfect.

Good news! Thanks for letting us know.