Previous Topic Next Topic

Excel pro

Solved/Closed
usha - Mar 29, 2010 at 12:48 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 30, 2010 at 08:16 AM
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




Related:

3 responses

Answer 1 / 3
closeup22 Posts 8922 Registration date Friday May 15, 2009 Status Member Last seen October 7, 2010 2,099
Mar 29, 2010 at 05:58 AM
Hi there,

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

Thanks
0
Answer 2 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 29, 2010 at 03:31 PM
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
0
usha
Mar 30, 2010 at 12:01 AM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 30, 2010 at 06:13 AM
Change this line
iStartCol = 3

to
iStartCol = 2
0
Answer 3 / 3
usha
Mar 30, 2010 at 06:24 AM
thanks a lot,......... but i need

total column shoud be like this

k92661400 18632

but when i run this macro show like

k92661400-18632
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 30, 2010 at 08:16 AM
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
0