Hi,
We have an invoice in text format in excel. I need to convert in a
table format to be able to convert it into a pivot

I need macros created which can
1) delete blank rows
2) delete rows which start with certain text in a particular (the
invoice heading appears frequently in every page)
3) each transaction is in 2 lines- need it to be in one line

PAGE
ROSANA ALAM 36422299601017
FINANCE CONTROLLER 30/03/10
MICROSOFT GULF FZE LLC
MICROSOFT STAFF 1
P O BOX 52244
DUBAI
AGN CODE:01 AGN NAME :DNATA TEMPORARY STATEMENT FOR MARCH
2010
DATE TICKET TRANS PASSENGER NAME DESCRIPTION VALUE TAX DISCOUNT
AMOUNT
REF. NO. TYPE ROUTING PAYABLE

OPENING BALANCE 2 ,840,384.13
PAYMENT RECEI VED
28/03/10 Payment Received 0.00 0.00 0.00 1 ,465,957.00CR
EB290316
TOTAL 1 ,465,957.00CR


DEPARTME NT 1
23/02/10 681 0000330760 SVC MR XXXXXXX InvNo:XXXXXXXXXXXX 140.00 0.00
0.00 140.00
1333843 140.00

23/02/10 176 3842850331 ATB MR. ABDCDEEEE InvNo:XXXXXXXXXXX 2,830.00
280.00 0.00 3,110.00
33495 ACC/DXB 3,110.00

I've written code like this many times before. The easiest way of
handle data like this is to create a new worksheet a scan through the
original worksheet extracting the data you need and puttingg it on a new
worksheet.

I really need to see more of the data to get the code correct. I also
see that you need to extract from the page header some information like
the company name and put tis information in a new column.

If you can attach a workbook to your posting I will work on it in the
next couple of days. The psoting is locatted at

http://tinyurl.com/y8tfzkc

Reply to the posting and at the bottom of the reply box is a button
labels
"Manage Attachments" where yo can add a workbook to the reply message.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

Hi Joel,

Have added a sample of the attachment. appreciate ur help!!!!

Thanks,

Rosana



Rosana;686512 Wrote:

Hi,
We have an invoice in text format in excel. I need to convert in a
table format to be able to convert it into a pivot

I need macros created which can
1) delete blank rows
2) delete rows which start with certain text in a particular (the
invoice heading appears frequently in every page)
3) each transaction is in 2 lines- need it to be in one line

PAGE
ROSANA ALAM 36422299601017
FINANCE CONTROLLER 30/03/10
MICROSOFT GULF FZE LLC
MICROSOFT STAFF 1
P O BOX 52244
DUBAI
AGN CODE:01 AGN NAME :DNATA TEMPORARY STATEMENT FOR MARCH
2010
DATE TICKET TRANS PASSENGER NAME DESCRIPTION VALUE TAX DISCOUNT
AMOUNT
REF. NO. TYPE ROUTING PAYABLE

OPENING BALANCE 2 ,840,384.13
PAYMENT RECEI VED
28/03/10 Payment Received 0.00 0.00 0.00 1 ,465,957.00CR
EB290316
TOTAL 1 ,465,957.00CR


DEPARTME NT 1
23/02/10 681 0000330760 SVC MR XXXXXXX InvNo:XXXXXXXXXXXX 140.00 0.00
0.00 140.00
1333843 140.00

23/02/10 176 3842850331 ATB MR. ABDCDEEEE InvNo:XXXXXXXXXXX 2,830.00
280.00 0.00 3,110.00
33495 ACC/DXB 3,110.00


+-------------------------------------------------------------------+
|Filename: excel statement.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=511|
+-------------------------------------------------------------------+

--
i-roalam
------------------------------------------------------------------------
i-roalam's Profile: 1695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

I wil look at thsi today and get back to you by tomorrow. I took a
quick look and have a couple of questions. I know you ddi some editing
of the file which is good, but there ae some inconsistances I need to
understand

1) This line occurs only once in the file. Can yo explain?

DEPARTM ENT 1


2) I'm plannning to use the word New in column A to remove the headers
from each file. Is there a reason the first page Doesn't have NEW and
it also doesn't have any invoice listings. My plan is to ingore all
rows of data before the first NEW in column A, is this acceptable or is
there data before this 1st NEW that you need to keep.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

Hi Joel,

Yes i have edited the template since it was confidential data
1) Depart ment- this is appearing only twice in the whole statement-
once at the beginning and then in the end

2) i do not need the data which is appearing above New so that can be
deleted. so if i take row#28 (which is the start of the data for the
table) i would expect both the two lines to become one so. i would
expect the data in cell b29 to move to cell L28 so that all the data
would be in 1 row. also i do not need the data in cell K29. and
subsequently expect the same to happen for all the line entries below
that.

hope this makes sense.
thanks for all ur help!

regards,
rosana






i-roalam;687554 Wrote:

Hi Joel,

Have added a sample of the attachment. appreciate ur help!!!!

Thanks,

Rosana


--
i-roalam
------------------------------------------------------------------------
i-roalam's Profile: 1695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

Did you se my last posting. I attached the macro the posting.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

By the way, sometimes these type problems are easier to solve if you
let the macro open the text file. For example in you spreadsheet you
had the word Department split into columns B and C. I can open a text
file in VBA and get the word department to be in one column in stead of
tow columns.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

joel;688677 Wrote:

By the way, sometimes these type problems are easier to solve if you let
the macro open the text file. For example in you spreadsheet you had
the word Department split into columns B and C. I can open a text file
in VBA and get the word department to be in one column in stead of tow
columns.



hi joel,

thanks a million for the macro!!! it works like magic. it has taken me
less than a minute to convert the table. previously it used to take us
almost 4 hrs every month just to get the data into a table format.

Regards,

Rosana


--
i-roalam
------------------------------------------------------------------------
i-roalam's Profile: http://www.thecodecage.com/forumz/member.php?u=1695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz

A macro is an investment. The amount of the profit/reward is the number
of times you use it and the amount of savings you get every time you use
it!


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=191856

http://www.thecodecage.com/forumz