Importing data held in matrix form from Excel workbooks

[eagleaye]

I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. Each item can have a different quantity issued on different days. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection.

 

I'm hoping to be able to create a Filemaker solution but having difficulty importing the existing data in a useable format. Is it possible to import the quantity and date issued information as it is currently held in Excel?

 

Hope this makes sense, any guidance appreciated.

[comment]

Is it possible to import the quantity and date issued information as it is currently held in Excel?

 

It's possible, but it will be very tedious. The "useable format" you are looking for is a table with four columns: item, date, quantity, group.  The way you have it now, you would need to import one date column at a time (along with the common item column), and populate the date and group fields immediately after each import. Rearranging the data in Excel (or in another application) before importing it would probably be more efficient.

Edited October 15, 2013 by comment

[eagleaye]

Thanks for the reply, I had a feeling that would be the answer but wanted to check there wasn't some sort of method of scripting an import.

Looks like I'll have to find a way of automating the rearrangement of data in Excel!

[Wim Decorte]

Oh, you can still script the import. The import however would go into a scratch table and then your script would need to parse the data in that table to create records into your proper tables.  Like Comment said: tedious.