Assistance required to extend the existing code with slight change

B

bajrang2101

New Member
Joined
Dec 4, 2014
Messages
21
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:

[TABLE="width: 444"]
<colgroup><col width="148" span="3" style="width:111pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 148"]Script #[/TD]
[TD="class: xl63, width: 148"]Step #[/TD]
[TD="class: xl63, width: 148"]Module[/TD]
[/TR]
[TR]
[TD="class: xl63"]Script 1[/TD]
[TD="class: xl64, width: 148"]Step 1[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 2[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]Step 3[/TD]
[TD="class: xl63"]PD[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]Step 4[/TD]
[TD="class: xl63"]PD[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]Step 5[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 6[/TD]
[TD="class: xl63"]Broner[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]Step 7[/TD]
[TD="class: xl63"]Broner[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]Step 8[/TD]
[TD="class: xl63"]Broner[/TD]
[/TR]
[TR]
[TD="class: xl63"]Script 2[/TD]
[TD="class: xl64, width: 148"]Step 1[/TD]
[TD="class: xl63"]Finance[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 2[/TD]
[TD="class: xl63"]Finance[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 3[/TD]
[TD="class: xl63"]Finance[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 4[/TD]
[TD="class: xl63"]PD[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 5[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 6[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 7[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 8[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 9[/TD]
[TD="class: xl63"]Comm[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl64, width: 148"]Step 10[/TD]
[TD="class: xl63"]PD
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 258"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Script #[/TD]
[TD]Step #[/TD]
[TD]Module[/TD]
[/TR]
[TR]
[TD]Script 1[/TD]
[TD]Step 1
Step 2[/TD]
[TD]Comm[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 3
Step 4[/TD]
[TD]PD[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 5[/TD]
[TD]Comm[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 6
Step 7[/TD]
[TD]Broner[/TD]
[/TR]
[TR]
[TD]Script 2[/TD]
[TD]Step 1
Step 2
Step 3[/TD]
[TD]Finance[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 4[/TD]
[TD]PD[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 5
Step 6[/TD]
[TD]Comm[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 8
Step 9[/TD]
[TD]Comm[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Step 10[/TD]
[TD]PD[/TD]
[/TR]
</tbody>[/TABLE]

Code: 
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
does this do what you want

Code: 
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
 
Upvote 0
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:

[TABLE="width: 444"]
<colgroup><col width="148" span="3" style="width:111pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 148"]Script #[/TD]
[TD="class: xl65, width: 148"]Step #[/TD]
[TD="class: xl65, width: 148"]Module[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 148"]Script 1[/TD]
[TD="class: xl67, width: 148"]Step 1 - Step 6[/TD]
[TD="class: xl66, width: 148"]Comm-SD[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl67, width: 148"]Step 7 - Step 17[/TD]
[TD="class: xl66, width: 148"]FICO[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl67, width: 148"]Step 18[/TD]
[TD="class: xl66, width: 148"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl67, width: 148"]Step 19[/TD]
[TD="class: xl66, width: 148"]FICO
[/TD]
[/TR]
</tbody>[/TABLE]

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

Best Regards,
Bajrang
 
Upvote 0
Theoretically could be. But I do not understand how does the table on the left translates into the right table?



Excel 2013
ABCDEFG
1Script #Step #ModuleScript #Step #Module
2Script 1Step 1CommScript 1Step 1 - Step 6Comm-SD
3Step 2CommStep 7 - Step 17FICO
4Step 3PDStep 18
5Step 4PDStep 19FICO
6Step 5Comm
7Step 6Broner
8Step 7Broner
9Step 8Broner
10Script 2Step 1Finance
11Step 2Finance
12Step 3Finance
13Step 4PD
14Step 5Comm
15Step 6Comm
16Step 7
17Step 8Comm
18Step 9Comm
19Step 10PD
Sheet8
 
Upvote 0
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.

[TABLE="width: 1332"]
<colgroup><col width="148" span="9" style="width:111pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 444, colspan: 3"]Original Data[/TD]
[TD="class: xl68, width: 444, colspan: 3"]Result obatained by current query[/TD]
[TD="class: xl71, width: 444, colspan: 3"]Desired Result (if Feasible)[/TD]
[/TR]
[TR]
[TD="class: xl63"]Script #[/TD]
[TD="class: xl63"]Step #[/TD]
[TD="class: xl63"]Module[/TD]
[TD="class: xl63"]Script #[/TD]
[TD="class: xl63"]Step #[/TD]
[TD="class: xl63"]Module[/TD]
[TD="class: xl63"]Script #[/TD]
[TD="class: xl63"]Step #[/TD]
[TD="class: xl63"]Module[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 148"]Script 1[/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_1[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD="class: xl64, width: 148"]Script 1[/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_1[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD="class: xl64, width: 148"]Script 1[/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_1 - SIT3_107.002_DV03_7[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_2[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_2[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_8 - SIT3_107.002_DV03_19[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_3[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_3[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_20 - SIT3_107.002_DV03_21[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_4[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_4[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_22[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_5[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_5[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_6[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_6[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_24[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_7[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_7[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_8[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_8[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_9[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_9[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_10[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_10[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_11[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_11[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_12[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_12[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_13[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_13[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_14[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_14[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_15[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_15[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_16[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_16[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_17[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_17[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_18[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_18[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_19[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_19[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_20[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_20[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_21[/TD]
[TD="class: xl64, width: 148"]Comm-SD[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_21[/TD]
[TD="class: xl64, width: 148"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_22[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_22[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_23[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_24[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD="class: xl64, width: 148"]SIT3_107.002_DV03_24[/TD]
[TD="class: xl64, width: 148"]FICO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Locked
  • Question Question
formula for randomly choosing an option
Replies
1
Views
135
Fluff
Fluff
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
309
Minh Tuan VN
M
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
459
Rnkhch
R
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
244
Alex Blakenburg
A

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top