Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automation of excel spread sheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automation of excel spread sheet
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automation of excel spread sheet
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automation of excel spread sheet
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 cprocha wrote: 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" wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove an unwanted check box from an Excel spread sheet? | Excel Discussion (Misc queries) | |||
Combining data from cells from several excel sheets to a new sheet | Excel Discussion (Misc queries) | |||
ADD MONIES USIN EXCEL SPREAD SHEET | New Users to Excel | |||
is there anyway to make it so the users of my excel spread sheet cant view the macro code w/o a password? | Excel Worksheet Functions | |||
How do I export excel spread sheet to my Palm T3? | Excel Discussion (Misc queries) |