Excel formatting

[gristc01]

Hello,

I'm trying to automatically re-format vertically aligned data in excel spreadsheets that are sent to me to a Horizontal alignment.

So basically I get the data in the following format in columns A and B (I seperated them below with a --)

ColumnA -- ColumnB

Author: -- Doe1, John

ID# -- 1234567890

Cust # -- 12345678

UniqueID -- 12345678

Address -- 555 test dr

Address -- APT # 102

City -- TEST CITY

State -- MI

ZIP -- 55555

Account Number -- 55555

SupplyItem & ID -- TEST Description _ 5555555

Quantity -- 1

SupplyItem & ID -- TEST Description _ 4444444

Quantity -- 2

Author: -- Doe2, John

ID# -- 0987654321

Cust # -- 87654321

UniqueID -- 87654321

Address -- 111 test dr

Address -- APT # 50

City -- TEST CITY2

State -- OH

ZIP -- 44444

Account Number -- 55555

SupplyItem & ID -- TEST Description _ 111111

Quantity -- 3

SupplyItem & ID -- TEST Description _ 222222

Quantity -- 2

SupplyItem & ID -- TEST Description _ 333333

Quantity -- 1

I want to read this data and put it into a new excel file in the following format (basically take column A out of the picture and just take the values and align them horizontally individually in their own cells.)

Doe1, John -- 1234567890 -- 12345678 -- 12345678 -- 555 test dr -- APT # 102 -- TEST CITY -- MI -- 55555 -- 555555 -- 1

Doe1, John -- 1234567890 -- 12345678 -- 12345678 -- 555 test dr -- APT # 102 -- TEST CITY -- MI -- 55555 -- 444444 -- 2

Doe2, John -- 0987654321 -- 87654321 -- 87654321 -- 111 test dr -- APT # 50 -- TEST CITY -- MI -- 44444 -- 111111 -- 3

Doe2, John -- 0987654321 -- 87654321 -- 87654321 -- 111 test dr -- APT # 50 -- TEST CITY -- MI -- 44444 -- 222222 -- 2

Doe2, John -- 0987654321 -- 87654321 -- 87654321 -- 111 test dr -- APT # 50 -- TEST CITY -- MI -- 44444 -- 333333 -- 1

So for each item number per cust # I am in need of a seperate line.

Can anyone help a newbie in the right direction? I'm assuming I'm going to have to read in the strings and write them back out some way or another. Any insight would be greatly appreciated!

[DaRam]

You just need to:

1. Select ColumnB Cells for 1 entitiy (Author/ID)

2. Paste Special using Transform onto a different Sheet/Workbook

Repeat 1 and 2 for each entity. Build Script to do this (increase row offset by # of rows in each entity + 1).

[gristc01]

You just need to:

1. Select ColumnB Cells for 1 entitiy (Author/ID)

2. Paste Special using Transform onto a different Sheet/Workbook

Repeat 1 and 2 for each entity. Build Script to do this (increase row offset by # of rows in each entity + 1).

Awesome....I think I have that part figured out now. Thanks!

One more question though. For my Cust # I need it to be 8 digits long always, but mostly it's only 4-6 digits long.

Is there an easy way to add leading zero's to make it 8 digits?

I have another file format that looks like:

"Cust #","130977"

"Cust #","10512"

If I wanted to update all of them to look like

"Cust #","00130977"

"Cust #","00010512"

Is there something easy I could write for this?

[big_daddy]

Set the cell format to Custom and 00000000