Assistance required to extend the existing code with slight change

[bajrang2101]

Dear All,

I have the following code which works for 2 columns(B & C) in my excel when Column A does not exist but I want it to work for the format defined as under:

Script #	Step #	Module
Script 1	Step 1	Comm
	Step 2	Comm
	Step 3	PD
	Step 4	PD
	Step 5	Comm
	Step 6	Broner
	Step 7	Broner
	Step 8	Broner
Script 2	Step 1	Finance
	Step 2	Finance
	Step 3	Finance
	Step 4	PD
	Step 5	Comm
	Step 6	Comm
	Step 7
	Step 8	Comm
	Step 9	Comm
	Step 10	PD

<colgroup><col width="148" span="3" style="width:111pt"> </colgroup><tbody>
</tbody>

On running the macro it should give me the following desired results:

Script #	Step #	Module
Script 1	Step 1 Step 2	Comm
	Step 3 Step 4	PD
	Step 5	Comm
	Step 6 Step 7	Broner
Script 2	Step 1 Step 2 Step 3	Finance
	Step 4	PD
	Step 5 Step 6	Comm
	Step 7
	Step 8 Step 9	Comm
	Step 10	PD

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sub alternative()
Dim lr As Long, c(), a, i As Long, k As Long, x, y
 
lr = Range("A" & Rows.Count).End(xlUp).Row
ReDim c(1 To lr, 1 To 2)
x = Application.WorksheetFunction.Match("Module", Range("A1:B1"), 0)
y = IIf(x = 1, 2, 1)
With Range("A1").Resize(lr, 2)
    a = .Value
    c(1, x) = a(1, x): c(1, y) = a(1, y): k = 1
    For i = 2 To lr
        If a(i, x) <> a(i - 1, x) Then
            k = k + 1
            c(k, x) = a(i, x)
            c(k, y) = a(i, y)
        Else
            c(k, y) = c(k, y) & vbLf & a(i, y)
        End If
    Next i
    .ClearContents
    .Resize(k, 2) = c
End With
'
End Sub

Please help me.

Many Thanks,
Bajrang

 

[VBA Geek]

does this do what you want

Sub REORG()
With Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(A2="""",IF(C2<>"""",IF(C2<>C1,TRUE,""#N/A""),""#N/A""),TRUE)"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants, xlErrors).Offset(, -1).ClearContents
    .ClearContents
End With
End Sub

 

[bajrang2101]

Thank you so much. This is what I was looking for.

 

[bajrang2101]

One more query on this. Can this be tweaked to give me a range, as in Steps having same modules is displayed in a condensed form?

For Example:

Script #	Step #	Module
Script 1	Step 1 - Step 6	Comm-SD
	Step 7 - Step 17	FICO
	Step 18
	Step 19	FICO

<colgroup><col width="148" span="3" style="width:111pt"> </colgroup><tbody>
</tbody>

This would greatly reduce the manual effort on the result obtained earlier.

Best Regards,
Bajrang

 

[VBA Geek]

Theoretically could be. But I do not understand how does the table on the left translates into the right table?

Excel 2013
	A	B	C	D	E	F	G
1	Script #	Step #	Module		Script #	Step #	Module
2	Script 1	Step 1	Comm		Script 1	Step 1 - Step 6	Comm-SD
3		Step 2	Comm			Step 7 - Step 17	FICO
4		Step 3	PD			Step 18
5		Step 4	PD			Step 19	FICO
6		Step 5	Comm
7		Step 6	Broner
8		Step 7	Broner
9		Step 8	Broner
10	Script 2	Step 1	Finance
11		Step 2	Finance
12		Step 3	Finance
13		Step 4	PD
14		Step 5	Comm
15		Step 6	Comm
16		Step 7
17		Step 8	Comm
18		Step 9	Comm
19		Step 10	PD
Sheet8

 

[bajrang2101]

I think there is a confusion because the example I pasted is just for illustration and is not the result of the actual query.

Please refer the below table for clear understanding.

Original Data	Result obatained by current query	Desired Result (if Feasible)
Script #	Step #	Module	Script #	Step #	Module	Script #	Step #	Module
Script 1	SIT3_107.002_DV03_1	Comm-SD	Script 1	SIT3_107.002_DV03_1	Comm-SD	Script 1	SIT3_107.002_DV03_1 - SIT3_107.002_DV03_7	Comm-SD
	SIT3_107.002_DV03_2	Comm-SD		SIT3_107.002_DV03_2			SIT3_107.002_DV03_8 - SIT3_107.002_DV03_19	FICO
	SIT3_107.002_DV03_3	Comm-SD		SIT3_107.002_DV03_3			SIT3_107.002_DV03_20 - SIT3_107.002_DV03_21	Comm-SD
	SIT3_107.002_DV03_4	Comm-SD		SIT3_107.002_DV03_4			SIT3_107.002_DV03_22	FICO
	SIT3_107.002_DV03_5	Comm-SD		SIT3_107.002_DV03_5			SIT3_107.002_DV03_23
	SIT3_107.002_DV03_6	Comm-SD		SIT3_107.002_DV03_6			SIT3_107.002_DV03_24	FICO
	SIT3_107.002_DV03_7	Comm-SD		SIT3_107.002_DV03_7
	SIT3_107.002_DV03_8	FICO		SIT3_107.002_DV03_8	FICO
	SIT3_107.002_DV03_9	FICO		SIT3_107.002_DV03_9
	SIT3_107.002_DV03_10	FICO		SIT3_107.002_DV03_10
	SIT3_107.002_DV03_11	FICO		SIT3_107.002_DV03_11
	SIT3_107.002_DV03_12	FICO		SIT3_107.002_DV03_12
	SIT3_107.002_DV03_13	FICO		SIT3_107.002_DV03_13
	SIT3_107.002_DV03_14	FICO		SIT3_107.002_DV03_14
	SIT3_107.002_DV03_15	FICO		SIT3_107.002_DV03_15
	SIT3_107.002_DV03_16	FICO		SIT3_107.002_DV03_16
	SIT3_107.002_DV03_17	FICO		SIT3_107.002_DV03_17
	SIT3_107.002_DV03_18	FICO		SIT3_107.002_DV03_18
	SIT3_107.002_DV03_19	FICO		SIT3_107.002_DV03_19
	SIT3_107.002_DV03_20	Comm-SD		SIT3_107.002_DV03_20	Comm-SD
	SIT3_107.002_DV03_21	Comm-SD		SIT3_107.002_DV03_21
	SIT3_107.002_DV03_22	FICO		SIT3_107.002_DV03_22	FICO
	SIT3_107.002_DV03_23			SIT3_107.002_DV03_23
	SIT3_107.002_DV03_24	FICO		SIT3_107.002_DV03_24	FICO

<colgroup><col width="148" span="9" style="width:111pt"> </colgroup><tbody>
</tbody>