Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last row that is empty
I have extracted data from a sql database now the problem is formattin
the data as well as carrying out some functions on the data dependin on what the data is. For example I have to format the following data from this Jan Feb Mar Branch Qty Price Qty Price Qty Price Pep 8 7,484.42 18 9,196.67 Shoprite 7 7,628.09 4 7,381.47 Checkers 56 6,410.64 58 19,397.4 6 83.08 PicknPay 33 11,368.1 31 1,915.32 To look like this: Jan Feb Mar Branch Qty Price Qty Price Qty Price Pep 0 0.00 8 7,484.42 18 9,196.67 Shoprite 7 7,628.09 4 7,381.47 0 0.00 Checkers 56 6,410.64 58 19,397.40 6 83.0 PicknPay 0 0.00 100 11,368.10 97 1,915.32 I would like to be able to also insert a column between February an March and perform the following function (=(D3-B3) / B3) then fill thi function down for the rest of the rows. Then I would do the sam between March and April and so forth Feb Mar April Qty Price %Change Qty Price %Change Qty 8 7,484.42 16% 18 9,196.67 7% 17 4 7,381.47 -100% 0 0.00 100% 12 58 19,397.40 78% 6 83.08 33% 4 100 11,368.10 -3% 97 1,915.32 -4% 101 I hope this makes sense. Kind Regards PS. for some reason I can't indent the columns to the right places -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last row that is empty
Hi again
I am so sorry, I forget my subject heading altogether. Another proble is I want to be able to find the last empty row in a worksheet and wor out the totals of each column and place it in this row. That was the first problem I was going to ask about with that headin but then I forgot. Kind regard -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last row that is empty
poppy wrote ...
I want to be able to find the last empty row in a worksheet and work out the totals of each column and place it in this row. As you used SQL to fetch the data, I can tell you can use a query to insert the total in Excel e.g. INSERT INTO [MySheet$B3:B65536] (Price) SELECT SUM(Price) AS Price FROM [MySheet$B3:B65536] ; Although in the above the maximum rows has been specified, in reality the provider will find the next unused row and insert the total there. However, a SQL insert can only be done on a closed workbook. Is this a show-stopper for you? For your original question about formatting, your sample data is all over the place as I see it: http://groups.google.com/groups?selm...rum-nospam.com You may want to repost. Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last row that is empty
Hi Jamie
For some reason when i finsih typing out my sample table data an preview it, it's not tabbed the way I did it. It just ends up bein jumbled, do you have any idea what is wrong? And about the INSERT INTO - would I be doing it from sql or in vba -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last row that is empty
poppy wrote ...
For some reason when i finsih typing out my sample table data and preview it, it's not tabbed the way I did it. It just ends up being jumbled, do you have any idea what is wrong? You should be using a fixed width-font. I like Lucida Console, which I use for my VBE editor format. And about the INSERT INTO - would I be doing it from sql or in vba? You could do it from SQL Server but it would be easier to do on the Excel side. Either in MS Query (Data, Get External Data, New Database Query, make connection, get to the SQL window and start typing) or ADO in VBA code. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the next empty cell. | Excel Programming | |||
Finding last non-empty column in row... | Excel Programming | |||
Dynamically finding the last empty row | Excel Programming | |||
Finding Empty Rows | Excel Programming | |||
Finding Empty Rows | Excel Programming |