Transfer Multiple Rows Data in Single Row Excluding Blanks

[Dheepak]

Hi All,

I have the data of Product in Row A and its price in row B. Similarly Product in Row C and its price in row D. in Total having 3 list of products and prices. Maximum 10 entries user can enter in each row. I want all the products from those 3 rows in single row G and its price in H without including the blanks. Here is the expectation .

[TABLE="width: 750"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]Product A
[/TD]
[TD]Price
[/TD]
[TD]Product A1
[/TD]
[TD]Price
[/TD]
[TD]Product A2
[/TD]
[TD]Price
[/TD]
[TD]Product A
[/TD]
[TD]Price of Product A
[/TD]
[/TR]
[TR]
[TD]Product B
[/TD]
[TD]Price
[/TD]
[TD]Product B1
[/TD]
[TD]Price
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Product B
[/TD]
[TD]Price of Product A
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Product C1
[/TD]
[TD]Price
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Product A1
[/TD]
[TD]Price of Product A1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product B1
[/TD]
[TD]Price of Product B1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product C1
[/TD]
[TD]Price of Product C1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product A2
[/TD]
[TD]Price of Product A2
[/TD]
[/TR]
</tbody>[/TABLE]

Can any one please help on this.

 

[MickG]

For data starting in "A1", try this for results starting "G1".

[COLOR="Navy"]Sub[/COLOR] MG24Jul46
[COLOR="Navy"]Dim[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ray = Range("A1").CurrentRegion.Resize(, 6)
ReDim nray(1 To UBound(ray, 1) * UBound(ray, 2), 1 To 2)

[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(ray, 2) [COLOR="Navy"]Step[/COLOR] 2
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray, 1)
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nray(c, 1) = ray(n, Ac)
            nray(c, 2) = ray(n, Ac + 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
Range("G1").Resize(c, 2).Value = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick