+ Reply to Thread
Results 1 to 5 of 5

Consolidate text from different Columns in one column with respective heading

  1. #1
    Registered User
    Join Date
    11-22-2011
    Location
    Noida
    MS-Off Ver
    Microsoft 365 MSO (16.0.13......) 32 bit
    Posts
    38

    Question Consolidate text from different Columns in one column with respective heading

    Hi,

    I am looking for a macro which can ease my work of consolidating responses from different products/teams. I have attached a sample format.

    I have to consolidate responses in column D, E and F in Column H. This action needs to be done from row no. 3 to 1000.

    Here is the flow chart of the action I want to do with this macro:

    For wsSheet1(RowH3:RowH1000)
    - Check if cell C3 is having text. If yes, then copy its heading (in Bold with colon) in F3 and then its respective text. In case there is no text ignore and move to next column E.
    - Check if column D3 is having text, if yes copy its heading (in Bold with colon) and its respective text in F3 under previous response.
    - Perform similar action with E3.

    I am not an expert with Macro's so I tried with Record macro. But that doesn't helps because I want to generalize this macro, meaning if I have more than one columns or may be different range of rows, I just change the column and row no. in the macro and use it further.

    Please have a look to the attached file. I have tried to work with record macro and results are in column F3. In adjacent column H, I have given the format I desire.

    Thanks in advance !!
    Your support is appreciated !!


    Br,
    gmalpani
    Attached Files Attached Files
    Last edited by gmalpani; 12-13-2011 at 03:17 PM. Reason: Chnging symbols

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Consolidate text from different Columns in one column with respective heading

    Would this in F3 do as you need? No VBa required.
    Please Login or Register  to view this content.
    Drag/Fill down

    Then, if required, Copy and Paste Special > Values and delete any columns that are no longer required
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    11-22-2011
    Location
    Noida
    MS-Off Ver
    Microsoft 365 MSO (16.0.13......) 32 bit
    Posts
    38

    Re: Consolidate text from different Columns in one column with respective heading

    Hi Marcol,

    Thanks for the reply !

    Actually, I am working on a tool and want this to be done by button click and that's why I was looking for a macro. I need the format as mentioned in column H.
    If you can spare some time and help me with a macro that would be a great help.

    Anyways this formula works well but I need certain modification in it. I want the headings to be in bold followed by a colon.
    e.g. Radio Access:and then its respective text. I have an understanding that this is not possible by using formula.

    Thanks for time and support !!

    Br,
    Gaurav Malpani
    Last edited by gmalpani; 12-11-2011 at 03:38 PM. Reason: add more info

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Consolidate text from different Columns in one column with respective heading

    You're correct that formatting text can't be done with formula.

    Try this, you can use a button to call the sub if you really must.
    Please Login or Register  to view this content.
    Last edited by Marcol; 12-12-2011 at 06:07 AM. Reason: Tidied code a little for clarity

  5. #5
    Registered User
    Join Date
    11-22-2011
    Location
    Noida
    MS-Off Ver
    Microsoft 365 MSO (16.0.13......) 32 bit
    Posts
    38

    Re: Consolidate text from different Columns in one column with respective heading

    Hi Marcol,

    Thanks for your valuable time and support.

    I tried this code, it works well but I find an issue when Column C is empty, its doesn't move to next column and give results as empty F cell.

    Considering your code as a base and with little efforts & common sense, I have updated the If else loops in the code and finally I got the desired results.

    Thanks once again. You guys are real GURU of excel and VBA.

    Here is the updated code:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1