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
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