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

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

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.

--

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

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.

--