Convert text invoice into a table- please help!

R

Rosana

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
 
J

joel

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

I really need to see more of the data to get the code correct. I als
see that you need to extract from the page header some information lik
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 th
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 butto
labels
"Manage Attachments" where yo can add a workbook to the reply message.
 
I

i-roalam

Hi Joel,

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

Thanks,

Rosana



Rosana;686512 said:
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
Click to expand...


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

joel

I wil look at thsi today and get back to you by tomorrow. I took
quick look and have a couple of questions. I know you ddi some editin
of the file which is good, but there ae some inconsistances I need t
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 header
from each file. Is there a reason the first page Doesn't have NEW an
it also doesn't have any invoice listings. My plan is to ingore al
rows of data before the first NEW in column A, is this acceptable or i
there data before this 1st NEW that you need to keep.
 
I

i-roalam

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 b
deleted. so if i take row#28 (which is the start of the data for th
table) i would expect both the two lines to become one so. i woul
expect the data in cell b29 to move to cell L28 so that all the dat
would be in 1 row. also i do not need the data in cell K29. an
subsequently expect the same to happen for all the line entries belo
that.

hope this makes sense.
thanks for all ur help!

regards,
rosana
 
J

joel

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

i-roalam

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



hi joel,

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

Regards,

Rosana
 
J

joel

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