Sign in to follow this  
Followers 0
gristc01

Excel formatting

4 posts in this topic

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!

Share this post


Link to post
Share on other sites



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).

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Set the cell format to Custom and 00000000

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0