Damn you Excel!

[KillerGriller]

Can anybody help me with this? (yes I know there are help files, but I thought of you nerd first )

I'm trying to convert a flat table into a DB table (Access), but the data is oriented in individual records e.g.

record 1

attribute 1

attribute 2

attribute 3

attribute 4

record 2

attribute 1

attribute 2

attribute 3

attribute 4

how can I import/convert it into a proper DB table? e.g.

record 1 record 2

attribute 1 attribute 1

attribute 2 attribute 2

attribute 3 attribute 3

attribute 4 attribute 4

I feel like I'm missing something pretty obvious. I've added column headings to all the records which they didn't have before, but I'm stuck now.

[wildross]

Export it to text and use awk/Perl to convert it. Or spend a lot of time cutting and pasting. YOu can also do some things like numbering the rows (add a column), then use some vtext lookup functions to pull them into a new page using the line numbers. Will only work if they all have exactly the same number of entries so you can do math on the line numbers.

It aint easy.

Then go "speak to" the person who generated the data... Or maybe ask them first to regenerate it??

[KillerGriller]

damn, I thought there would be a simple transpose range function where I could identify values based on "name"

Yes the list is shitty, but not the worst I've seen...unfortunately.

[BarefootDawsy]

I'm sure we can work something out here, but let me clarify a few things.

If your spreadsheet's like this:

Record1

apples

celery

Record2

oranges

carrots

Shouldn't your DB table be something like

[record_id],[fruit],[vegetables]

1,apples,celery

2,oranges,carrots

If not, then I'm not really sure what you're trying to do, if so, then you have a few options.

The easiset would be to add your column headers on a sheet, then for each record, copy, then Paste Special>Transpose them under the headers.

You could write a macro to do this automatically too if there are a ton of records.

PM me if you like and I'll give you a hand.

[KillerGriller]

I'm sure we can work something out here, but let me clarify a few things.

If your spreadsheet's like this:

Record1

apples

celery

Record2

oranges

carrots

Shouldn't your DB table be something like

[record_id],[fruit],[vegetables]

1,apples,celery

2,oranges,carrots

If not, then I'm not really sure what you're trying to do, if so, then you have a few options.

The easiset would be to add your column headers on a sheet, then for each record, copy, then Paste Special>Transpose them under the headers.

You could write a macro to do this automatically too if there are a ton of records.

PM me if you like and I'll give you a hand.

Your example is exactly what I would like to make my DB table into. I have used transpose before, but never on 4000 records, I'll look into macros and see what I can come up with. Thanks

[BarefootDawsy]

No worries, let me know if u need help with the macro...I used to be an excel vba programmer