Results 1 to 21 of 21

Thread: [RESOLVED] [EXCEL] using VBA copy one field from a column and concatenate with transpose

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Resolved [RESOLVED] [EXCEL] using VBA copy one field from a column and concatenate with transpose

    Hi All,

    I am fairly new to VBA and I needed some help.

    There is a data Set with one column as id and has different nos.

    then, I have a row, with different names.

    Now, I want to copy these ids first, into a new sheet and then copy the names one by one into the new sheet and concatenate the id + name together.

    For e.g.

    Id yellow blue orange
    1 25 74 56
    2 34 64 46
    3 45 49 99
    4 12 45 11
    5 10 16 22
    6 20 13 13
    7 48 19 14

    the columns are not consecutive as well.

    What I want to do is:-

    1yellow - 25
    2yellow - 34
    3yellow - 45
    4yellow - 12
    .
    .
    .

    1blue - 74
    2blue - 64
    3blue - 49
    4blue - 45
    5blue - 16
    6blue - 13

    likewise for orange.

    This all needs to be done, from a separate file where I have a command button, I press the button and then the data is pulled from file 1 which has yellow, blue and orange data, and then pushed into a new file say test.xls

    both files are closed and have separate sheets as well.

    Please let me know or help me with the code.
    I am good with C++ bt never really worked with VB, worked with VC++ nd other languages.

    so, all, i need is, if someone can help me with the code.

    Many thanks,
    Unknown
    Last edited by unknowninator; Feb 15th, 2012 at 11:11 AM. Reason: Sticky Notes

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    there would be several ways to do this

    regardless of the code what would you consider to be the simplest method to achieve the result?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Question Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    The simplest way to do it wud be,

    to copy the id's first into a new sheet.
    and take the row data into the same sheet.

    viz.

    1yelow
    2blue
    3orange
    4
    5
    6
    7


    now, multiply them one by one into a new sheet viz.

    1yellow
    2yellow
    3yellow
    4yellow
    .
    .
    .

    and the pull value from the main file corresponding to 1 yellow.
    we need not worry about order, since the order will remain the same if we copy them as is.

    but, how do i put this in VBA code??

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    anyone???

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    you want to go from a column based expression of the data to a single column of "id-tagged values"

    is that correct?

  6. #6

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    yeah,

    But not exactly what you said.

    I am attaching a file, it would make things clear.
    Attached Files Attached Files

  7. #7
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    of 2 column of data as

    id-tagged thing and the other the actual data

    (row with id ) (red infocolumn =value)(green info column =value)
    to
    red ids and values
    ..
    ..

    green ids and values
    ..
    ..

    like that?

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    yes,

    concatenate(ids + red) in one column and the corresponding values against it.

    And put it into a new sheet.

  9. #9
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    next questions then...

    where in new sheet, what is sheet name, do colours go on different sheets, are there a known number of colours and rows or is every thing quite arbitrary?

  10. #10

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    Saved in a new workbook actually, which is just an interim one, since its going to be used for various processes and then closed eventually outputting the results into a third file.

    No. of rows can vary and so can the no. of columns.
    the sheet name can be anything, doesnt matter.

    actually, its just a part of a very big calculation being performed every month, taking into account data from previous and current month.

    this is jst like 5% of the whole project.

    so, all i need to do is, put them into a new sheet and then progress from there.

  11. #11
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    there are always other ways to do these things!

    anyway this solution requires a function that can be told a few factors rather than one that works out what to do, the first version will be a sight easier to construct.

    i can develop the pseudo code first

    and if it makes sense WE can build the code

    how does that grab you?

  12. #12

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    makes sense...

    I was wondering, is it beneficial to call functions in a subroutine or make various subroutines ??

    bt yeah.... if u can give sme pseudo code... i can tweak nd play around wid it...

    am asking this... coz am using a form for the big program, and already have a few subroutines.... was wondering, which is the best way to optimize code and efficiency....

    use more functions or subroutines ??

  13. #13
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    here is a bit of code that works.. to get at the data

    Code:
    Sub translate_group_data(id As String, first_row As Integer, last_row As Integer, first_data As String, last_data As String, step_size As Integer)
    'fix column values
    fd = Range(first_data & "1").Column
    ld = Range(last_data & "1").Column
    
    For Group = fd To ld Step step_size
    For r = first_row To last_row
    
    
    Debug.Print Range(id & r).Value & Cells(2, Group - 1).Value & vbTab & Cells(r, Group).Value
    
    Next r
    Next Group
    End Sub
    you need to calll it like this... based on your example.xls

    Code:
    translate_group_data "a",4,6,"c","i",3
    as you see it can access the appropriate information, now we just need to output the data...

    proof of concept run in immediate window

    translate_group_data "a",4,6,"c","i",3
    123RED 879
    456RED 759
    789RED 459
    123Yellow 457
    456Yellow 427
    789Yellow 127
    123Green 654
    456Green 354
    789Green 324
    it is in a format that can be saved as tab separated and pulled into excell without any other work except the save of course!

    here to help

  14. #14

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    thanks for ur code.

    I have attached the file, am i missing something in this??

    coz its not working for me.

    many thanks and apologies for the delay in replying, since wasn't working over the weekend.
    Attached Files Attached Files

  15. #15
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    your tester has macros off...

    the code i send does that task of collecting the data from the structure you showed on your post..

    you need to alter the output of the routine so that it puts the stuff where you want it.

    you will not see and output because it is sent to the immediate window for test purposes only.

    here to help (if you need more to put stuff in appropriate place)

  16. #16

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    The macros are not off... its jst a window's security thing...
    it asks for enabling it as a security measure.

    I am still new to vba, so i don't know, how to alter the output or save it from what u have given me.

    Please let me know... thanks.

  17. #17
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    ok - can you goto the macro editing bit in your excel?

    if so this is where you will need to be to understand the code i sent and then we can look at what you need to make the appropriate output.

  18. #18

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    yes ofc,

    the file i sent u had the macro in it... i put in your code in that file.

    that is why i sent you.

  19. #19

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    I am able to see the output in the immediate window, by pressing ctrl+g,

    but its not correct.
    I will try and send another file, that will make it more clear.

  20. #20

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Question Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    please find attached a new file, which makes things mroe clear.

    Please let me know, how to go forward from here.

    Regards,
    Andy
    Attached Files Attached Files

  21. #21

    Thread Starter
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Arrow Re: [EXCEL] using VBA copy one field from a column and concatenate with transpose

    Hi Bill,

    As discussed, please find the 3 files attached, which are the input and output files.

    Many thanks for your help.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width