Need quick help on VBA code to translate data

[SKV]

Hi,

Can someone help me write a code for translating the data from matrix format to a tabular format

Example: I have data in the format

Col 1 Col 2 Col 3 Col N
Row A val A1 Val A2 val A3 val AN
Row B val B1 Val B2 val B3 val BN
Row C val C1 val C2 val C3 val CN

I want to convert this data to the tabular format that is

Row Col Value
Row A Col1 val A1
Row B Col1 val B1
Row C Col1 val C1
Row A Col 2 Val A2
Row B Col 2 Val B2
Row C Col 2 val C2

Till now I was able to use queries for this translation as my meta data was same.

However recently I have start getting tables with different column names and also different number of columns.

So I am not looking for a code to automatically translate data with new fields names.

Please share if you have a code or guide me.

Thanks
SKV

 

[alansidman]

I was able to achieve your expected results using a UNION query. Depending on how many columns you have this may be a possible solution.

SELECT Table1.col1 AS Row, "col1" AS Col, Table1.col2 AS [Value]
FROM Table1;
Union
SELECT Table1.col1 AS Row, "col2" AS Col, Table1.col3 AS [Value]
FROM Table1;
Union
SELECT Table1.col1 AS Row, "col3" AS Col, Table1.col4 AS [Value]
FROM Table1;

I know that there is a limitation on how many unions you can have, but I do not know that number. Hopefully, someone else in the forum will help here or have a less tedious solution.

Alan

 

[SKV]

I was able to achieve your expected results using a UNION query. Depending on how many columns you have this may be a possible solution.

SELECT Table1.col1 AS Row, "col1" AS Col, Table1.col2 AS [Value]
FROM Table1;
Union
SELECT Table1.col1 AS Row, "col2" AS Col, Table1.col3 AS [Value]
FROM Table1;
Union
SELECT Table1.col1 AS Row, "col3" AS Col, Table1.col4 AS [Value]
FROM Table1;

I know that there is a limitation on how many unions you can have, but I do not know that number. Hopefully, someone else in the forum will help here or have a less tedious solution.

Alan

Thanks Alan !!!!

I have been using this up till now and I said my new data sets are different all the time so I want a VBA code to be flexible on fields names etc. I am sure there are smart people on this forum who have some solution on this.... so lets watch for them to reply

Thanks again.

 

[alansidman]

One more thought as I was driving home. Import new data into temp tables and then run an update query with the correct field names to the proper tables. You could then run your Union Query.

 

[SKV]

I am finally able to write a code to do the transformation. It does 90% of the job and then you need minor manual adjustments.

The code can turn any numbers columns without a problem.

Let me know if you need this code

 

[alansidman]

It would be nice to post it so that if others have a similar situation, they could rely (attempt to use) your code.

Glad you solved it.