Automation of excel spread sheet

[cprocha]

I receive a daily report; I need help in automating this process.
Step one: highlight the entire spread sheet
Step two: expand the columns so I can read the information
Step three: de activate highlight
Step four: go to columns N thru Z) and set the date. (Format date and time)
Step five: go to Colum (AA) format alignment. Get rid of wrapped text.
Step six: Hard part because this always changes depending on the report.
6a- I have to highlight the rows and insert tree spaces. The reason this is
difficult is because I have matching invoices that need to be grouped
together all the way down.
Step Seven: after all the spacing in the rows is in I need to copy the top
row of all matching invoices and copy it one row above where I copied it from:

On the copied row I have to use the concatenate formula all the way down the
spread sheet

Example:
Copied Row - 12345 cat dog worms
Original row- 12345 cat dog worms Also a matching invoice.
Matching invoice 12345 cat dog worms
Matching Invoice 12345 cat dog worms

Through out this sheet we have miss-matched invoices numbers we have to work
around.

Step seven: I have to use the (Sum) on column (H) then copy to (Columns I,
J, K) all the way down where I used the concatenate formula.

Step eight: go over to column (A) and wrap the text. Format, alignment,
wrap text

Step Nine: Copy and paste the wrapped text and use the (PASTE as Special)
choose values for every row where I used the concatenate and sum formulas.
Step Ten: delete all unused rows to clean it up.

Is there an easier way to do this?
Thanks in advance

 

[Pete_UK]

I followed this through to step six, thinking you could record a macro
once to do this then replay it again in the future. I got a bit lost at
6a, though, and at Step Seven, and I'm not really sure what you mean
about using the "concatenate formula all the way down the spread sheet"
or having "mis-matched invoice numbers" that you have to work around.

I'm sure you could automate all of this, but you would need to describe
things a bit more clearly in the latter stages. Rather than concentrate
on what you do now, perhaps you could tell us what you want to achieve
and describe the format of the data that you have to work with.

Pete

 

[cprocha]

Thanks Pete, I hope this helps more.

What I have is a movement report. It tracks the shipments and movements.

My main goal is to string together a various number of rows keeping all
invoice numbers associated with one master bill Then I sum up the cartons
and copy the total to the ACT, CHG, and CBN What I look for Is the master
bill I find all alike master bills and then I separate them with spaces. In
between we have some master bills which do not have alike master bills. I
skip them and move on.

So in step Six A, I have to high light three rows then insert cells this
gives me three blank space on top then I repeat the steps for the next set
all the way down the sheet. When I am finished I come back to the top and
start with my first alike master bill. I copy the row and paste it above
where I copied it from. Then I delete the company name in the row I just
pasted and use the concatenate feature to tie these a like bills together.
Then I move on to the next set of alike master bills repeating these steps.

Example: does not have the copied and pasted row in yet for either of these
master bills.
Also does not have a miscellaneous set of master bills in it.
Company PO Vendor Name Broker Container # Master Bill Carton counts Act
WT Chg WT CBNs



12345 Happy US 9876 111222 5 12.90 12.90 6.447
23456 Happy US 54321 111222 5 134.00 134.00 2.333
26350 Happy US 2345 111222 66 233.00 233.00 19.234
4658957 Happy US 6789 111222 99 23.00 23.00 234.87
12345 Happy US 8765 111222 78 36.00 36.00 23.76



44444 Not Happy USA 98768 1116767 5 12.90 12.90 6.447
45454 Not
Happy USA 543214 1116767 5 134.00 134.00 2.333
99992 Not Happy USA 23453 1116767 66 233.00 233.00 19.234
45739 Not
Happy USA 67892 1116767 99 23.00 23.00 234.87
28356 Not
Happy USA 87651 1116767 78 36.00 36.00 23.76

 

[Pete_UK]

I think I follow what you are doing - grouping together records which
have the same Master Bill number (in your example these seem to be
111222 and 1116767 - line wrap has affected the layout), applying a Sum
formula on a few columns within the group, and separating each group
with a blank row. I'm still not sure what you are concatenating,
though.

A few more questions, if you don't mind:

Are these records in random order when you get them, or are they
already grouped?
Approximately how many rows do you have to deal with each day?
Approximately how many Master Bill numbers are in each file each day?
Why do you physically move the records (rather than use sort)?
Are you familiar with using Data|Subtotals, and if so, is this not
appropriate?

From your earlier posting, I presume you have many more columns than

those in your example. Would you like to take this off-line and send an
example file directly to me? It would be nice to have a sheet with the
original data as you receive it (your Step One) and another sheet
showing the data once it has been transformed (your Step 10). Of
course, you can change anything of a confidential nature before sending
it. My email address is pashurst <at> auditel.net, and I would expect
to be able to automate the whole process, so that you drop data into a
"source" sheet, run a macro, and end up with a "transformed" sheet -
depending on the amount of data this process should only take a few
seconds.

Hope this helps.

Pete