*UPDATE* Rows of data to Columns of data *UPDATE*

Thanks for the help so far, thou a slight change......

So far the VBS works fine BUT needs to be altered, something a little more advanced.
it was quite easy to understand how the VBS pulls the data but how can it skip a column of data and a column to add the number "000" to the front of the accountID.???

new test excel file attached.

thanks once again!!!
_lex

-------------------------------------------------------------------------------------------------------------
Hi all,

Such a wonderful site here,
but for the life of me, i can not work out how to setup a Macro or VBS to convert my simple rows of data to making it columns of data.

Problem is:
a) program scans 3x fields of data in (per page), then out puts it to a excel file.
b) each field is on a different row i.e. name\date\code.
c) each other page continues after the last row.
d) 100 pages are scanned in.

Question being, is there a easy way to output this data so it reads\shows in Columns rather than Rows.

Any bit of help would go along way!

Attached is a dummy file which shows the way the data is entered and how it should look like in the end.

thanks for your time!

Hi

The above can be obtained using formulas.

E1: =IF((ROW()-1)*3>=COUNTA(A:A),"",OFFSET($A$1,(ROW()-1)*3,0))
F1: =IF(E1="","",OFFSET($A$1,((ROW()-1)*3)+1,0))
G1: =IF(E1="","",OFFSET($A$1,((ROW()-1)*3)+2,0))

Copy down to match the relevant data.

If you do really need a macro then try

Please Login or Register  to view this content.

HTH

rylo

Hello a6ela,

Here is an automated VBA solution. The macro below copies the data in column "A" three rows at a time to columns "E:G" as single rows. A button has been added to the worksheet to run the macro.

Please Login or Register  to view this content.

Hi
Apply this code to attached sample file, if you find this to be working then you can make necessary changes in your original file.

Please Login or Register  to view this content.

Hope this helps..

Hello a6ela,

You will need to give me an example of the problem or post an updated workbook that has the problem.

Hello a6ela,

Here is the revised macro. It now adds a "000" prefix to all the account IDs. The columns have been changed to "J,L,N,O". Here is the code that has been added to the attached workbook.

Please Login or Register  to view this content.