Reconcilation

I need help again for creating a macro. I have a notepad file that contains data. We need to convert the notepad file into excel and then segregate the data after conversion. Segregation point would be the point where in we can find keyword “Summary”. We need to create a macro that finds the occurrence of summary keyword. Then from the beginning till that summary point cut the entire data and paste in other worksheet. Name the worksheet as “Receivables” or “Payables” or “Fee Payable” depending what type of data that summary contains.


After creating different worksheets we need to format the worksheet in specific format.
For example: I have attached the “Recon1” XL file attached. Under Recon1 – “RECEIVABLES 1” contains the as is data converted from notepad. Later we need to modify the same data using macro as specified in “RECEIVABLES 2” and then as per the format available in “RECEIVABLES 3”.

Any help on this would be highly appreciable.


Regards,
Shahcu

Hi,

Need help, Please help me out.

Shahcu

Someone please help.

If you can attach the file without Zipped, I can take a look at it and see if I can help you or not.

Hi Jindon,

I have attached both files as an attachment. Please help.

Regards

Attached please find the excel sheet ...

Found 3 sheets (RECEIVABLES 1,2,3)

1) which is the data imported from the text file ?
2) what is the logic to manipulate the data into separate worksheets?

I coudn't read your text file attached...
If you can attach picture file showing original data, it helps a lot.

Hi jindon,

RECEIVABLES1 contains data that i have converted from notepad. I need a macro that remove the unnecessary spaces , page header and then put the data in another sheet which is RECEIVABLES2. Receivable3 is againa refined version of RECEIVABLES2. We only need to transfer data from RECEIVABLES2 to Receivable3 in appropriate format ie... only columns listed below.

Ref no Name CODE DATE RATE AMOUNT DUE

I am again attaching main notepad file so that u can get the actual look of the raw data.

shahcu,

Due to the security here, I can only see worksheets in .xls file and picture file.
So I can not open .txt file attached...

Hi Jindon,

I understand that you are not able to see the notepad files.

Jindon, I would request you to please copy entire contents of Receivables 1 sheet and paste it into Notepad file at your system. I intention is to just to give you the look n feel of how notepad file looks like. If you can do that If would be a great help for me.

I can't upload any picture file reason being only 100 kb of file size is allowed on this forum.

Regards

OK
So RECEIVABLES 1 is the sheet from original data.

Then what is the logic to manipulate the data ?

Can you tell me step by step how you manually do the job ?

Hi Jindon,

I need a macro that remove the unnecessary spaces , blank lines , page headers and then put the data in another sheet which is RECEIVABLES2.

When I say page header that means details like :


Recon Statement
ABC INC 10:37:34 1 3-Apr-08 Page: 1
ABC INC 10:37:34 13-Apr-08 Page: 5
Recon Statement
Date Ra nge From 01 MAR 08 to 31 MAR 8
Counterparty Name
Counterparty Address1
Dept: SECURITIES
"Contact: Contact Name " R E C E I V A B L E
Settled Act F Eff


Receivable3 is againa refined version of RECEIVABLES2. We only need to transfer data from RECEIVABLES2 to Receivable3 in appropriate format ie... only columns listed below.

It seems the number of records in RECEIVABLES 1 & RECEIVABLES 2 are different.
RECEIVABLES 2 have less records than RECEIVABLES 1.
Any reason ?

Reason being i have uploaded the data in zip format when u asked me to upload normal excel file then i deleted some records from each worksheets to reduce the file size to 100kb. We are not allowed to upload any file which is greater than 100 KB.


Regards

Can it be said
on RECEIVABLES 1
1) Find 1st "Bargain Ref Date Date" in col.A
2) Find company name (value not begin with number)
3) Copy ColA to ColL where ColA have value starts with Number until balnk in ColC
4) When ColA has any value starts with non numeric character, then find 2nd "Bargain Ref Date Date"

and continue

?

Yes... that's right

Just try this and see if this close..

Please Login or Register  to view this content.

Hi Jindon,

Really appreciate your hard work and support. Many thanks to you for being this much supportive.

Just ran the macro that you have sent however there are couple of more rows that needs to be eliminated. I have marked them as yellow. I do not need all these yellow marked rows in Receivables2.

Attached please find the output excel file.


Many Thanks again.

I am getting the following error:

"Run Time Error 424"
Object Required

At line ...


.Formula = "=if(countif(b$1:b1,""Bargain*"")>1,1,"""")"

Can you just delete that line and run the code?

Please let me know what is the significance of thsi statement...


.SpecialCells(-4123,1).EntireRow.Delete

I understand that this will delete the entire row carrying any specialcell value but unable ot understand the (-4123,1) what does it mean...

If you have any line that you don't understand, get vb Help file first.

SpecialCells
-4123 = xlCellTypeFormulas
1 = xlNumbers

Hi,

Sorry for the late response.

I am only getting 30 records as a output. Basis the sample file provided i shud get 130 records. Can you please suggest me on that.