1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Move data from column to row based on value of cell?

Discussion in 'Business Applications' started by maysemk, Oct 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. maysemk

    maysemk Thread Starter

    Joined:
    Oct 7, 2011
    Messages:
    3
    I have an Excel workbook with 400k rows Each set of rows contains a table name with the fields in the table. The number of fields is not the same for each table. For example:

    COLUMN A COLUMNB

    AV_ACTN_RSLT_VWACTN_RSLT_CDAV_ACTN_RSLT_VWDESCRAV_ACTN_RSLT_VWDESCRSHORTAV_ACTN_RSP_VWEMPLIDAV_ACTN_RSP_VWEXT_ORG_IDAV_ACTN_RSP_VWINSTITUTIONAV_ACTN_RSP_VWNAMEAV_ACTN_RSP_VWRSPL_ID

    AMT_X_SAL_TYPEWCS_PLAN_DESCR20AMT_X_SAL_TYPEWCS_TOT_AMOUNT

    I need to convert the field names so that they are in a row like this:

    AMT_X_SAL_TYPEWCS_PLAN_DESCR20WCS_TOT_AMOUNT

    Is there any way for me to do this programatically? I'm using the transpose feature but it is time consuming and I have 400k rows. So basically, I want something like if the field in column A row 1 matches row 2, move that field to the right of row 1 and so on until the table name changes.

    Or maybe i should dump the spreadsheet in access and try there...

    Thanks,

    Amy
     
  2. maysemk

    maysemk Thread Starter

    Joined:
    Oct 7, 2011
    Messages:
    3
    My columns did not come out right after I posted. So I may have one table name with 5 fields, another with two fields etc.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, welcome to the forum,
    If you would record a macro and then do the transpose part, stop the recording and take a look at the vba code that was created, you might get an idea how vba works and maybe even change it.
    Have you tried that?

    Your text in the post does not really show what you have and what you want, a sample would be much better.
    Seeing the numer of rows you speak of I imagine it's Excel 2007 or 2010?
    It's always a good habit to mention the version of the programs being used.
     
  4. maysemk

    maysemk Thread Starter

    Joined:
    Oct 7, 2011
    Messages:
    3
    Thank you. It's Excel 2007; I recorded a macro shown below; my issue is that the range will not always be the same; I need it to select based on the value in column A as sometimes column B has three rows that match up to column A or four rows etc...I cannot figure out where to attach a same of text...but I have for example four rows; each of the four rows has the same table name; column B has the four fields in that table. I need to move the four fields to the right of the table name, deleting the extra duplicate table name in column A. Make sense?

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+p
    '
    Range("B9:B13").Select
    End Sub
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+b
    '
    Range("B9:B13").Select
    Selection.Copy
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    End Sub
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I'd be happy to help you but then could you attach a sample of the Excel file (without personal or sensitive data?)
    Your explanation and attached macro code does not give a picture of what you have and what you want.

    The tabels and codes in the first part do not help.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1021141

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice