Basic Excel problem

[Thorwyn]

Ok.. maybe someone here is able to help me with this littel problem.
I have like 250 excel sheets. Each one of them has the same structure (exported from access). The thing is, however, that the exported structures are wrong.

Simple example...

Exported sheet is
Name ... Number ...FirstName ...Street
Smith ....5 ...........Hans ..........StupidRoad

What I WANT is
FirstName ...Name ....Street ........Number
Hans ..........Smith ...StupidRoad ..5

Is there any way to bring all the 250 Excel sheets into ONE predefined structure?

 

[Zenith.UK]

How are you exporting the data from Access?
It just strikes me as a SQL query formatting issue in Access.

SELECT uid, FirstName, Name, Street, Number FROM your_table

That sort of thing.

 

[Thorwyn]

The access export is not under my control, I just have the .xls files to work with.

 

[ST^]

You could make a new Access database, import them all and use the first row as the field name. Do you need to keep the data separate or can all of the sheets be merged?

Tbh, it wouldn't take (me) very long to drag the columns about manually.

 

[old.Tohtori]

Do what professionals do; send angry emails to those who did export them and say you expect results yesterday!

 

[Thorwyn]

You could make a new Access database, import them all and use the first row as the field name. Do you need to keep the data separate or can all of the sheets be merged?

Like I said, the access side is not under my control. And yes, the sheets need to remain separated.

It´s a bit more complicated than that, since it´s about 30 columns per sheet, so lots of dragging and lots of chances for mistakes.

Tbh, it wouldn't take (me) very long to drag the columns about manually.

Do what professionals do; send angry emails to those who did export them and say you expect results yesterday!

That´s actually the best idea so far.

 

[Jupitus]

To be honest, re-importing them will be a mare. Likewise trying to do each would be. Best option would be to get them re-exported to spec, but if you really have no option then I think you are heading into VB macro land... initial macro to chop and change the columns into the structure you want, and then a control macro to open all the files in turn from a list before calling the chop and change routine on each one.

Nasty tbpfh...

 

[old.Tohtori]

That´s actually the best idea so far.

Heh, i've learned that while cleaning out other peoples mistakes shows initiative and is well respected, if you can't do it without it hindering YOUR work hours and thus making your job at risk to be delayed, your best option is to lay blame down or up the production line.

No one will make it their business to come after you, if you've not made a mistake. Especially if you word the complaint in the form of; "I tried to do X to fix the situation but..."

 

[Zenith.UK]

Jupitus is spot on.
Record a VB macro of you performing the column movements. Make sure you save it to your Personal Workbook, not the workbook you're working in. It'll be available for all XLS sheets you open after that.

Go back into the VB editor to be sure that your cell references are good (you may need to unhide your personal workbook). Essentially you're looking to remove duplicate or unnecessary actions.

I would turn on the recording, right click on the column header, cut, right click the column header where I want the column to go, and choose paste. Wash, rinse, repeat until done. Then turn off the recording and try to run the macro on an "untouched" workbook. If you get the expected results, it's all good.

 

[Lamp]

Record and use Excel macros - Excel - Microsoft Office