Excel pro

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
hi ,

i have some data like gvn below

M/C SR NO. total b4 a4 cl4
k92661400 18632 8544 544 9544
K81667500 21844 18948 1948 948
H25102020 6756 884 2884 2988
J83414017 5320 3182 318 1820
A75900600 34064 2847 28474 2743
K83665211 12292 2818 287 9187
A73689140 12158 2470 208 9480


i need to this data to convert like

k92661400-b4 8544
k92661400-a4 544
k92661400-cl4 9544
K81667500-b4 18948
K81667500-a4 1948
K81667500-cl4 948
H25102020-b4 884
H25102020-a4 2884
H25102020-cl4 2988
J83414017-b4 3182
J83414017-a4 318
J83414017-cl4 1820
A75900600-b4 2847
A75900600-a4 28474
A75900600-cl4 2743
K83665211-b4 2818
K83665211-a4 287
K83665211-cl4 9187
A73689140-a4 2470
A73689140-b4 208
A73689140-cl4 9480




3 replies

Posts
8926
Registration date
Friday May 15, 2009
Status
Member
Last seen
October 7, 2010
2,108
Hi there,

Informations are missing in it as result you cannot just obtain an output like mentioned.

Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Assumptions
1. The sheet headers are at row 1
2. The data is to be created in sheet2

Sub massageData()

Dim lMaxRows As Long
Dim lMaxCols As Integer
Dim iStartCol As Integer
Dim iColCounter As Integer
Dim lRowCounter As Long
Dim lNewRows As Long

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    lMaxCols = Cells(1, Columns.Count).End(xlToLeft).Column
    
    
    iStartCol = 3
    lNewRows = 2
    
    For lRowCounter = 2 To lMaxRows
        
        For iColCounter = iStartCol To lMaxCols
        '-b4 8544
            Sheets("Sheet2").Cells(lNewRows, "A") = Cells(lRowCounter, 1) & "-" & Cells(1, iColCounter)
            Sheets("Sheet2").Cells(lNewRows, "B") = Cells(lRowCounter, iColCounter)
            lNewRows = lNewRows + 1
        Next iColCounter
        
    Next lRowCounter
    
End Sub
thanks can u tell me the new macro for below data

M/C SR NO. total b4 a4 cl4
k92661400 18632 8544 544 9544
K81667500 21844 18948 1948 948
H25102020 6756 884 2884 2988

convert to

k92661400 18632
k92661400-b4 8544
k92661400-a4 544
k92661400-cl4 9544
K81667500 21844
K81667500-b4 18948
K81667500-a4 1948
K81667500-cl4 948
H25102020 6756
H25102020-b4 884
H25102020-a4 2884
H25102020-cl4 2988
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Change this line
iStartCol = 3

to
iStartCol = 2
thanks a lot,......... but i need

total column shoud be like this

k92661400 18632

but when i run this macro show like

k92661400-18632
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If you try to step thru the code, you would have notice this this is happening in this line

Sheets("Sheet2").Cells(lNewRows, "A") = Cells(lRowCounter, 1) & "-" & Cells(1, iColCounter)

so if you change this line to

if(iColCounter = 2) then
Sheets("Sheet2").Cells(lNewRows, "A") = Cells(lRowCounter, 1)
else
Sheets("Sheet2").Cells(lNewRows, "A") = Cells(lRowCounter, 1) & "-" & Cells(1, iColCounter)
end if