Help writing function/routine for creating table

[myexcel]

Hi any helper:

I have an Excel table:

DocNum Test1 Test2 Test3 Test4............. TestN
Doc1 x x
x
Doc2 x
Doc3
Doc4 x

 

[CLR]

Hi.........

If you're really seeking some help, we'll need a little more than this to go
on. You need to tell us what you want to accomplish, what you've tried that
did not work, how it did not work, etc etc.............please post back with
some more information.

Vaya con Dios,
Chuck, CABGx3

 

[myexcel]

Thank you for interesting in helping my weakness.

Here is a general problem, I have a large table with 150 columns an
20500 rows. The head field of the columns are DocNum, Test1, Test2
Test 3....Test150.

Column DocNum contains all distinct document names of 150 documents.
Each document name can have a number of "x" in their Test column. Fo
example: a document "Doc Mac" could have several "x" in the Test colum
indicating which tests the document has.

A document "Doc Apple" could have only 3 "x"s each in Test1, Test2
Test 100; there would empty cell in Test3,Test4 and so forth.

Now, I wish to make a new table only 2 columns. First column, is th
DocNum and the second column is Type Of Test. The second column, wil
display the head field ("Testnumber") of table above if there is an "x
indicating there is a test. The results are based on the data of th
150x20500 table above. It would look like this:

DocNum Type of Test
---------- ----------------
Doc Apple Test1
Doc Apple Test2
Doc Apple Test100

Doc 3 Test75
Doc 3 Test100
Doc 3 Test110
Doc 3 Test115
Doc 4 Test1
Doc 4 Test2
Doc 4 Test101
Doc 4 Test102
Doc 5 Test100
Doc 6 Test44


Results of these 2 column table indicate the document and the test
(head field of the intial table above) it has.

As you can see, "Doc Apple" has 3 tests: Test1, Test2 and Test100.
This is in according to the 150x20500 table above.

I hope you would understand the problem now.
Again, if you can suggest a way to solve using Excel or Access, I woul
be greatful to you.

Thank you

 

[Jamie Collins]

myexcel wrote ...

Thank you for interesting in helping my weakness.

Here is a general problem, I have a large table with 150 columns and
20500 rows. The head field of the columns are DocNum, Test1, Test2,
Test 3....Test150.

Column DocNum contains all distinct document names of 150 documents.
Each document name can have a number of "x" in their Test column. For
example: a document "Doc Mac" could have several "x" in the Test column
indicating which tests the document has.

A document "Doc Apple" could have only 3 "x"s each in Test1, Test2,
Test 100; there would empty cell in Test3,Test4 and so forth.

Now, I wish to make a new table only 2 columns. First column, is the
DocNum and the second column is Type Of Test. The second column, will
display the head field ("Testnumber") of table above if there is an "x"
indicating there is a test.

This in the SQL view of a query in the MS Access UI:

SELECT
DocNum,
IIF(Test1='x', 'Test1', '')
& IIF(Test2='x', 'Test2', '')
& IIF(Test3='x', 'Test3', '')
...
& IIF(Test150='x', 'Test150', '')
AS type_of_test
INTO
MyNewTable
FROM
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].[MyWorksheet$]
;

Jamie.

--

 

[myexcel]

Thank you, Jamie. I will try it with Access and let you know th
results.
Thanks a million.
Moe

 

[myexcel]

Thank you Jamie. It works but not as I would like too.
I want to learn more about MS Access and its capability. I need t
search for a good book.
Thanks again