transpose and write values from several columns in one

    hello,can anyone plesase help if its possible in vba. I have tried it in access and coudnt figure it out. I have a table in sheet called "CURRENT_STATE" (this is a part of it, the entire has aruond 500 rows): What i would like to do is to have for exaple HP UC6018 values of F1 to F6 written in one column and indexd it with HP ID which is for example UC6018, that means all 12 values that are now written in 2 rows and 6 columns would be in new table written in just 1 row and 12 values.I have given an excell file in attachment. The desired tabel is in the green sheet calle "DESIRED LOOK" Also if there is an easier acess solution it would be fine too.thanks

    Re: transpose nad write values from several columns in one


    Hello,


    Attached is a formula to replicate your desired result ...


    Hope this will help

    Re: transpose nad write values from several columns in one


    hello,
    thanks for the help. It actualy forks fine, only problem is I have to drag the formula down the rows to get the results if i write it in a new sheet. If there is little rows it wouldnt be a problem, but in my case the end would be at around 6000 rows plus i cant tell how many rows exactly at tje end would be. Is there any way that you just marke the first field with enterd formula and dobule click on it, like at other formulas?

    Re: transpose nad write values from several columns in one


    Hello,


    There are two separate issues ...


    If you need an event macro to automatically calculate the number of rows ... it is indeed possible ... or


    Do you need a solution without VBA ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: transpose nad write values from several columns in one


    Hello again,


    Below is an event macro for the worksheet module to be tested ...


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$2" Then Exit Sub
Dim i As Long
Dim j As Long
i = Sheet1.Cells(Application.Rows.Count, "A").End(xlUp).Row
j = Sheet1.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Target.Copy Destination:=Range("A3:A" & (i - 1) * (j - 1) + 1)
Cancel = True
End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: transpose nad write values from several columns in one


    hello,
    unfortunaly it doesn t work. It returns an error in this line "i = Sheet1.Cells(Application.Rows.Count, "A").End(xlUp).Row".
    What could be the problem?

    Re: transpose nad write values from several columns in one


    It works like a charm. Thanks for the help


    Only in the sheet "Formula2" i cant see values in it.

    Re: transpose nad write values from several columns in one


    Quote from lolstre.loster;789958

    It works like a charm. Thanks for the help


    Only in the sheet "Formula2" i cant see values in it.


    Glad the "Formula1" is working as expected ...


    Regarding your second request "Formula2" just started to create Unique identifiers ... and did not have the time to go any further ...


    Are you after a macro or a formula solution ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: transpose nad write values from several columns in one


    OK ... then ...


    As soon as I have a moment ... I will dive into your macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: transpose nad write values from several columns in one


    Hello again,


    Regarding your second request "Formula2" ... cannot manage to find out the calculation you require ...


    Thanks for clarifying

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: transpose nad write values from several columns in one


    Hello,


    If my understanding of your two levels of transformation is correct ... see attached proposal ...


    Hope this will help ...

    Re: transpose nad write values from several columns in one


    Quote from lolstre.loster;790147

    hello,
    awsome, thank you very much. It couldnt be better.


    Glad you found a solution to your problem ... :wink:


    Thanks ... for your thanks ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login