Columns need to be transposed based on unique table name

[subhapratimdas]

Input Available data snapshot

Table Column

ACCOUNT_POOL_MAPPING N_RUN_SKEY

ACCOUNT_TXNS V_PROD_CODE

APPLICATION_PLANS N_PLAN_TARGET_NUMBER

ATTRIBUTION_DEFINITION V_ATTRIBUTION_ID

ATTRIBUTION_EXECUTION_MASTER N_ATTRIBUTION_RUN_SKEY

BENCHMARK_MASTER V_CCY_CODE

BENCHMARK_RATES N_TENOR

CAMPAIGN_CHANNELS V_CAMPAIGN_ID

CAMPAIGN_PRODUCTS V_PROD_CODE

CAMPAIGN_RULES V_RULE_ID


Output Required

ACCOUNT_POOL_MAPPING N_RUN_SKEY FIC_MIS_DATE N_ACCT_SKEY

ACCOUNT_TXNS V_PROD_CODE FIC_MIS_DATE V_ACCT_NUMBER

APPLICATION_PLANS N_PLAN_TARGET_NUMBER FIC_MIS_DATE

ATTRIBUTION_DEFINITION V_ATTRIBUTION_ID

ATTRIBUTION_EXECUTION_MASTER N_ATTRIBUTION_RUN_SKEY

BENCHMARK_MASTER V_CCY_CODE V_BENCHMARK_CODE

BENCHMARK_RATES N_TENOR V_CCY_CODE V_BENCHMARK_IND FIC_MIS_DATE


Basedon Unique Table name, i need correspnding columns of the same table.


Tried all formulas , nothing worked, do you have a way to work out. There are 3578 rows meaning columns of 700 unique tables

 

[Luke M]

It's not clear from your example how data is created. E.g., you have FIC_MIS_DATE listed in the output, but I have no idea where that came from. You could upload a workbook or post a smaller/clearer example?

 

[SirJB7]

Hi, subhapratimdas!

Agree with Luke M. Please upload a file or embed text within back-ticks

, so as to get a clear idea of what is in each cell.

Regards!

 

[subhapratimdas]

Input Given

TABLE_NAME COLUMN_NAME

ACCOUNT_POOL_MAPPING N_RUN_SKEY

ACCOUNT_POOL_MAPPING FIC_MIS_DATE

ACCOUNT_POOL_MAPPING N_ACCT_SKEY

ACCOUNT_TXNS V_PROD_CODE

ACCOUNT_TXNS FIC_MIS_DATE

ACCOUNT_TXNS V_ACCT_NUMBER


Output required

ACCOUNT_POOL_MAPPING N_RUN_SKEY FIC_MIS_DATE N_ACCT_SKEY

ACCOUNT_TXNS V_PROD_CODE FIC_MIS_DATE V_ACCT_NUMBER


Sorry for the inconvenience

 

[Luke M]

Assuming original list is in columns A and B, and you already have a unique list of table names in column E (starting in E2), formula in F2 is:

=IF(COLUMNS($E2:E2)>COUNTIF($A:$A,$D2),"",INDEX($B:$B,SMALL(IF($A$2:$A$10=$D2,ROW($A$2:$A$10)),COLUMN(A$1))))


Note that this is an array formula, need to confirm using Ctrl+Shift+Enter, not just Enter. Copy formula to the right as far as you think would ever be needed, and then down as needed.

 

[SirJB7]

@Luke M

Hi!

I tried the formula but I get blanks, maybe I'm doing something wrong? I checked it twice.

Here's the file:

http://www.4shared.com/file/TSvW95Uo/Columns_need_to_be_transposed_.html

Regards!

 

[Luke M]

@SirJB7


Oops, I stated the wrong column. List of unique names should be in column D, (not E), with the formula in E2. My apologies.

 

[subhapratimdas]

Thanks a ton Luke, its amazing


Subha