Separate the lines based on column header and its value

[harinsh]

Hi Team,

Hope anyone can help me, I have column heading fields like New, Old, Exist, Duplicate and each column has its own values and require vba code to get each line separately. For example for product A, if you have value in New and Duplicate amounting $1000 and $2000 respectively then it shows in single line in both columns but I should be two different columns saying one for New and other for Duplicate.

Example:
Input data
[TABLE="width: 399"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Product Name
[/TD]
[TD]New[/TD]
[TD]Old[/TD]
[TD]Exists[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]Product 1
[/TD]
[TD] $ 1,000[/TD]
[TD] $ - [/TD]
[TD] $ 100[/TD]
[TD] $ 10[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD] $ 2,000[/TD]
[TD] $ 20[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD] $ 300[/TD]
[TD] $ 500[/TD]
[TD] $ 1,000[/TD]
[TD] $ 100[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD] $ 1,000[/TD]
[TD] $ 250[/TD]
[TD] $ 350[/TD]
[TD] $ 500
[/TD]
[/TR]
</tbody>[/TABLE]


Output:

[TABLE="width: 243"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Product Name
[/TD]
[TD]Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]New[/TD]
[TD] $ 1,000[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]Exists[/TD]
[TD] $ 100
[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]Duplicate[/TD]
[TD] $ 10[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]New[/TD]
[TD] $ 2,000[/TD]
[/TR]
[TR]
[TD]Product 2
[/TD]
[TD]Old[/TD]
[TD] $ 20[/TD]
[/TR]
[TR]
[TD]Product 3
[/TD]
[TD]New[/TD]
[TD] $ 300[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]Old[/TD]
[TD] $ 500[/TD]
[/TR]
[TR]
[TD]Product 3
[/TD]
[TD]Exists[/TD]
[TD] $ 1,000[/TD]
[/TR]
[TR]
[TD]Product 3
[/TD]
[TD]Duplicate[/TD]
[TD] $ 100[/TD]
[/TR]
[TR]
[TD]Product 4
[/TD]
[TD]New[/TD]
[TD] $ 1,000[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]Old
[/TD]
[TD] $ 250[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]Exists[/TD]
[TD] $ 350[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]Duplicate[/TD]
[TD] $ 500
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you,

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG14Sep35
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 3)
nray(1, 1) = "Product Name": nray(1, 2) = "Type": nray(1, 3) = "Amount"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 2 To 5
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nray(c, 1) = Ray(n, 1)
            nray(c, 2) = Ray(1, Ac)
            nray(c, 3) = Ray(n, Ac)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 3)
     .Value = nray
     .Borders.Weight = 2
     .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[harinsh]

Super!! Really appreciate your efforts ....But if I want to customize the columns ..I mean to say ...if I want to increase the column range...where should I change in the code....it should copy the entire row and create separate and I have around 100 columns...will it works ?

Actual data format :
I have columns "A" to "FK" and condition should check in column "P","R","T","V","X","Z","AB","AD" and create the data as per the these columns ....

 

[MickG]

If you saying the data could have up to 100 columns , but the result will still only have 3, then you should just need to change the line below:-

From this:-

[COLOR=#000080]For[/COLOR] Ac = 2 To 5

To:-

[COLOR=#000080]For[/COLOR] Ac = 2 To ubound(ray,2)

 

[harinsh]

what should I change here....

With Sheets("Sheet2").Range("A1").Resize(c, 1)

incase change in the columns, I'm getting debug here....

 

[MickG]

That line on the original code was "Resize(c,3)" not "Resize(c,1)"
I increased my columns to 125, and the code still works, with the previous mod.
I Imagine your Results data should still be in 3 columns, so that should not change.
If its still not working , post a basic example of data that's not working.

 

[Rick Rothstein]

Hope anyone can help me, I have column heading fields like New, Old, Exist, Duplicate and each column has its own values and require vba code to get each line separately. For example for product A, if you have value in New and Duplicate amounting $1000 and $2000 respectively then it shows in single line in both columns but I should be two different columns saying one for New and other for Duplicate.

Example:
Input data
[TABLE="width: 399"]
<tbody>[TR]
[TD]Product Name
[/TD]
[TD]New[/TD]
[TD]Old[/TD]
[TD]Exists[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD] $ 1,000[/TD]
[TD] $ - [/TD]
[TD] $ 100[/TD]
[TD] $ 10[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD] $ 2,000[/TD]
[TD] $ 20[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD] $ 300[/TD]
[TD] $ 500[/TD]
[TD] $ 1,000[/TD]
[TD] $ 100[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD] $ 1,000[/TD]
[TD] $ 250[/TD]
[TD] $ 350[/TD]
[TD] $ 500[/TD]
[/TR]
</tbody>[/TABLE]

Are the values displayed in the cells the results of Cell Formatting? In other words, if I selected Product 1 in the New column, what is displayed in the Formula Bar... 1000? What about Product 1 in the Old column... is it 0 or blank?